-- TripMeter Admin Panel - Database Schema
-- Import this file first (e.g. via phpMyAdmin or `mysql -u root -p < schema.sql`)

CREATE DATABASE IF NOT EXISTS tripmeter CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE tripmeter;

-- ---------------------------------------------------------------
-- Admin / Manager logins
-- ---------------------------------------------------------------
CREATE TABLE users (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    username      VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name     VARCHAR(100),
    role          ENUM('admin','manager') DEFAULT 'manager',
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Default login: username = admin / password = admin123
-- (CHANGE THIS after first login — see README)
INSERT INTO users (username, password_hash, full_name, role)
VALUES ('admin', '$2b$10$mfnbBJX0cuCEGYaOETBOveRsHo21riNkTabeQ5N2w5Iy5n15ZzJ9u', 'Administrator', 'admin');

-- ---------------------------------------------------------------
-- Employees
-- ---------------------------------------------------------------
CREATE TABLE employees (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    employee_code VARCHAR(50) UNIQUE,
    name          VARCHAR(100) NOT NULL,
    department    VARCHAR(100),
    vehicle_type  ENUM('Bike','Scooter','Car') DEFAULT 'Bike',
    rate_per_km   DECIMAL(6,2) DEFAULT 3.50,
    active        TINYINT(1) DEFAULT 1,
    -- Live location (updated on every ping from the app; NULL until first ping ever arrives)
    last_lat        DECIMAL(10,7) NULL,
    last_lng         DECIMAL(10,7) NULL,
    last_accuracy_m   DECIMAL(6,1) NULL,
    last_speed_kmh     DECIMAL(6,1) NULL,
    last_seen_at        DATETIME NULL,
    on_trip               TINYINT(1) DEFAULT 0,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- Quick-pick purpose chips (mirrors app Settings > purpose chips)
-- ---------------------------------------------------------------
CREATE TABLE purposes (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    name   VARCHAR(100) NOT NULL,
    active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

INSERT INTO purposes (name) VALUES
('Site Visit'), ('Client Meeting'), ('Material Purchase'), ('Bank Work'),
('Government Office'), ('Document Submission'), ('Delivery'), ('Other');

-- ---------------------------------------------------------------
-- Trips (one row per trip, matches CSV export + anti-fraud fields)
-- ---------------------------------------------------------------
CREATE TABLE trips (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    employee_id       INT NOT NULL,
    trip_date         DATE NOT NULL,
    start_time        DATETIME NOT NULL,
    end_time          DATETIME NULL,
    duration_minutes  INT DEFAULT 0,
    from_location     VARCHAR(255),
    to_location       VARCHAR(255),
    purpose           VARCHAR(255) NOT NULL,
    purpose_edited    TINYINT(1) DEFAULT 0,   -- flagged if changed within 24h window
    client             VARCHAR(150),
    vehicle_type       ENUM('Bike','Scooter','Car'),
    distance_km        DECIMAL(8,2) NOT NULL DEFAULT 0,
    rate_per_km        DECIMAL(6,2) NOT NULL DEFAULT 0,
    amount              DECIMAL(10,2) NOT NULL DEFAULT 0,
    gps_points_count    INT DEFAULT 0,
    mock_gps             TINYINT(1) DEFAULT 0,  -- fake-GPS app detected
    status                ENUM('pending','approved','rejected') DEFAULT 'pending',
    approved_by           INT NULL,
    approved_at           DATETIME NULL,
    notes                 TEXT,
    source                ENUM('csv_import','api_sync','manual') DEFAULT 'csv_import',
    created_at            TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- Global settings (default rates per vehicle, shown when adding an employee)
-- ---------------------------------------------------------------
CREATE TABLE settings (
    setting_key   VARCHAR(50) PRIMARY KEY,
    setting_value VARCHAR(255)
) ENGINE=InnoDB;

INSERT INTO settings (setting_key, setting_value) VALUES
('rate_bike', '3.50'),
('rate_scooter', '3.50'),
('rate_car', '10.00');

-- ---------------------------------------------------------------
-- API keys — for future direct sync from the app (once the Flutter
-- source is updated to POST trips / location instead of only local SQLite)
-- ---------------------------------------------------------------
CREATE TABLE api_keys (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    api_key     VARCHAR(64) UNIQUE NOT NULL,
    active      TINYINT(1) DEFAULT 1,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- Live location breadcrumb trail — one row per GPS ping sent while
-- a trip is in progress. employees.last_lat/last_lng/last_seen_at
-- hold the current position for the map; this table holds history
-- so a manager can replay a trip's route.
-- ---------------------------------------------------------------
CREATE TABLE location_pings (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    employee_id   INT NOT NULL,
    trip_id       INT NULL,          -- linked once the trip is imported/synced; NULL while trip is still in progress on-device
    latitude      DECIMAL(10,7) NOT NULL,
    longitude     DECIMAL(10,7) NOT NULL,
    accuracy_m    DECIMAL(6,1) NULL,
    speed_kmh     DECIMAL(6,1) NULL,
    recorded_at   DATETIME NOT NULL,   -- timestamp from the phone
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- timestamp the server received it
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (trip_id) REFERENCES trips(id) ON DELETE SET NULL,
    INDEX idx_employee_time (employee_id, recorded_at)
) ENGINE=InnoDB;