-- КОРИСНИЦИ

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
);

-- ============================================================
-- DriveNet — APPLICATION VIEWS
-- Target DB : drive_net / advdb_202526l_prj_drive_net
-- Views     : 9
--
--  1. v_available_rides        — ride-search results
--  2. v_driver_profile         — driver card with stats & vehicle
--  3. v_passenger_trip_history — passenger's full booking history
--  4. v_ride_manifest          — all passengers on a ride (driver view)
--  5. v_driver_earnings        — monthly revenue breakdown per driver
--  6. v_route_popularity       — most-booked routes ranking
--  7. v_booking_details        — single-booking detail (passenger view)
--  8. v_unread_notifications   — unread notification inbox per user
--  9. v_incident_summary       — incident reports with full ride context
-- ============================================================


-- ============================================================
-- 1. v_available_rides
-- Purpose : Ride-search screen — returns every scheduled ride
--           with driver info, vehicle, route, and price estimate.
-- Used by : Passenger "Find a ride" feature.
-- ============================================================
CREATE OR REPLACE VIEW v_available_rides AS
SELECT
    r.id                                                            AS ride_id,
    r.departure_time,
    r.seats_available,
    r.price_per_km,
    r.is_recurring,
    -- driver
    d.id                                                            AS driver_id,
    ud.name                                                         AS driver_name,
    ROUND(AVG(rt.score)::numeric, 2)                                AS driver_avg_rating,
    COUNT(rt.id)                                                    AS driver_rating_count,
    -- vehicle
    mfr.name || ' ' || vm.model_name                               AS vehicle,
    v.year                                                          AS vehicle_year,
    v.color,
    v.seats                                                         AS vehicle_total_seats,
    -- route
    orig_city.name                                                  AS origin_city,
    orig.name                                                       AS origin_location,
    dest_city.name                                                  AS destination_city,
    dest.name                                                       AS destination_location,
    ro.distance_km,
    ro.estimated_duration                                           AS duration_mins,
    ROUND((ro.distance_km * r.price_per_km)::numeric, 2)           AS estimated_fare
FROM rides r
JOIN drivers d              ON d.id = r.driver_id
JOIN users ud               ON ud.id = d.user_id
JOIN routes ro              ON ro.id = r.route_id
JOIN locations orig         ON orig.id = ro.origin_id
JOIN cities orig_city       ON orig_city.id = orig.city_id
JOIN locations dest         ON dest.id = ro.destination_id
JOIN cities dest_city       ON dest_city.id = dest.city_id
JOIN vehicles v             ON v.id = r.vehicle_id
JOIN vehicle_models vm      ON vm.id = v.model_id
JOIN manufacturers mfr      ON mfr.id = vm.manufacturer_id
LEFT JOIN ratings rt        ON rt.reviewee_user_id = ud.id
WHERE r.status = 'scheduled'
  AND ud.deleted_at IS NULL
GROUP BY
    r.id, r.departure_time, r.seats_available, r.price_per_km, r.is_recurring,
    d.id, ud.name,
    mfr.name, vm.model_name, v.year, v.color, v.seats,
    orig_city.name, orig.name, dest_city.name, dest.name,
    ro.distance_km, ro.estimated_duration;


-- ============================================================
-- 2. v_driver_profile
-- Purpose : Driver profile card — identity, license, primary
--           vehicle, ride counts and aggregate rating.
-- Used by : Driver profile page; passenger "view driver" modal.
-- ============================================================
CREATE OR REPLACE VIEW v_driver_profile AS
SELECT
    d.id                                                                    AS driver_id,
    ud.id                                                                   AS user_id,
    ud.name,
    ud.email,
    ud.phone,
    ud.is_verified,
    d.license_number,
    d.license_class,
    d.license_expiry,
    d.status                                                                AS driver_status,
    d.verified_at,
    -- primary active vehicle (lowest vehicle_id when multiple)
    COALESCE(mfr.name || ' ' || vm.model_name, 'No vehicle')               AS primary_vehicle,
    v.year                                                                  AS vehicle_year,
    v.color,
    v.license_plate,
    -- ride stats
    COUNT(DISTINCT r.id)                                                    AS total_rides,
    COUNT(DISTINCT r.id) FILTER (WHERE r.status = 'completed')             AS completed_rides,
    COUNT(DISTINCT r.id) FILTER (WHERE r.status = 'cancelled')             AS cancelled_rides,
    -- rating stats
    ROUND(AVG(rt.score)::numeric, 2)                                        AS avg_rating,
    COUNT(rt.id)                                                            AS rating_count
