-- КОРИСНИЦИ

CREATE TABLE users (
    id           SERIAL PRIMARY KEY,
    name         VARCHAR(100)    NOT NULL,
    email        VARCHAR(150)    NOT NULL UNIQUE,
    phone        VARCHAR(20)     NOT NULL UNIQUE,
    is_verified  BOOLEAN         NOT NULL DEFAULT FALSE,
    created_at   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at   TIMESTAMP       NULL,
    CONSTRAINT chk_users_email      CHECK (email LIKE '%@%.%'),
    CONSTRAINT chk_users_name       CHECK (LENGTH(TRIM(name)) > 0),
    CONSTRAINT chk_users_phone      CHECK (phone ~ '^\+389[2-9]\d{6,7}$'),
    CONSTRAINT chk_users_deleted_at CHECK (deleted_at IS NULL OR deleted_at > created_at)
);

INSERT INTO users (id, name, email, phone, is_verified)
VALUES (0, 'Deleted User', 'deleted@drivenet.internal', '+38921000000', FALSE);

SELECT setval('users_id_seq', 1, FALSE);


CREATE TABLE passengers (
    id                  SERIAL PRIMARY KEY,
    user_id             INT             NOT NULL UNIQUE,
    smoking             BOOLEAN         NOT NULL DEFAULT FALSE,
    pets                BOOLEAN         NOT NULL DEFAULT FALSE,
    max_detour_percent  DECIMAL(5,2)    NULL,
    is_active           BOOLEAN         NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_passengers_max_detour CHECK (
        max_detour_percent IS NULL
        OR (max_detour_percent >= 0 AND max_detour_percent <= 100)
    ),
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE RESTRICT
);

CREATE TABLE drivers (
    id              SERIAL PRIMARY KEY,
    user_id         INT             NOT NULL UNIQUE,
    license_number  VARCHAR(50)     NOT NULL UNIQUE,
    license_class   VARCHAR(10)     NOT NULL,
    license_expiry  DATE            NOT NULL,
    status          VARCHAR(20)     NOT NULL DEFAULT 'pending',
    verified_at     TIMESTAMP       NULL,
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_drivers_status       CHECK (status IN ('pending', 'active', 'suspended', 'banned')),
    CONSTRAINT chk_drivers_expiry       CHECK (license_expiry > '2000-01-01'),
    CONSTRAINT chk_drivers_verified_at  CHECK (
        verified_at IS NULL OR status IN ('active', 'suspended', 'banned')
    ),
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE RESTRICT
);

CREATE TABLE user_addresses (
    id       SERIAL PRIMARY KEY,
    user_id  INT             NOT NULL DEFAULT 0,
    label    VARCHAR(50)     NOT NULL,
    address  VARCHAR(200)    NOT NULL,
    lat      DECIMAL(10,7)   NOT NULL,
    lng      DECIMAL(10,7)   NOT NULL,
    CONSTRAINT chk_user_addresses_lat   CHECK (lat BETWEEN -90 AND 90),
    CONSTRAINT chk_user_addresses_lng   CHECK (lng BETWEEN -180 AND 180),
    CONSTRAINT chk_user_addresses_label CHECK (LENGTH(TRIM(label)) > 0),
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE SET DEFAULT
);


--ВОЗИЛА

CREATE TABLE manufacturers (
    id       SERIAL PRIMARY KEY,
    name     VARCHAR(100)    NOT NULL UNIQUE,
    country  VARCHAR(100)    NOT NULL,
    CONSTRAINT chk_manufacturers_name    CHECK (LENGTH(TRIM(name)) > 0),
    CONSTRAINT chk_manufacturers_country CHECK (LENGTH(TRIM(country)) > 0)
);

CREATE TABLE vehicle_models (
    id              SERIAL PRIMARY KEY,
    manufacturer_id INT             NOT NULL,
    model_name      VARCHAR(100)    NOT NULL,
    body_type       VARCHAR(50)     NULL,
    fuel_type       VARCHAR(50)     NULL,
    CONSTRAINT chk_vehicle_models_fuel       CHECK (
        fuel_type IS NULL
        OR fuel_type IN ('petrol', 'diesel', 'electric', 'hybrid', 'lpg')
    ),
    CONSTRAINT chk_vehicle_models_body       CHECK (
        body_type IS NULL
        OR body_type IN ('sedan', 'hatchback', 'suv', 'van', 'minivan', 'coupe', 'estate')
    ),
    CONSTRAINT chk_vehicle_models_name       CHECK (LENGTH(TRIM(model_name)) > 0),
    CONSTRAINT uq_vehicle_models_manufacturer UNIQUE (manufacturer_id, model_name),
    FOREIGN KEY (manufacturer_id) REFERENCES manufacturers(id)
        ON DELETE RESTRICT
);

