-- Timeline Persistence Tables
-- Adds timeline_stays and timeline_trips tables for caching processed timeline data
-- Also adds city and country columns to favorite_locations and reverse_geocoding_location tables
-- Part of Timeline Persistence & Dynamic Favorite Updates feature

-- Add city and country columns to existing tables
ALTER TABLE favorite_locations 
    ADD COLUMN city VARCHAR(200),
    ADD COLUMN country VARCHAR(100);

ALTER TABLE reverse_geocoding_location 
    ADD COLUMN city VARCHAR(200),
    ADD COLUMN country VARCHAR(100);

-- Create indexes for the new location columns (for filtering and analytics)
CREATE INDEX idx_favorite_locations_city ON favorite_locations (city) WHERE city IS NOT NULL;
CREATE INDEX idx_favorite_locations_country ON favorite_locations (country) WHERE country IS NOT NULL;
CREATE INDEX idx_favorite_locations_city_country ON favorite_locations (country, city) WHERE city IS NOT NULL AND country IS NOT NULL;

CREATE INDEX idx_reverse_geocoding_city ON reverse_geocoding_location (city) WHERE city IS NOT NULL;
CREATE INDEX idx_reverse_geocoding_country ON reverse_geocoding_location (country) WHERE country IS NOT NULL;
CREATE INDEX idx_reverse_geocoding_city_country ON reverse_geocoding_location (country, city) WHERE city IS NOT NULL AND country IS NOT NULL;

-- Timeline stays table for caching processed stay points
CREATE TABLE timeline_stays
(
    id               BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    user_id          UUID                                    NOT NULL,
    timestamp        TIMESTAMP WITHOUT TIME ZONE             NOT NULL,
    stay_duration    BIGINT                                  NOT NULL,
    latitude         DOUBLE PRECISION                        NOT NULL,
    longitude        DOUBLE PRECISION                        NOT NULL,
    location_name    VARCHAR(500)                            NOT NULL,
    favorite_id      BIGINT,
    geocoding_id     BIGINT,
    created_at       TIMESTAMP WITHOUT TIME ZONE             NOT NULL,
    timeline_version VARCHAR(64),
    last_updated     TIMESTAMP WITHOUT TIME ZONE             NOT NULL,
    is_stale         BOOLEAN                                 NOT NULL DEFAULT FALSE,
    location_source  VARCHAR(255)                            NOT NULL DEFAULT 'GEOCODING' CHECK (location_source IN ('FAVORITE','GEOCODING','HISTORICAL')),
    CONSTRAINT pk_timeline_stays PRIMARY KEY (id)
);

-- Timeline trips table for caching processed trip segments  
CREATE TABLE timeline_trips
(
    id               BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    user_id          UUID                                    NOT NULL,
    timestamp        TIMESTAMP WITHOUT TIME ZONE             NOT NULL,
    trip_duration    BIGINT                                  NOT NULL,
    start_latitude   DOUBLE PRECISION                        NOT NULL,
    start_longitude  DOUBLE PRECISION                        NOT NULL,
    end_latitude     DOUBLE PRECISION                        NOT NULL,
    end_longitude    DOUBLE PRECISION                        NOT NULL,
    distance_km      DOUBLE PRECISION                        NOT NULL,
    movement_type    VARCHAR(50),
    path             GEOMETRY(LineString, 4326),
    timeline_version VARCHAR(64),
    last_updated     TIMESTAMP WITHOUT TIME ZONE             NOT NULL,
    is_stale         BOOLEAN                                 NOT NULL DEFAULT FALSE,
    created_at       TIMESTAMP WITHOUT TIME ZONE             NOT NULL DEFAULT NOW(),
    CONSTRAINT pk_timeline_trips PRIMARY KEY (id)
);

-- Foreign key constraints
ALTER TABLE timeline_stays
    ADD CONSTRAINT FK_TIMELINE_STAYS_ON_USER FOREIGN KEY (user_id) REFERENCES users (id);

ALTER TABLE timeline_stays
    ADD CONSTRAINT FK_TIMELINE_STAYS_ON_FAVORITE_LOCATION FOREIGN KEY (favorite_id) REFERENCES favorite_locations (id);

ALTER TABLE timeline_stays
    ADD CONSTRAINT FK_TIMELINE_STAYS_ON_GEOCODING_LOCATION FOREIGN KEY (geocoding_id) REFERENCES reverse_geocoding_location (id);

ALTER TABLE timeline_trips
    ADD CONSTRAINT FK_TIMELINE_TRIPS_ON_USER FOREIGN KEY (user_id) REFERENCES users (id);

-- Performance indexes for timeline queries
-- Core query indexes (user + date range)
CREATE INDEX idx_timeline_stays_user_timestamp ON timeline_stays (user_id, timestamp);
CREATE INDEX idx_timeline_trips_user_timestamp ON timeline_trips (user_id, timestamp);

-- Date-based queries (for daily timeline requests)
CREATE INDEX idx_timeline_stays_user_date ON timeline_stays (user_id, DATE(timestamp));
CREATE INDEX idx_timeline_trips_user_date ON timeline_trips (user_id, DATE(timestamp));

-- Staleness detection (for cache invalidation)
CREATE INDEX idx_timeline_stays_user_stale ON timeline_stays (user_id, is_stale) WHERE is_stale = true;
CREATE INDEX idx_timeline_trips_user_stale ON timeline_trips (user_id, is_stale) WHERE is_stale = true;

-- Version-based queries (for cache validation)
CREATE INDEX idx_timeline_stays_version ON timeline_stays (timeline_version);
CREATE INDEX idx_timeline_trips_version ON timeline_trips (timeline_version);

-- Location reference queries (for favorite change impact analysis)
CREATE INDEX idx_timeline_stays_favorite_location ON timeline_stays (favorite_id) WHERE favorite_id IS NOT NULL;
CREATE INDEX idx_timeline_stays_geocoding_location ON timeline_stays (geocoding_id) WHERE geocoding_id IS NOT NULL;

-- Spatial index for trip paths
CREATE INDEX idx_timeline_trips_path ON timeline_trips USING GIST (path) WHERE path IS NOT NULL;

-- Time range queries (for efficient date filtering)
CREATE INDEX idx_timeline_stays_user_timestamp_duration ON timeline_stays (user_id, timestamp, stay_duration);
CREATE INDEX idx_timeline_trips_user_timestamp_duration ON timeline_trips (user_id, timestamp, trip_duration);

-- Cleanup and maintenance indexes
CREATE INDEX idx_timeline_stays_created_at ON timeline_stays (created_at);
CREATE INDEX idx_timeline_trips_created_at ON timeline_trips (created_at);
CREATE INDEX idx_timeline_stays_last_updated ON timeline_stays (last_updated);
CREATE INDEX idx_timeline_trips_last_updated ON timeline_trips (last_updated);