FROM drivers d
JOIN users ud               ON ud.id = d.user_id
-- one active vehicle per driver
LEFT JOIN LATERAL (
    SELECT vo2.vehicle_id
    FROM vehicle_ownership vo2
    WHERE vo2.driver_id = d.id
      AND vo2.is_active  = TRUE
    ORDER BY vo2.vehicle_id
    LIMIT 1
) pv                        ON TRUE
LEFT JOIN vehicles v        ON v.id = pv.vehicle_id
LEFT JOIN vehicle_models vm ON vm.id = v.model_id
LEFT JOIN manufacturers mfr ON mfr.id = vm.manufacturer_id
LEFT JOIN rides r           ON r.driver_id = d.id
LEFT JOIN ratings rt        ON rt.reviewee_user_id = ud.id
WHERE ud.deleted_at IS NULL
GROUP BY
    d.id, ud.id, ud.name, ud.email, ud.phone, ud.is_verified,
    d.license_number, d.license_class, d.license_expiry, d.status, d.verified_at,
    mfr.name, vm.model_name, v.year, v.color, v.license_plate;


-- ============================================================
-- 3. v_passenger_trip_history
-- Purpose : Full booking history for every passenger — status,
--           route, driver name and final fare paid.
-- Used by : Passenger "My trips" screen.
-- ============================================================
CREATE OR REPLACE VIEW v_passenger_trip_history AS
SELECT
    b.id                            AS booking_id,
    b.ride_id,
    -- passenger
    up.id                           AS passenger_user_id,
    up.name                         AS passenger_name,
    -- ride
    r.departure_time,
    r.status                        AS ride_status,
    b.status                        AS booking_status,
    -- route
    orig_city.name                  AS origin_city,
    dest_city.name                  AS destination_city,
    ro.distance_km,
    -- driver
    ud.name                         AS driver_name,
    ud.phone                        AS driver_phone,
    -- timing
    b.created_at                    AS booked_at,
    b.pickup_confirmed_at,
    b.dropoff_confirmed_at,
    -- fare
    bff.total_amount                AS fare_paid,
    -- rating given by this passenger for this ride
    rt.score                        AS rating_given
FROM bookings b
JOIN passengers p           ON p.id  = b.passenger_id
JOIN users up               ON up.id = p.user_id
JOIN rides r                ON r.id  = b.ride_id
JOIN drivers d              ON d.id  = r.driver_id
JOIN users ud               ON ud.id = d.user_id
JOIN routes ro              ON ro.id = r.route_id
JOIN locations orig         ON orig.id = ro.origin_id
JOIN cities orig_city       ON orig_city.id = orig.city_id
JOIN locations dest         ON dest.id = ro.destination_id
JOIN cities dest_city       ON dest_city.id = dest.city_id
LEFT JOIN booking_final_fare bff ON bff.booking_id = b.id
LEFT JOIN ratings rt        ON rt.reviewer_user_id = up.id
                           AND rt.ride_id          = b.ride_id
WHERE up.deleted_at IS NULL;


-- ============================================================
-- 4. v_ride_manifest
-- Purpose : All active passengers on each ride with pickup /
--           drop-off locations and contact details.
-- Used by : Driver "My passengers" screen before/during a ride.
-- ============================================================
CREATE OR REPLACE VIEW v_ride_manifest AS
SELECT
    r.id                            AS ride_id,
    r.departure_time,
    r.status                        AS ride_status,
    -- route
    orig_city.name                  AS origin_city,
    dest_city.name                  AS destination_city,
    -- booking
    b.id                            AS booking_id,
    b.status                        AS booking_status,
    -- passenger
    up.id                           AS passenger_user_id,
    up.name                         AS passenger_name,
    up.phone                        AS passenger_phone,
    -- stop names
    pup_loc.name                    AS pickup_location,
    pdrop_loc.name                  AS dropoff_location,
    -- fare
    bff.total_amount                AS fare