CREATE TABLE vehicles (
    id            SERIAL PRIMARY KEY,
    model_id      INT             NOT NULL,
    year          INT             NOT NULL,
    seats         INT             NOT NULL,
    license_plate VARCHAR(20)     NOT NULL UNIQUE,
    vehicle_type  VARCHAR(50)     NULL,
    color         VARCHAR(50)     NULL,
    is_active     BOOLEAN         NOT NULL DEFAULT TRUE,
    CONSTRAINT chk_vehicles_year  CHECK (
        year >= 1980
        AND year <= EXTRACT(YEAR FROM CURRENT_DATE) + 1
    ),
    CONSTRAINT chk_vehicles_seats CHECK (seats >= 2 AND seats <= 9),
    CONSTRAINT chk_vehicles_type  CHECK (
        vehicle_type IS NULL
        OR vehicle_type IN ('car', 'van', 'minibus')
    ),
    CONSTRAINT chk_vehicles_plate CHECK (LENGTH(TRIM(license_plate)) > 0),
    FOREIGN KEY (model_id) REFERENCES vehicle_models(id)
        ON DELETE RESTRICT
);

CREATE TABLE vehicle_ownership (
    id         SERIAL PRIMARY KEY,
    driver_id  INT             NOT NULL,
    vehicle_id INT             NOT NULL,
    owned_from DATE            NOT NULL,
    owned_to   DATE            NULL,
    is_active  BOOLEAN         NOT NULL DEFAULT TRUE,
    CONSTRAINT chk_vehicle_ownership_dates CHECK (owned_to IS NULL OR owned_to > owned_from),
    FOREIGN KEY (driver_id)  REFERENCES drivers(id)
        ON DELETE RESTRICT,
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
        ON DELETE RESTRICT
);

CREATE UNIQUE INDEX uq_vehicle_ownership_active
    ON vehicle_ownership (vehicle_id)
    WHERE is_active = TRUE;


-- ЛОКАЦИИ И РУТИ

CREATE TABLE cities (
    id       SERIAL PRIMARY KEY,
    name     VARCHAR(100)    NOT NULL,
    country  VARCHAR(100)    NOT NULL,
    lat      DECIMAL(10,7)   NOT NULL,
    lng      DECIMAL(10,7)   NOT NULL,
    CONSTRAINT uq_cities_name_country CHECK (LENGTH(TRIM(name)) > 0),
    CONSTRAINT chk_cities_lat         CHECK (lat BETWEEN -90 AND 90),
    CONSTRAINT chk_cities_lng         CHECK (lng BETWEEN -180 AND 180),
    UNIQUE (name, country)
);

CREATE TABLE locations (
    id       SERIAL PRIMARY KEY,
    city_id  INT             NOT NULL,
    name     VARCHAR(150)    NOT NULL,
    address  VARCHAR(250)    NULL,
    lat      DECIMAL(10,7)   NOT NULL,
    lng      DECIMAL(10,7)   NOT NULL,
    CONSTRAINT chk_locations_lat  CHECK (lat BETWEEN -90 AND 90),
    CONSTRAINT chk_locations_lng  CHECK (lng BETWEEN -180 AND 180),
    CONSTRAINT chk_locations_name CHECK (LENGTH(TRIM(name)) > 0),
    FOREIGN KEY (city_id) REFERENCES cities(id)
        ON DELETE RESTRICT
);

CREATE TABLE routes (
    id                 SERIAL PRIMARY KEY,
    origin_id          INT             NOT NULL,
    destination_id     INT             NOT NULL,
    distance_km        DECIMAL(8,2)    NOT NULL,
    estimated_duration INT             NOT NULL,
    CONSTRAINT chk_routes_distance  CHECK (distance_km > 0),
    CONSTRAINT chk_routes_duration  CHECK (estimated_duration > 0),
    CONSTRAINT chk_routes_different CHECK (origin_id != destination_id),
    FOREIGN KEY (origin_id)      REFERENCES locations(id)
        ON DELETE RESTRICT,
    FOREIGN KEY (destination_id) REFERENCES locations(id)
        ON DELETE RESTRICT
);

