-- Full baseline: all tables for a new database.
-- Apply with: npm run schema:load   (then npm run migrate && npm run seed)
-- Incremental changes go in sql/migrations/*.sql and npm run migrate
--
-- Table order: plans → users → transport_owners (owner may reference user login)
-- TrackIt Super Admin schema
-- CREATE DATABASE IF NOT EXISTS mdsstech_parcel_go;
-- USE mdsstech_parcel_go;

CREATE TABLE IF NOT EXISTS subscription_plans (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  price_monthly DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT 'INR: per month if monthly cycle, total per year if yearly',
  max_vehicles INT UNSIGNED NOT NULL DEFAULT 10,
  billing_cycle ENUM('monthly', 'yearly') NOT NULL DEFAULT 'monthly',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_plans_active (is_active)
);

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  mobile VARCHAR(20) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(128) NOT NULL,
  role ENUM('super_admin', 'transport_owner', 'driver') NOT NULL DEFAULT 'transport_owner',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_users_mobile (mobile),
  KEY idx_users_role (role)
);

CREATE TABLE IF NOT EXISTS transport_owners (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_name VARCHAR(255) NOT NULL,
  contact_email VARCHAR(255) NOT NULL,
  phone VARCHAR(64) NULL,
  address TEXT NULL,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  subscription_plan_id INT UNSIGNED NULL,
  subscription_started_at DATE NULL COMMENT 'Current billing period start',
  subscription_renews_at DATE NULL COMMENT 'Next renewal due date',
  user_id INT UNSIGNED NULL COMMENT 'Linked transport_owner app login',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_owner_plan FOREIGN KEY (subscription_plan_id) REFERENCES subscription_plans (id) ON DELETE SET NULL,
  CONSTRAINT fk_owner_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL,
  KEY idx_owners_status (status),
  KEY idx_owners_renews (subscription_renews_at)
);

CREATE TABLE IF NOT EXISTS fleet_drivers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  transport_owner_id INT UNSIGNED NOT NULL,
  name VARCHAR(128) NOT NULL,
  mobile VARCHAR(20) NULL,
  aadhaar VARCHAR(12) NULL COMMENT '12-digit UID',
  status ENUM('available', 'in_trip', 'inactive') NOT NULL DEFAULT 'available',
  user_id INT UNSIGNED NULL COMMENT 'Linked driver app login',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_fd_owner FOREIGN KEY (transport_owner_id) REFERENCES transport_owners (id) ON DELETE CASCADE,
  CONSTRAINT fk_fd_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL,
  KEY idx_fd_owner (transport_owner_id),
  KEY idx_fd_status (status)
);

CREATE TABLE IF NOT EXISTS fleet_vehicles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  transport_owner_id INT UNSIGNED NOT NULL,
  registration_no VARCHAR(64) NOT NULL,
  status ENUM('available', 'in_trip', 'inactive') NOT NULL DEFAULT 'available',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_fv_owner FOREIGN KEY (transport_owner_id) REFERENCES transport_owners (id) ON DELETE CASCADE,
  KEY idx_fv_owner (transport_owner_id),
  KEY idx_fv_status (status)
);

CREATE TABLE IF NOT EXISTS fleet_trips (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  transport_owner_id INT UNSIGNED NOT NULL,
  driver_id INT UNSIGNED NULL,
  vehicle_id INT UNSIGNED NULL,
  status ENUM('active', 'completed') NOT NULL DEFAULT 'active',
  parcels_delivered INT UNSIGNED NOT NULL DEFAULT 0,
  revenue DECIMAL(12,2) NOT NULL DEFAULT 0,
  expenses DECIMAL(12,2) NOT NULL DEFAULT 0,
  started_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at TIMESTAMP NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_ft_owner FOREIGN KEY (transport_owner_id) REFERENCES transport_owners (id) ON DELETE CASCADE,
  CONSTRAINT fk_ft_driver FOREIGN KEY (driver_id) REFERENCES fleet_drivers (id) ON DELETE SET NULL,
  CONSTRAINT fk_ft_vehicle FOREIGN KEY (vehicle_id) REFERENCES fleet_vehicles (id) ON DELETE SET NULL,
  KEY idx_ft_owner (transport_owner_id),
  KEY idx_ft_status (status)
);

CREATE TABLE IF NOT EXISTS delivery_contacts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  transport_owner_id INT UNSIGNED NOT NULL,
  name VARCHAR(128) NOT NULL,
  default_address TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_dc_owner FOREIGN KEY (transport_owner_id) REFERENCES transport_owners (id) ON DELETE CASCADE,
  UNIQUE KEY uq_dc_owner_name (transport_owner_id, name),
  KEY idx_dc_owner (transport_owner_id)
);

CREATE TABLE IF NOT EXISTS trip_parcels (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  transport_owner_id INT UNSIGNED NOT NULL,
  trip_id INT UNSIGNED NOT NULL,
  delivery_contact_id INT UNSIGNED NULL,
  delivery_name VARCHAR(128) NOT NULL,
  delivery_address TEXT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  description TEXT NULL,
  status ENUM('pending', 'out_for_delivery', 'delivered', 'failed') NOT NULL DEFAULT 'pending',
  delivered_at TIMESTAMP NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_tp_owner FOREIGN KEY (transport_owner_id) REFERENCES transport_owners (id) ON DELETE CASCADE,
  CONSTRAINT fk_tp_trip FOREIGN KEY (trip_id) REFERENCES fleet_trips (id) ON DELETE CASCADE,
  CONSTRAINT fk_tp_contact FOREIGN KEY (delivery_contact_id) REFERENCES delivery_contacts (id) ON DELETE SET NULL,
  KEY idx_tp_trip (trip_id),
  KEY idx_tp_owner (transport_owner_id),
  KEY idx_tp_status (status)
);

CREATE TABLE IF NOT EXISTS trip_expenses (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  transport_owner_id INT UNSIGNED NOT NULL,
  trip_id INT UNSIGNED NOT NULL,
  driver_id INT UNSIGNED NULL,
  type ENUM('diesel', 'toll', 'maintenance', 'other') NOT NULL DEFAULT 'other',
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  note VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_te_owner FOREIGN KEY (transport_owner_id) REFERENCES transport_owners (id) ON DELETE CASCADE,
  CONSTRAINT fk_te_trip FOREIGN KEY (trip_id) REFERENCES fleet_trips (id) ON DELETE CASCADE,
  CONSTRAINT fk_te_driver FOREIGN KEY (driver_id) REFERENCES fleet_drivers (id) ON DELETE SET NULL,
  KEY idx_te_trip (trip_id),
  KEY idx_te_owner (transport_owner_id)
);