FROM rides r
JOIN routes ro                  ON ro.id  = r.route_id
JOIN locations orig             ON orig.id = ro.origin_id
JOIN cities orig_city           ON orig_city.id = orig.city_id
JOIN locations dest             ON dest.id = ro.destination_id
JOIN cities dest_city           ON dest_city.id = dest.city_id
JOIN bookings b                 ON b.ride_id = r.id
JOIN passengers p               ON p.id  = b.passenger_id
JOIN users up                   ON up.id = p.user_id
JOIN route_stops pup_stop       ON pup_stop.id = b.pickup_stop_id
JOIN locations pup_loc          ON pup_loc.id  = pup_stop.location_id
JOIN route_stops pdrop_stop     ON pdrop_stop.id = b.dropoff_stop_id
JOIN locations pdrop_loc        ON pdrop_loc.id  = pdrop_stop.location_id
LEFT JOIN booking_final_fare bff ON bff.booking_id = b.id
WHERE b.status IN ('confirmed', 'picked_up', 'completed')
  AND up.deleted_at IS NULL;


-- ============================================================
-- 5. v_driver_earnings
-- Purpose : Monthly revenue per driver from completed bookings.
-- Used by : Driver "Earnings" dashboard; finance reports.
-- ============================================================
CREATE OR REPLACE VIEW v_driver_earnings AS
SELECT
    d.id                                                            AS driver_id,
    ud.name                                                         AS driver_name,
    DATE_TRUNC('month', r.departure_time)                          AS month,
    COUNT(DISTINCT r.id)                                            AS completed_rides,
    COUNT(b.id)                                                     AS paid_bookings,
    ROUND(SUM(bff.total_amount)::numeric,  2)                       AS gross_revenue,
    ROUND(AVG(bff.total_amount)::numeric,  2)                       AS avg_fare_per_booking,
    ROUND(MAX(bff.total_amount)::numeric,  2)                       AS max_fare,
    ROUND(MIN(bff.total_amount)::numeric,  2)                       AS min_fare
FROM drivers d
JOIN users ud               ON ud.id = d.user_id
JOIN rides r                ON r.driver_id = d.id
                           AND r.status    = 'completed'
JOIN bookings b             ON b.ride_id = r.id
                           AND b.status  = 'completed'
JOIN booking_final_fare bff ON bff.booking_id = b.id
WHERE ud.deleted_at IS NULL
GROUP BY
    d.id, ud.name,
    DATE_TRUNC('month', r.departure_time);


-- ============================================================
-- 6. v_route_popularity
-- Purpose : Routes ranked by total bookings — useful for
--           surfacing top-demand corridors and pricing hints.
-- Used by : Home-screen "Popular routes" widget; analytics.
-- ============================================================
CREATE OR REPLACE VIEW v_route_popularity AS
SELECT
    ro.id                                                           AS route_id,
    orig_city.name                                                  AS origin_city,
    orig.name                                                       AS origin_location,
    dest_city.name                                                  AS destination_city,
    dest.name                                                       AS destination_location,
    ro.distance_km,
    ro.estimated_duration                                           AS duration_mins,
    COUNT(DISTINCT r.id)                                            AS total_rides,
    COUNT(b.id)                                                     AS total_bookings,
    COUNT(b.id) FILTER (WHERE b.status = 'completed')              AS completed_bookings,
    ROUND(AVG(bff.total_amount)::numeric, 2)                        AS avg_fare,
    ROUND(AVG(r.price_per_km)::numeric, 4)                         AS avg_price_per_km
FROM routes ro
JOIN locations orig          ON orig.id = ro.origin_id
JOIN cities orig_city        ON orig_city.id = orig.city_id
JOIN locations dest          ON dest.id = ro.destination_id
JOIN cities dest_city        ON dest_city.id = dest.city_id
LEFT JOIN rides r            ON r.route_id = ro.id
LEFT JOIN bookings b         ON b.ride_id  = r.id
LEFT JOIN booking_final_fare bff ON bff.booking_id = b.id
GROUP BY
    ro.id, orig_city.name, orig.name, dest_city.name, dest.name,
    ro.distance_km, ro.estimated_duration
ORDER BY total_bookings DESC;


