-- Schema
CREATE TABLE IF NOT EXISTS org_units (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL
);
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(120) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('user','driver','admin') NOT NULL DEFAULT 'user',
  org_unit_id INT NULL,
  is_active TINYINT(1) DEFAULT 1,
  FOREIGN KEY (org_unit_id) REFERENCES org_units(id)
);
CREATE TABLE IF NOT EXISTS vehicles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  plate_no VARCHAR(16) UNIQUE NOT NULL,
  label VARCHAR(80) NULL,
  type ENUM('MPV_MID','MPV_ECO','SUV_ECO') NOT NULL,
  seats TINYINT DEFAULT 7,
  is_active TINYINT(1) DEFAULT 1
);
CREATE TABLE IF NOT EXISTS drivers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNIQUE NOT NULL,
  license_no VARCHAR(64) NULL,
  phone VARCHAR(32) NULL,
  is_active TINYINT(1) DEFAULT 1,
  FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE IF NOT EXISTS driver_vehicle_pairings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  driver_id INT NOT NULL,
  vehicle_id INT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NULL,
  is_primary TINYINT(1) DEFAULT 1,
  UNIQUE KEY uniq_active_pair (driver_id, vehicle_id, start_date),
  FOREIGN KEY (driver_id) REFERENCES drivers(id),
  FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
);
CREATE TABLE IF NOT EXISTS ride_requests (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  requester_id INT NOT NULL,
  org_unit_id INT NOT NULL,
  vehicle_type ENUM('MPV_MID','MPV_ECO','SUV_ECO') NOT NULL,
  pickup_time DATETIME NOT NULL,
  dropoff_eta DATETIME NULL,
  pickup_lat DECIMAL(10,7) NOT NULL,
  pickup_lon DECIMAL(10,7) NOT NULL,
  pickup_poi VARCHAR(120) NULL,
  drop_lat DECIMAL(10,7) NOT NULL,
  drop_lon DECIMAL(10,7) NOT NULL,
  drop_poi VARCHAR(120) NULL,
  passenger_name VARCHAR(100) NOT NULL,
  notes TEXT NULL,
  status ENUM('PENDING','ASSIGNED','REJECTED','CANCELLED','COMPLETED') DEFAULT 'PENDING',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (requester_id) REFERENCES users(id),
  FOREIGN KEY (org_unit_id) REFERENCES org_units(id),
  INDEX (vehicle_type, pickup_time),
  INDEX (status, pickup_time)
);
CREATE TABLE IF NOT EXISTS ride_assignments (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  ride_request_id BIGINT NOT NULL,
  driver_id INT NOT NULL,
  vehicle_id INT NOT NULL,
  assigned_by INT NULL,
  assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  driver_status ENUM('PENDING','ACCEPTED','DECLINED') DEFAULT 'PENDING',
  FOREIGN KEY (ride_request_id) REFERENCES ride_requests(id),
  FOREIGN KEY (driver_id) REFERENCES drivers(id),
  FOREIGN KEY (vehicle_id) REFERENCES vehicles(id),
  UNIQUE KEY uniq_req (ride_request_id)
);
CREATE TABLE IF NOT EXISTS trips (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  ride_request_id BIGINT UNIQUE NOT NULL,
  driver_id INT NOT NULL,
  vehicle_id INT NOT NULL,
  start_time DATETIME NULL,
  start_lat DECIMAL(10,7) NULL,
  start_lon DECIMAL(10,7) NULL,
  finish_time DATETIME NULL,
  finish_lat DECIMAL(10,7) NULL,
  finish_lon DECIMAL(10,7) NULL,
  distance_km DECIMAL(8,2) NULL,
  status ENUM('NOT_STARTED','ONGOING','FINISHED','ABORTED') DEFAULT 'NOT_STARTED',
  FOREIGN KEY (ride_request_id) REFERENCES ride_requests(id),
  FOREIGN KEY (driver_id) REFERENCES drivers(id),
  FOREIGN KEY (vehicle_id) REFERENCES vehicles(id),
  INDEX (status, start_time)
);