CREATE TABLE route_stops (
    id             SERIAL PRIMARY KEY,
    route_id       INT             NOT NULL,
    location_id    INT             NOT NULL,
    stop_order     INT             NOT NULL,
    estimated_time TIME            NULL,
    CONSTRAINT chk_route_stops_order CHECK (stop_order >= 0),
    CONSTRAINT uq_route_stops_order  UNIQUE (route_id, stop_order),

    FOREIGN KEY (route_id)    REFERENCES routes(id)
        ON DELETE RESTRICT,
    FOREIGN KEY (location_id) REFERENCES locations(id)
        ON DELETE RESTRICT
);


--ВОЗЕЊА

CREATE TABLE rides (
    id                  SERIAL PRIMARY KEY,
    driver_id           INT             NOT NULL,
    vehicle_id          INT             NOT NULL,
    route_id            INT             NOT NULL,
    departure_time      TIMESTAMP       NOT NULL,
    status              VARCHAR(20)     NOT NULL DEFAULT 'scheduled',
    price_per_km        DECIMAL(8,2)    NOT NULL,
    seats_available     INT             NOT NULL,
    is_recurring        BOOLEAN         NOT NULL DEFAULT FALSE,
    recurrence_days     VARCHAR(50)     NULL,
    recurrence_end_date DATE            NULL,
    created_at          TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_rides_status         CHECK (
        status IN ('scheduled', 'in_progress', 'completed', 'cancelled')
    ),
    CONSTRAINT chk_rides_price          CHECK (price_per_km > 0),
    CONSTRAINT chk_rides_seats          CHECK (seats_available >= 0),
    CONSTRAINT chk_rides_recurrence     CHECK (
        is_recurring = FALSE
        OR (
            is_recurring = TRUE
            AND recurrence_days IS NOT NULL
            AND recurrence_end_date IS NOT NULL
        )
    ),
    CONSTRAINT chk_rides_recurrence_end CHECK (
        recurrence_end_date IS NULL
        OR recurrence_end_date > departure_time::DATE
    ),
    CONSTRAINT chk_rides_created_before_departure CHECK (created_at <= departure_time),
    FOREIGN KEY (driver_id)  REFERENCES drivers(id)
        ON DELETE RESTRICT,
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
        ON DELETE RESTRICT,
    FOREIGN KEY (route_id)   REFERENCES routes(id)
        ON DELETE RESTRICT
);

CREATE TABLE route_segments (
    id             SERIAL PRIMARY KEY,
    ride_id        INT             NOT NULL,
    from_stop_id   INT             NOT NULL,
    to_stop_id     INT             NOT NULL,
    distance_km    DECIMAL(8,2)    NOT NULL,
    segment_order  INT             NOT NULL,
    CONSTRAINT chk_route_segments_distance CHECK (distance_km > 0),
    CONSTRAINT chk_route_segments_order    CHECK (segment_order >= 0),
    CONSTRAINT chk_route_segments_stops    CHECK (from_stop_id != to_stop_id),
    CONSTRAINT uq_route_segments_order     UNIQUE (ride_id, segment_order),
    FOREIGN KEY (ride_id)      REFERENCES rides(id)
        ON DELETE RESTRICT,
    FOREIGN KEY (from_stop_id) REFERENCES route_stops(id)
        ON DELETE RESTRICT,
    FOREIGN KEY (to_stop_id)   REFERENCES route_stops(id)
        ON DELETE RESTRICT
);

CREATE TABLE ride_status_history (
    id          SERIAL PRIMARY KEY,
    ride_id     INT             NOT NULL,
    status      VARCHAR(20)     NOT NULL,
    changed_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    lat         DECIMAL(10,7)   NULL,
    lng         DECIMAL(10,7)   NULL,
    CONSTRAINT chk_ride_status_history_status CHECK (
        status IN ('scheduled', 'in_progress', 'completed', 'cancelled')
    ),
    CONSTRAINT chk_ride_status_history_lat CHECK (lat IS NULL OR lat BETWEEN -90 AND 90),
    CONSTRAINT chk_ride_status_history_lng CHECK (lng IS NULL OR lng BETWEEN -180 AND 180),
    FOREIGN KEY (ride_id) REFERENCES rides(id)
        ON DELETE RESTRICT
);