-- ============================================================
-- 7. v_booking_details
-- Purpose : Complete single-booking record — passenger, driver,
--           vehicle, pickup/drop-off stops, and fare.
-- Used by : Booking confirmation screen; receipt / PDF export.
-- ============================================================
CREATE OR REPLACE VIEW v_booking_details AS
SELECT
    b.id                            AS booking_id,
    b.status                        AS booking_status,
    b.created_at                    AS booked_at,
    b.pickup_confirmed_at,
    b.dropoff_confirmed_at,
    -- passenger
    up.id                           AS passenger_user_id,
    up.name                         AS passenger_name,
    up.phone                        AS passenger_phone,
    -- driver
    ud.id                           AS driver_user_id,
    ud.name                         AS driver_name,
    ud.phone                        AS driver_phone,
    -- ride
    r.id                            AS ride_id,
    r.departure_time,
    r.status                        AS ride_status,
    r.price_per_km,
    -- vehicle
    mfr.name || ' ' || vm.model_name AS vehicle,
    v.license_plate,
    v.color,
    -- stops
    pup_loc.name                    AS pickup_location,
    orig_city.name                  AS pickup_city,
    drop_loc.name                   AS dropoff_location,
    dest_city.name                  AS dropoff_city,
    ro.distance_km,
    -- fare
    bff.total_amount                AS fare_paid,
    bff.calculated_at               AS fare_calculated_at
FROM bookings b
JOIN passengers p               ON p.id  = b.passenger_id
JOIN users up                   ON up.id = p.user_id
JOIN rides r                    ON r.id  = b.ride_id
JOIN drivers d                  ON d.id  = r.driver_id
JOIN users ud                   ON ud.id = d.user_id
JOIN vehicles v                 ON v.id  = r.vehicle_id
JOIN vehicle_models vm          ON vm.id = v.model_id
JOIN manufacturers mfr          ON mfr.id = vm.manufacturer_id
JOIN routes ro                  ON ro.id  = r.route_id
JOIN locations orig             ON orig.id = ro.origin_id
JOIN cities orig_city           ON orig_city.id = orig.city_id
JOIN locations dest             ON dest.id = ro.destination_id
JOIN cities dest_city           ON dest_city.id = dest.city_id
JOIN route_stops pup_stop       ON pup_stop.id = b.pickup_stop_id
JOIN locations pup_loc          ON pup_loc.id  = pup_stop.location_id
JOIN route_stops drop_stop      ON drop_stop.id = b.dropoff_stop_id
JOIN locations drop_loc         ON drop_loc.id  = drop_stop.location_id
LEFT JOIN booking_final_fare bff ON bff.booking_id = b.id;


-- ============================================================
-- 8. v_unread_notifications
-- Purpose : All unread notifications with user context.
-- Used by : Notification bell / inbox in the mobile app.
-- ============================================================
CREATE OR REPLACE VIEW v_unread_notifications AS
SELECT
    n.id                    AS notification_id,
    n.user_id,
    u.name                  AS user_name,
    n.type,
    n.title,
    n.body,
    n.sent_at,
    -- age in minutes so the app can show "5 min ago"
    EXTRACT(EPOCH FROM (NOW() - n.sent_at)) / 60    AS age_mins
FROM notifications n
JOIN users u ON u.id = n.user_id
WHERE n.read_at    IS NULL
  AND u.deleted_at IS NULL;


-- ============================================================
-- 9. v_incident_summary
-- Purpose : All incident reports enriched with ride, reporter
--           and driver details for the admin safety dashboard.
-- Used by : Admin "Incidents" panel; safety team review.
-- ============================================================
CREATE OR REPLACE VIEW v_incident_summary AS
SELECT
    ir.id                           AS incident_id,
    ir.type                         AS incident_type,
    ir.description,
    ir.reported_at,
    -- reporter (passenger)
    u_rep.id                        AS reporter_user_id,
    u_rep.name                      AS reporter_name,
    u_rep.phone                     AS reporter_phone,
    -- ride
    r.id                            AS ride_id,
    r.departure_time,
    r.status                        AS ride_status,
    -- driver
    u_drv.id                        AS driver_user_id,
    u_drv.name                      AS driver_name,
    u_drv.phone                     AS driver_phone,
    d.license_number                AS driver_license,
    -- route
    orig_city.name                  AS origin_city,
    dest_city.name                  AS destination_city,
    ro.distance_km
FROM incident_reports ir
JOIN users u_rep            ON u_rep.id = ir.reporter_id
JOIN rides r                ON r.id     = ir.ride_id
JOIN drivers d              ON d.id     = r.driver_id
JOIN users u_drv            ON u_drv.id = d.user_id
JOIN routes ro              ON ro.id    = r.route_id
JOIN locations orig         ON orig.id  = ro.origin_id
JOIN cities orig_city       ON orig_city.id = orig.city_id
JOIN locations dest         ON dest.id  = ro.destination_id
JOIN cities dest_city       ON dest_city.id = dest.city_id
WHERE u_rep.deleted_at IS NULL;