CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;

CREATE SEQUENCE IF NOT EXISTS reverse_geocoding_location_seq START WITH 1 INCREMENT BY 50 NO MAXVALUE;

CREATE TABLE favorite_locations
(
    id       BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    user_id  UUID                                    NOT NULL,
    geometry GEOMETRY(Geometry, 4326),
    name     VARCHAR(255),
    type     VARCHAR(255),
    CONSTRAINT pk_favorite_locations PRIMARY KEY (id)
);

CREATE TABLE friend_invitations
(
    id          BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    sender_id   UUID                                    NOT NULL,
    receiver_id UUID                                    NOT NULL,
    status      VARCHAR(255),
    sent_at     TIMESTAMP WITHOUT TIME ZONE             NOT NULL,
    CONSTRAINT pk_friend_invitations PRIMARY KEY (id)
);

CREATE TABLE gps_points
(
    id          BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    device_id   VARCHAR(255),
    user_id     UUID                                    NOT NULL,
    coordinates GEOMETRY(Point, 4326),
    timestamp   TIMESTAMP WITHOUT TIME ZONE,
    accuracy    DOUBLE PRECISION,
    battery     DOUBLE PRECISION,
    velocity    DOUBLE PRECISION,
    altitude    DOUBLE PRECISION,
    source_type VARCHAR(255),
    created_at  TIMESTAMP WITHOUT TIME ZONE,
    CONSTRAINT pk_gps_points PRIMARY KEY (id)
);

CREATE TABLE gps_source_config
(
    id            UUID         NOT NULL,
    user_id       UUID         NOT NULL,
    source_type   VARCHAR(255) NOT NULL,
    username      VARCHAR(255),
    password_hash VARCHAR(255),
    token         VARCHAR(255),
    active        BOOLEAN      NOT NULL,
    CONSTRAINT pk_gps_source_config PRIMARY KEY (id)
);

CREATE TABLE reverse_geocoding_location
(
    id                  BIGINT                      NOT NULL,
    request_coordinates GEOMETRY(Point, 4326)       NOT NULL,
    result_coordinates  GEOMETRY(Point, 4326),
    bounding_box        GEOMETRY(Polygon, 4326),
    display_name        VARCHAR(1000)               NOT NULL,
    provider_name       VARCHAR(50)                 NOT NULL,
    created_at          TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    last_accessed_at    TIMESTAMP WITHOUT TIME ZONE,
    CONSTRAINT pk_reverse_geocoding_location PRIMARY KEY (id)
);

CREATE TABLE shared_link
(
    id           UUID NOT NULL,
    name         VARCHAR(255),
    expires_at   TIMESTAMP WITHOUT TIME ZONE,
    password     VARCHAR(255),
    show_history BOOLEAN,
    user_id      UUID NOT NULL,
    created_at   TIMESTAMP WITHOUT TIME ZONE,
    view_count   INTEGER,
    CONSTRAINT pk_shared_link PRIMARY KEY (id)
);

CREATE TABLE user_friends
(
    id        BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    user_id   UUID                                    NOT NULL,
    friend_id UUID                                    NOT NULL,
    created_at TIMESTAMP WITHOUT TIME ZONE,
    CONSTRAINT pk_user_friends PRIMARY KEY (id)
);

CREATE TABLE users
(
    id                   UUID         NOT NULL,
    email                VARCHAR(254) NOT NULL,
    emailVerified        BOOLEAN      NOT NULL,
    password_hash        VARCHAR(255) NOT NULL,
    full_name            VARCHAR(100),
    created_at           TIMESTAMP WITHOUT TIME ZONE,
    updated_at           TIMESTAMP WITHOUT TIME ZONE,
    is_active            BOOLEAN,
    role                 VARCHAR(255),
    avatar               VARCHAR(500),
    timeline_preferences JSONB,
    CONSTRAINT pk_users PRIMARY KEY (id)
);

ALTER TABLE users
    ADD CONSTRAINT uc_users_email UNIQUE (email);

ALTER TABLE favorite_locations
    ADD CONSTRAINT FK_FAVORITE_LOCATIONS_ON_USER FOREIGN KEY (user_id) REFERENCES users (id);

ALTER TABLE friend_invitations
    ADD CONSTRAINT FK_FRIEND_INVITATIONS_ON_RECEIVER FOREIGN KEY (receiver_id) REFERENCES users (id);

ALTER TABLE friend_invitations
    ADD CONSTRAINT FK_FRIEND_INVITATIONS_ON_SENDER FOREIGN KEY (sender_id) REFERENCES users (id);

ALTER TABLE gps_points
    ADD CONSTRAINT FK_GPS_POINTS_ON_USER FOREIGN KEY (user_id) REFERENCES users (id);

ALTER TABLE gps_source_config
    ADD CONSTRAINT FK_GPS_SOURCE_CONFIG_ON_USER FOREIGN KEY (user_id) REFERENCES users (id);

ALTER TABLE shared_link
    ADD CONSTRAINT FK_SHARED_LINK_ON_USER FOREIGN KEY (user_id) REFERENCES users (id);

ALTER TABLE user_friends
    ADD CONSTRAINT FK_USER_FRIENDS_ON_FRIEND FOREIGN KEY (friend_id) REFERENCES users (id);

ALTER TABLE user_friends
    ADD CONSTRAINT FK_USER_FRIENDS_ON_USER FOREIGN KEY (user_id) REFERENCES users (id);

-- Indexes for foreign keys (performance)
CREATE INDEX idx_favorite_locations_user_id ON favorite_locations (user_id);
CREATE INDEX idx_friend_invitations_sender_id ON friend_invitations (sender_id);
CREATE INDEX idx_friend_invitations_receiver_id ON friend_invitations (receiver_id);
CREATE INDEX idx_gps_points_user_id ON gps_points (user_id);
CREATE INDEX idx_gps_source_config_user_id ON gps_source_config (user_id);
CREATE INDEX idx_shared_link_user_id ON shared_link (user_id);
CREATE INDEX idx_user_friends_user_id ON user_friends (user_id);
CREATE INDEX idx_user_friends_friend_id ON user_friends (friend_id);

-- Spatial indexes for PostGIS columns
CREATE INDEX idx_favorite_locations_geometry ON favorite_locations USING GIST (geometry);
CREATE INDEX idx_gps_points_coordinates ON gps_points USING GIST (coordinates);
CREATE INDEX idx_reverse_geocoding_request_coords ON reverse_geocoding_location USING GIST (request_coordinates);
CREATE INDEX idx_reverse_geocoding_bounding_box ON reverse_geocoding_location USING GIST (bounding_box);

-- Query performance indexes
CREATE INDEX idx_gps_points_timestamp ON gps_points (timestamp);
CREATE INDEX idx_gps_points_user_timestamp ON gps_points (user_id, timestamp);
CREATE INDEX idx_friend_invitations_status ON friend_invitations (status);