-- РЕЗЕРВАЦИИ

CREATE TABLE bookings (
    id                   SERIAL PRIMARY KEY,
    ride_id              INT             NOT NULL,
    passenger_id         INT             NOT NULL,
    pickup_stop_id       INT             NOT NULL,
    dropoff_stop_id      INT             NOT NULL,
    status               VARCHAR(20)     NOT NULL DEFAULT 'pending',
    created_at           TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    pickup_confirmed_at  TIMESTAMP       NULL,
    dropoff_confirmed_at TIMESTAMP       NULL,
    CONSTRAINT chk_bookings_status        CHECK (
        status IN ('pending', 'confirmed', 'picked_up', 'completed', 'cancelled')
    ),
    CONSTRAINT chk_bookings_stops         CHECK (pickup_stop_id != dropoff_stop_id),
    CONSTRAINT chk_bookings_dropoff_after CHECK (
        dropoff_confirmed_at IS NULL
        OR pickup_confirmed_at IS NULL
        OR dropoff_confirmed_at > pickup_confirmed_at
    ),
    CONSTRAINT chk_bookings_pickup_after_created CHECK (
        pickup_confirmed_at IS NULL
        OR pickup_confirmed_at >= created_at
    ),
    CONSTRAINT uq_bookings_passenger_ride UNIQUE (ride_id, passenger_id),
    FOREIGN KEY (ride_id)         REFERENCES rides(id)
        ON DELETE RESTRICT,
    FOREIGN KEY (passenger_id)    REFERENCES passengers(id)
        ON DELETE RESTRICT,
    FOREIGN KEY (pickup_stop_id)  REFERENCES route_stops(id)
        ON DELETE RESTRICT,
    FOREIGN KEY (dropoff_stop_id) REFERENCES route_stops(id)
        ON DELETE RESTRICT
);

CREATE TABLE booking_status_history (
    id          SERIAL PRIMARY KEY,
    booking_id  INT             NOT NULL,
    status      VARCHAR(20)     NOT NULL,
    changed_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reason      VARCHAR(300)    NULL,
    CONSTRAINT chk_booking_status_history_status CHECK (
        status IN ('pending', 'confirmed', 'picked_up', 'completed', 'cancelled')
    ),
    FOREIGN KEY (booking_id) REFERENCES bookings(id)
        ON DELETE RESTRICT
);


-- ЦЕНА И ПАТАРИНИ

CREATE TABLE passenger_segments (
    id                    SERIAL PRIMARY KEY,
    booking_id            INT             NOT NULL,
    segment_id            INT             NOT NULL,
    is_present            BOOLEAN         NOT NULL DEFAULT TRUE,
    segment_cost          DECIMAL(10,2)   NOT NULL,
    passengers_on_segment INT             NOT NULL,
    amount_charged        DECIMAL(10,2)   NOT NULL,
    CONSTRAINT chk_passenger_segments_cost       CHECK (segment_cost >= 0),
    CONSTRAINT chk_passenger_segments_passengers CHECK (passengers_on_segment >= 1),
    CONSTRAINT chk_passenger_segments_charged    CHECK (amount_charged >= 0),
    CONSTRAINT chk_passenger_segments_charged_le CHECK (amount_charged <= segment_cost),
    CONSTRAINT uq_passenger_segments             UNIQUE (booking_id, segment_id),
    FOREIGN KEY (booking_id) REFERENCES bookings(id)
        ON DELETE RESTRICT,
    FOREIGN KEY (segment_id) REFERENCES route_segments(id)
        ON DELETE RESTRICT
);

CREATE TABLE booking_final_fare (
    id             SERIAL PRIMARY KEY,
    booking_id     INT             NOT NULL UNIQUE,
    total_amount   DECIMAL(10,2)   NOT NULL,
    calculated_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_booking_final_fare_amount CHECK (total_amount >= 0),
    FOREIGN KEY (booking_id) REFERENCES bookings(id)
        ON DELETE RESTRICT
);

