-- ===================================================================== -- Travel Docket · Migration 005 · Phase P5 — Visits, photos, places to see, -- shopping, trip map, weather, holidays and events -- Covers: VIS-01..03, PHO-01..06, PLC-05, DSC-01..05, SHP-01..04, MAP-01..05, -- WTH-01..04, WTH-06, EVT-01..05, EVT-07 -- ===================================================================== SET NAMES utf8mb4; ALTER TABLE trips ADD COLUMN photo_quota_mb INT UNSIGNED NOT NULL DEFAULT 2048 AFTER home_tz, -- PHO-06 ADD COLUMN external_album_url VARCHAR(500) NULL AFTER photo_quota_mb; ALTER TABLE places ADD COLUMN weekly_off SET('mon','tue','wed','thu','fri','sat','sun') NULL AFTER tz; -- EVT-05 -- --------------------------------------------------------------------- -- Visits (VIS-01..03, DSC-03). Plan time is local wall-clock at the place. -- --------------------------------------------------------------------- CREATE TABLE visits ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, uuid CHAR(36) CHARACTER SET ascii NOT NULL, trip_id BIGINT UNSIGNED NOT NULL, place_id BIGINT UNSIGNED NOT NULL, visit_date DATE NOT NULL, plan_time TIME NULL, tz VARCHAR(64) NOT NULL DEFAULT 'Asia/Kolkata', category ENUM('temple','sightseeing','nature','food','shopping','other') NOT NULL DEFAULT 'sightseeing', status ENUM('planned','visited','skipped') NOT NULL DEFAULT 'planned', arrived_utc DATETIME NULL, left_utc DATETIME NULL, rating TINYINT UNSIGNED NULL, notes VARCHAR(1000) NULL, source ENUM('manual','suggestion') NOT NULL DEFAULT 'manual', created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_visits_uuid (uuid), KEY ix_visits_trip_date (trip_id, visit_date), KEY ix_visits_place (place_id), CONSTRAINT chk_visit_rating CHECK (rating IS NULL OR rating BETWEEN 1 AND 5), CONSTRAINT fk_visit_trip FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE, CONSTRAINT fk_visit_place FOREIGN KEY (place_id) REFERENCES places (id), CONSTRAINT fk_visit_user FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- Photos (PHO-01..05, MAP-05) -- --------------------------------------------------------------------- CREATE TABLE photos ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, uuid CHAR(36) CHARACTER SET ascii NOT NULL, trip_id BIGINT UNSIGNED NOT NULL, uploaded_by BIGINT UNSIGNED NOT NULL, sha256 CHAR(64) CHARACTER SET ascii NOT NULL, storage_path VARCHAR(255) NOT NULL, -- JPEG (HEIC converted, PHO-02) thumb_path VARCHAR(255) NULL, mime VARCHAR(40) NOT NULL DEFAULT 'image/jpeg', width SMALLINT UNSIGNED NULL, height SMALLINT UNSIGNED NULL, size_bytes INT UNSIGNED NOT NULL, taken_utc DATETIME NULL, -- EXIF lat DECIMAL(9,6) NULL, lng DECIMAL(9,6) NULL, day_date DATE NULL, -- auto (PHO-03) or manual (PHO-04) visit_id BIGINT UNSIGNED NULL, expense_id BIGINT UNSIGNED NULL, -- a receipt photo caption VARCHAR(255) NULL, assign_source ENUM('exif','manual','none') NOT NULL DEFAULT 'none', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_photos_uuid (uuid), UNIQUE KEY uq_photos_trip_sha (trip_id, sha256), KEY ix_photos_trip_day (trip_id, day_date), KEY ix_photos_visit (visit_id), CONSTRAINT fk_photo_trip FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE, CONSTRAINT fk_photo_user FOREIGN KEY (uploaded_by) REFERENCES users (id), CONSTRAINT fk_photo_visit FOREIGN KEY (visit_id) REFERENCES visits (id) ON DELETE SET NULL, CONSTRAINT fk_photo_expense FOREIGN KEY (expense_id) REFERENCES expenses (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- Places to see / shopping suggestions cache (DSC-01, DSC-04, DSC-05, SHP-01) -- --------------------------------------------------------------------- CREATE TABLE poi_cache ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, place_id BIGINT UNSIGNED NOT NULL, -- centre point provider VARCHAR(30) NOT NULL, -- opentripmap / geoapify / google category VARCHAR(40) NOT NULL, -- sights / temples / shopping / markets … radius_km SMALLINT UNSIGNED NOT NULL DEFAULT 10, payload JSON NOT NULL, -- normalised list of POIs fetched_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, expires_at DATETIME NOT NULL, -- fetched + 30 days PRIMARY KEY (id), UNIQUE KEY uq_poi_cache (place_id, provider, category, radius_km), KEY ix_poi_expires (expires_at), CONSTRAINT fk_poi_place FOREIGN KEY (place_id) REFERENCES places (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- Local speciality notes per place (SHP-02): curated or member-added. CREATE TABLE place_notes ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, uuid CHAR(36) CHARACTER SET ascii NOT NULL, place_id BIGINT UNSIGNED NOT NULL, kind ENUM('speciality','tip') NOT NULL DEFAULT 'speciality', note VARCHAR(500) NOT NULL, is_curated TINYINT(1) NOT NULL DEFAULT 0, created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_place_notes_uuid (uuid), KEY ix_place_notes_place (place_id), CONSTRAINT fk_pn_place FOREIGN KEY (place_id) REFERENCES places (id) ON DELETE CASCADE, CONSTRAINT fk_pn_user FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- Trip shopping list (SHP-03, SHP-04) CREATE TABLE shopping_items ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, uuid CHAR(36) CHARACTER SET ascii NOT NULL, trip_id BIGINT UNSIGNED NOT NULL, item VARCHAR(150) NOT NULL, for_whom VARCHAR(120) NULL, place_id BIGINT UNSIGNED NULL, budget_amount DECIMAL(12,2) NULL, currency CHAR(3) NOT NULL DEFAULT 'INR', status ENUM('to_buy','bought','dropped') NOT NULL DEFAULT 'to_buy', bought_by BIGINT UNSIGNED NULL, -- traveller bought_at DATETIME NULL, expense_id BIGINT UNSIGNED NULL, -- created when marked bought notes VARCHAR(255) NULL, created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_shopping_uuid (uuid), KEY ix_shopping_trip (trip_id, status), KEY ix_shopping_place (place_id), CONSTRAINT fk_shop_trip FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE, CONSTRAINT fk_shop_place FOREIGN KEY (place_id) REFERENCES places (id) ON DELETE SET NULL, CONSTRAINT fk_shop_buyer FOREIGN KEY (bought_by) REFERENCES travellers (id) ON DELETE SET NULL, CONSTRAINT fk_shop_expense FOREIGN KEY (expense_id) REFERENCES expenses (id) ON DELETE SET NULL, CONSTRAINT fk_shop_user FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- Weather (WTH-01..04, WTH-06). One row per place, date and kind. -- kind='daily' forecast, 'hourly' (payload holds 24 h), 'normal' (climate -- normal for the month; forecast_date = first day of that month). -- --------------------------------------------------------------------- CREATE TABLE weather_cache ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, place_id BIGINT UNSIGNED NOT NULL, provider VARCHAR(30) NOT NULL, -- openmeteo / openweathermap kind ENUM('daily','hourly','normal') NOT NULL, forecast_date DATE NOT NULL, -- local date at the place temp_min_c DECIMAL(4,1) NULL, temp_max_c DECIMAL(4,1) NULL, precip_prob TINYINT UNSIGNED NULL, -- % precip_mm DECIMAL(6,1) NULL, condition_code VARCHAR(20) NULL, -- normalised: clear/cloudy/rain/storm… condition_text VARCHAR(80) NULL, is_severe TINYINT(1) NOT NULL DEFAULT 0, -- WTH-05 thresholds hit payload JSON NULL, -- hourly series / raw extras fetched_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_weather (place_id, kind, forecast_date, provider), KEY ix_weather_fetched (fetched_at), CONSTRAINT fk_weather_place FOREIGN KEY (place_id) REFERENCES places (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- --------------------------------------------------------------------- -- Holidays and festivals (EVT-01..03, EVT-07). -- state_code '' = national. place_id set for city-specific festivals (Mysuru Dasara). -- --------------------------------------------------------------------- CREATE TABLE holidays ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, country_code CHAR(2) NOT NULL DEFAULT 'IN', state_code VARCHAR(10) NOT NULL DEFAULT '', place_id BIGINT UNSIGNED NULL, holiday_date DATE NOT NULL, end_date DATE NULL, -- multi-day festivals name VARCHAR(150) NOT NULL, scope ENUM('national','state','regional','observance') NOT NULL, source ENUM('api','curated') NOT NULL DEFAULT 'api', impact_tags SET('bank_closed','office_closed','market_closed','monument_closed', 'crowding','fare_surge','road_restrictions','dry_day') NULL, notes VARCHAR(255) NULL, created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_holiday (country_code, state_code, holiday_date, name), KEY ix_holiday_date (holiday_date, country_code, state_code), KEY ix_holiday_place (place_id), CONSTRAINT fk_holiday_place FOREIGN KEY (place_id) REFERENCES places (id) ON DELETE SET NULL, CONSTRAINT fk_holiday_user FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- One provider fetch per country/state/year (EVT-07). CREATE TABLE holiday_fetch_log ( country_code CHAR(2) NOT NULL, state_code VARCHAR(10) NOT NULL DEFAULT '', year SMALLINT UNSIGNED NOT NULL, provider VARCHAR(30) NOT NULL, fetched_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (country_code, state_code, year, provider) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- Special events near places (EVT-04): from a provider (trip_id NULL) or added by a member. CREATE TABLE local_events ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, uuid CHAR(36) CHARACTER SET ascii NOT NULL, trip_id BIGINT UNSIGNED NULL, place_id BIGINT UNSIGNED NOT NULL, name VARCHAR(150) NOT NULL, category ENUM('concert','sports','fair','festival','rally','bandh','other') NOT NULL DEFAULT 'other', start_utc DATETIME NOT NULL, end_utc DATETIME NULL, lat DECIMAL(9,6) NULL, lng DECIMAL(9,6) NULL, source ENUM('provider','member') NOT NULL, provider VARCHAR(30) NULL, provider_ref VARCHAR(100) NULL, impact_tags SET('bank_closed','office_closed','market_closed','monument_closed', 'crowding','fare_surge','road_restrictions','dry_day') NULL, added_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_events_uuid (uuid), UNIQUE KEY uq_events_provider_ref (provider, provider_ref), KEY ix_events_place_time (place_id, start_utc), KEY ix_events_trip (trip_id), CONSTRAINT fk_evt_trip FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE, CONSTRAINT fk_evt_place FOREIGN KEY (place_id) REFERENCES places (id) ON DELETE CASCADE, CONSTRAINT fk_evt_user FOREIGN KEY (added_by) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO schema_migrations (version) VALUES ('005_p5_visits_photos_discovery');