CREATE TABLE toll_points (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(150)    NOT NULL UNIQUE,
    lat         DECIMAL(10,7)   NOT NULL,
    lng         DECIMAL(10,7)   NOT NULL,
    road_name   VARCHAR(150)    NULL,
    price_car   DECIMAL(8,2)    NOT NULL,
    price_van   DECIMAL(8,2)    NOT NULL,
    CONSTRAINT chk_toll_points_lat       CHECK (lat BETWEEN -90 AND 90),
    CONSTRAINT chk_toll_points_lng       CHECK (lng BETWEEN -180 AND 180),
    CONSTRAINT chk_toll_points_price_car CHECK (price_car >= 0),
    CONSTRAINT chk_toll_points_price_van CHECK (price_van >= 0),
    CONSTRAINT chk_toll_points_name      CHECK (LENGTH(TRIM(name)) > 0)
);

CREATE TABLE ride_tolls (
    id                  SERIAL PRIMARY KEY,
    ride_id             INT             NOT NULL,
    toll_point_id       INT             NOT NULL,
    actual_amount_paid  DECIMAL(8,2)    NOT NULL,
    passed_at           TIMESTAMP       NOT NULL,
    CONSTRAINT chk_ride_tolls_amount CHECK (actual_amount_paid >= 0),
    CONSTRAINT uq_ride_tolls         UNIQUE (ride_id, toll_point_id),
    FOREIGN KEY (ride_id)       REFERENCES rides(id)
        ON DELETE RESTRICT,
    FOREIGN KEY (toll_point_id) REFERENCES toll_points(id)
        ON DELETE RESTRICT
);

CREATE TABLE toll_passenger_split (
    id           SERIAL PRIMARY KEY,
    ride_toll_id INT             NOT NULL,
    booking_id   INT             NOT NULL,
    amount_due   DECIMAL(8,2)    NOT NULL,
    CONSTRAINT chk_toll_passenger_split_amount CHECK (amount_due >= 0),
    CONSTRAINT uq_toll_passenger_split         UNIQUE (ride_toll_id, booking_id),
    FOREIGN KEY (ride_toll_id) REFERENCES ride_tolls(id)
        ON DELETE RESTRICT,
    FOREIGN KEY (booking_id)   REFERENCES bookings(id)
        ON DELETE RESTRICT
);


--ОЦЕНУВАЊЕ, ЧАТ И ИНЦИДЕНТИ

CREATE TABLE ratings (
    id               SERIAL PRIMARY KEY,
    reviewer_user_id INT             NOT NULL DEFAULT 0,
    reviewee_user_id INT             NOT NULL DEFAULT 0,
    ride_id          INT             NOT NULL,
    score            INT             NOT NULL,
    comment          TEXT            NULL,
    created_at       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_ratings_score   CHECK (score BETWEEN 1 AND 5),
    CONSTRAINT chk_ratings_no_self CHECK (reviewer_user_id != reviewee_user_id),
    CONSTRAINT uq_ratings_per_ride UNIQUE (reviewer_user_id, reviewee_user_id, ride_id),
    FOREIGN KEY (reviewer_user_id) REFERENCES users(id)
        ON DELETE SET DEFAULT,
    FOREIGN KEY (reviewee_user_id) REFERENCES users(id)
        ON DELETE SET DEFAULT,
    FOREIGN KEY (ride_id) REFERENCES rides(id)
        ON DELETE RESTRICT
);

-- VIEW за пресметка на просечна оцена на возачи
CREATE VIEW driver_ratings AS
SELECT
    d.id                          AS driver_id,
    u.name                        AS driver_name,
    ROUND(AVG(r.score)::NUMERIC, 2) AS avg_rating,
    COUNT(r.id)                   AS total_ratings
FROM drivers d
JOIN users u ON u.id = d.user_id
LEFT JOIN ratings r ON r.reviewee_user_id = d.user_id
GROUP BY d.id, u.name;

-- VIEW за пресметка на просечна оцена на патници
CREATE VIEW passenger_ratings AS
SELECT
    p.id                          AS passenger_id,
    u.name                        AS passenger_name,
    ROUND(AVG(r.score)::NUMERIC, 2) AS avg_rating,
    COUNT(r.id)                   AS total_ratings
FROM passengers p
JOIN users u ON u.id = p.user_id
LEFT JOIN ratings r ON r.reviewee_user_id = p.user_id
GROUP BY p.id, u.name;


CREATE TABLE chat_threads (
    id               SERIAL PRIMARY KEY,
    ride_id          INT             NOT NULL,
    participant_1_id INT             NOT NULL DEFAULT 0,
    participant_2_id INT             NOT NULL DEFAULT 0,
    created_at       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_chat_threads_no_self CHECK (participant_1_id != participant_2_id),
    CONSTRAINT uq_chat_threads          UNIQUE (ride_id, participant_1_id, participant_2_id),
    FOREIGN KEY (ride_id)          REFERENCES rides(id)
        ON DELETE RESTRICT,
    FOREIGN KEY (participant_1_id) REFERENCES users(id)
        ON DELETE SET DEFAULT,
    FOREIGN KEY (participant_2_id) REFERENCES users(id)
        ON DELETE SET DEFAULT
);

CREATE TABLE chat_messages (
    id          SERIAL PRIMARY KEY,
    thread_id   INT             NOT NULL,
    sender_id   INT             NOT NULL DEFAULT 0,
    content     TEXT            NOT NULL,
    sent_at     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_deleted  BOOLEAN         NOT NULL DEFAULT FALSE,
    CONSTRAINT chk_chat_messages_content CHECK (LENGTH(TRIM(content)) > 0),
    FOREIGN KEY (thread_id) REFERENCES chat_threads(id)
        ON DELETE RESTRICT,
    FOREIGN KEY (sender_id) REFERENCES users(id)
        ON DELETE SET DEFAULT
);

CREATE TABLE incident_reports (
    id           SERIAL PRIMARY KEY,
    ride_id      INT             NOT NULL,
    reporter_id  INT             NOT NULL DEFAULT 0,
    type         VARCHAR(50)     NOT NULL,
    description  TEXT            NOT NULL,
    reported_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_incident_reports_type        CHECK (
        type IN ('accident', 'reckless_driving', 'harassment', 'no_show', 'fraud', 'other')
    ),
    CONSTRAINT chk_incident_reports_description CHECK (LENGTH(TRIM(description)) > 0),
    FOREIGN KEY (ride_id)     REFERENCES rides(id)
        ON DELETE RESTRICT,
    FOREIGN KEY (reporter_id) REFERENCES users(id)
        ON DELETE SET DEFAULT
);


-- LIVE СЛЕДЕЊЕ

CREATE TABLE location_pings (
    id         SERIAL PRIMARY KEY,
    ride_id    INT             NOT NULL,
    user_id    INT             NOT NULL DEFAULT 0,
    lat        DECIMAL(10,7)   NOT NULL,
    lng        DECIMAL(10,7)   NOT NULL,
    pinged_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    speed      DECIMAL(6,2)    NULL,
    CONSTRAINT chk_location_pings_lat   CHECK (lat BETWEEN -90 AND 90),
    CONSTRAINT chk_location_pings_lng   CHECK (lng BETWEEN -180 AND 180),
    CONSTRAINT chk_location_pings_speed CHECK (speed IS NULL OR speed >= 0),
    FOREIGN KEY (ride_id) REFERENCES rides(id)
        ON DELETE RESTRICT,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE SET DEFAULT
);


-- СИСТЕМ

CREATE TABLE notifications (
    id       SERIAL PRIMARY KEY,
    user_id  INT             NOT NULL DEFAULT 0,
    type     VARCHAR(50)     NOT NULL,
    title    VARCHAR(200)    NOT NULL,
    body     TEXT            NOT NULL,
    read_at  TIMESTAMP       NULL,
    sent_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_notifications_type    CHECK (
        type IN ('booking', 'ride', 'chat', 'rating', 'system', 'incident')
    ),
    CONSTRAINT chk_notifications_read_at CHECK (read_at IS NULL OR read_at >= sent_at),
    CONSTRAINT chk_notifications_title   CHECK (LENGTH(TRIM(title)) > 0),
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE SET DEFAULT
);

CREATE TABLE audit_logs (
    id          SERIAL PRIMARY KEY,
    table_name  VARCHAR(100)    NOT NULL,
    record_id   INT             NOT NULL,
    operation   VARCHAR(10)     NOT NULL,
    old_data    TEXT            NULL,
    new_data    TEXT            NULL,
    changed_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    changed_by  INT             NULL DEFAULT 0,
    CONSTRAINT chk_audit_logs_operation  CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    CONSTRAINT chk_audit_logs_table_name CHECK (LENGTH(TRIM(table_name)) > 0),
    FOREIGN KEY (changed_by) REFERENCES users(id)
        ON DELETE SET DEFAULT
);