-- =====================================================================
--  FLEETWELL — Vehicle / Fleet Management ERP
--  MySQL schema + seed data.
--  Column names match the original prototype's field names exactly so the
--  API JSON contract is preserved. Reserved words are back-ticked.
--  Seed dates use CURDATE() +/- INTERVAL to mirror dateAgo()/dateAhead()
--  at install time.
-- =====================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ---------- auth users (new — for Login / Registration) ----------
CREATE TABLE IF NOT EXISTS `users` (
  `id`            INT AUTO_INCREMENT PRIMARY KEY,
  `name`          VARCHAR(120) NOT NULL,
  `email`         VARCHAR(160) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `company`       VARCHAR(160) DEFAULT 'Fleetwell Logistics',
  `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- company (single-row profile) ----------
CREATE TABLE IF NOT EXISTS `company` (
  `id`   INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(160) NOT NULL,
  `fy`   VARCHAR(40)  NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- masters ----------
CREATE TABLE IF NOT EXISTS `vehicle_types` (
  `id`   INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fuel_types` (
  `id`   INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(80) NOT NULL,
  `unit` VARCHAR(16) NOT NULL DEFAULT 'L'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `depots` (
  `id`       INT AUTO_INCREMENT PRIMARY KEY,
  `name`     VARCHAR(120) NOT NULL,
  `code`     VARCHAR(40),
  `location` VARCHAR(200)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `vendors` (
  `id`      INT AUTO_INCREMENT PRIMARY KEY,
  `name`    VARCHAR(160) NOT NULL,
  `type`    VARCHAR(40)  NOT NULL DEFAULT 'Garage',
  `contact` VARCHAR(120),
  `phone`   VARCHAR(60),
  `email`   VARCHAR(160),
  `gstin`   VARCHAR(40)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `drivers` (
  `id`          INT AUTO_INCREMENT PRIMARY KEY,
  `name`        VARCHAR(120) NOT NULL,
  `phone`       VARCHAR(60),
  `license_no`  VARCHAR(80),
  `license_exp` DATE,
  `depot_id`    INT DEFAULT 0,
  `status`      VARCHAR(20) DEFAULT 'Active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `vehicles` (
  `id`            INT AUTO_INCREMENT PRIMARY KEY,
  `reg_no`        VARCHAR(40) NOT NULL,
  `name`          VARCHAR(160),
  `type_id`       INT DEFAULT 0,
  `fuel_id`       INT DEFAULT 0,
  `year`          INT,
  `depot_id`      INT DEFAULT 0,
  `driver_id`     INT DEFAULT 0,
  `status`        VARCHAR(20) DEFAULT 'Active',
  `odo_open`      INT DEFAULT 0,
  `tank`          INT DEFAULT 0,
  `purchase_date` DATE,
  `purchase_cost` DECIMAL(14,2) DEFAULT 0,
  `color`         VARCHAR(40)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- documents / renewals ----------
CREATE TABLE IF NOT EXISTS `documents` (
  `id`         INT AUTO_INCREMENT PRIMARY KEY,
  `vehicle_id` INT NOT NULL,
  `type`       VARCHAR(40),
  `no`         VARCHAR(80),
  `vendor_id`  INT DEFAULT 0,
  `issue`      DATE,
  `expiry`     DATE,
  `amount`     DECIMAL(14,2) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- operations ----------
CREATE TABLE IF NOT EXISTS `trips` (
  `id`         INT AUTO_INCREMENT PRIMARY KEY,
  `no`         VARCHAR(40),
  `vehicle_id` INT NOT NULL,
  `driver_id`  INT DEFAULT 0,
  `date`       DATE,
  `from`       VARCHAR(160),
  `to`         VARCHAR(160),
  `odo_start`  INT DEFAULT 0,
  `odo_end`    INT DEFAULT 0,
  `purpose`    VARCHAR(160),
  `revenue`    DECIMAL(14,2) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `fuel_logs` (
  `id`         INT AUTO_INCREMENT PRIMARY KEY,
  `vehicle_id` INT NOT NULL,
  `driver_id`  INT DEFAULT 0,
  `date`       DATE,
  `odo`        INT DEFAULT 0,
  `qty`        DECIMAL(12,3) DEFAULT 0,
  `rate`       DECIMAL(12,2) DEFAULT 0,
  `vendor_id`  INT DEFAULT 0,
  `full`       TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `services` (
  `id`         INT AUTO_INCREMENT PRIMARY KEY,
  `no`         VARCHAR(40),
  `vehicle_id` INT NOT NULL,
  `date`       DATE,
  `odo`        INT DEFAULT 0,
  `type`       VARCHAR(40),
  `vendor_id`  INT DEFAULT 0,
  `parts`      DECIMAL(14,2) DEFAULT 0,
  `labor`      DECIMAL(14,2) DEFAULT 0,
  `desc`       VARCHAR(255),
  `next_odo`   INT DEFAULT 0,
  `next_date`  DATE NULL,
  `status`     VARCHAR(20) DEFAULT 'Closed'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `expenses` (
  `id`         INT AUTO_INCREMENT PRIMARY KEY,
  `vehicle_id` INT NOT NULL,
  `date`       DATE,
  `category`   VARCHAR(40),
  `amount`     DECIMAL(14,2) DEFAULT 0,
  `vendor_id`  INT DEFAULT 0,
  `remarks`    VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
--  SEED DATA
-- =====================================================================

INSERT INTO `company` (`id`,`name`,`fy`) VALUES (1,'Fleetwell Logistics','FY 2025-26');

INSERT INTO `vehicle_types` (`id`,`name`) VALUES
 (1,'Mini Truck'),(2,'LCV'),(3,'Van'),(4,'Truck'),(5,'Heavy Truck'),(6,'Auto'),(7,'Car');

INSERT INTO `fuel_types` (`id`,`name`,`unit`) VALUES
 (1,'Diesel','L'),(2,'Petrol','L'),(3,'CNG','kg'),(4,'Electric','kWh');

INSERT INTO `depots` (`id`,`name`,`code`,`location`) VALUES
 (1,'Central Depot','DEP-CEN','Patancheru, Hyderabad'),
 (2,'East Hub','DEP-EAST','Uppal, Hyderabad'),
 (3,'South Yard','DEP-STH','Shamshabad, Hyderabad');

INSERT INTO `vendors` (`id`,`name`,`type`,`contact`,`phone`,`email`,`gstin`) VALUES
 (1,'SpeedFix Auto Garage','Garage','Naveen K','+91 90000 41001','service@speedfix.in','36AABFS1234A1Z5'),
 (2,'HP Highway Fuel Station','Fuel','Station Mgr','+91 90000 41002','hp.uppal@fuel.in','36AABFH5678B1Z2'),
 (3,'IndiSure General Insurance','Insurer','Priya Menon','+91 90000 41003','fleet@indisure.com','36AABFI9012C1Z9'),
 (4,'AutoParts Hub','Parts','Rafiq A','+91 90000 41004','sales@autoparts.in','36AABFA3456D1Z1'),
 (5,'RTO / Highways Authority','RTO','—','—','—','—');

INSERT INTO `drivers` (`id`,`name`,`phone`,`license_no`,`license_exp`,`depot_id`,`status`) VALUES
 (1,'Ramesh Kumar','+91 90111 20001','TS0120190001234',DATE_ADD(CURDATE(),INTERVAL 420 DAY),1,'Active'),
 (2,'Anil Yadav','+91 90111 20002','TS0220180004567',DATE_ADD(CURDATE(),INTERVAL 18 DAY),1,'Active'),
 (3,'Suresh Babu','+91 90111 20003','TS0720210007890',DATE_ADD(CURDATE(),INTERVAL 700 DAY),2,'Active'),
 (4,'Imran Khan','+91 90111 20004','TS0820200002468',DATE_SUB(CURDATE(),INTERVAL 12 DAY),2,'Active'),
 (5,'Venkat Reddy','+91 90111 20005','TS0920170001357',DATE_ADD(CURDATE(),INTERVAL 260 DAY),1,'Active'),
 (6,'Mohan Das','+91 90111 20006','TS1020220009753',DATE_ADD(CURDATE(),INTERVAL 540 DAY),3,'Active');

INSERT INTO `vehicles` (`id`,`reg_no`,`name`,`type_id`,`fuel_id`,`year`,`depot_id`,`driver_id`,`status`,`odo_open`,`tank`,`purchase_date`,`purchase_cost`,`color`) VALUES
 (1,'TS09 UA 1234','Tata Ace Gold',1,1,2021,1,1,'Active',41200,40,DATE_SUB(CURDATE(),INTERVAL 1400 DAY),620000,'White'),
 (2,'TS09 UB 5678','Mahindra Bolero Pik-Up',2,1,2020,1,2,'Active',78400,60,DATE_SUB(CURDATE(),INTERVAL 1850 DAY),910000,'Silver'),
 (3,'TS07 UC 9012','Ashok Leyland Dost+',2,1,2022,2,3,'Active',33600,45,DATE_SUB(CURDATE(),INTERVAL 1000 DAY),840000,'Blue'),
 (4,'TS08 UD 3456','Maruti Eeco Cargo',3,2,2023,2,4,'Active',21800,32,DATE_SUB(CURDATE(),INTERVAL 620 DAY),560000,'White'),
 (5,'TS09 UE 7890','Tata 407 LPT',4,1,2019,1,5,'Active',142500,90,DATE_SUB(CURDATE(),INTERVAL 2300 DAY),1380000,'Yellow'),
 (6,'TS10 UF 2345','Bajaj RE Compact',6,3,2022,3,6,'Active',18900,10,DATE_SUB(CURDATE(),INTERVAL 900 DAY),235000,'Yellow'),
 (7,'TS09 UG 6789','Toyota Innova Crysta',7,1,2021,1,0,'Idle',64300,55,DATE_SUB(CURDATE(),INTERVAL 1500 DAY),2150000,'Grey'),
 (8,'TS09 UH 1011','Eicher Pro 2049',5,1,2018,3,0,'Maintenance',198700,120,DATE_SUB(CURDATE(),INTERVAL 2700 DAY),1720000,'White');

INSERT INTO `documents` (`id`,`vehicle_id`,`type`,`no`,`vendor_id`,`issue`,`expiry`,`amount`) VALUES
 (1,1,'Insurance','POL-AC-110234',3,DATE_SUB(CURDATE(),INTERVAL 120 DAY),DATE_ADD(CURDATE(),INTERVAL 245 DAY),24800),
 (2,1,'PUC','PUC-1100231',5,DATE_SUB(CURDATE(),INTERVAL 160 DAY),DATE_ADD(CURDATE(),INTERVAL 20 DAY),90),
 (3,1,'Fitness','FIT-110045',5,DATE_SUB(CURDATE(),INTERVAL 700 DAY),DATE_ADD(CURDATE(),INTERVAL 360 DAY),1200),
 (4,2,'Insurance','POL-AC-220567',3,DATE_SUB(CURDATE(),INTERVAL 300 DAY),DATE_ADD(CURDATE(),INTERVAL 65 DAY),31200),
 (5,2,'RoadTax','TAX-2205',5,DATE_SUB(CURDATE(),INTERVAL 330 DAY),DATE_ADD(CURDATE(),INTERVAL 35 DAY),18500),
 (6,2,'PUC','PUC-2205',5,DATE_SUB(CURDATE(),INTERVAL 200 DAY),DATE_SUB(CURDATE(),INTERVAL 10 DAY),110),
 (7,3,'Insurance','POL-AC-330890',3,DATE_SUB(CURDATE(),INTERVAL 60 DAY),DATE_ADD(CURDATE(),INTERVAL 305 DAY),28600),
 (8,3,'Permit','PRM-3308',5,DATE_SUB(CURDATE(),INTERVAL 400 DAY),DATE_ADD(CURDATE(),INTERVAL 330 DAY),9500),
 (9,4,'Insurance','POL-AC-440246',3,DATE_SUB(CURDATE(),INTERVAL 90 DAY),DATE_ADD(CURDATE(),INTERVAL 275 DAY),17400),
 (10,4,'PUC','PUC-4402',5,DATE_SUB(CURDATE(),INTERVAL 170 DAY),DATE_ADD(CURDATE(),INTERVAL 8 DAY),90),
 (11,5,'Insurance','POL-AC-550135',3,DATE_SUB(CURDATE(),INTERVAL 340 DAY),DATE_ADD(CURDATE(),INTERVAL 25 DAY),46800),
 (12,5,'Fitness','FIT-550078',5,DATE_SUB(CURDATE(),INTERVAL 355 DAY),DATE_SUB(CURDATE(),INTERVAL 5 DAY),1600),
 (13,5,'Permit','PRM-5501',5,DATE_SUB(CURDATE(),INTERVAL 380 DAY),DATE_ADD(CURDATE(),INTERVAL 180 DAY),12500),
 (14,6,'Insurance','POL-AC-660975',3,DATE_SUB(CURDATE(),INTERVAL 150 DAY),DATE_ADD(CURDATE(),INTERVAL 215 DAY),8900),
 (15,6,'Permit','PRM-6609',5,DATE_SUB(CURDATE(),INTERVAL 220 DAY),DATE_ADD(CURDATE(),INTERVAL 140 DAY),4200),
 (16,7,'Insurance','POL-AC-770468',3,DATE_SUB(CURDATE(),INTERVAL 45 DAY),DATE_ADD(CURDATE(),INTERVAL 320 DAY),38500),
 (17,8,'Insurance','POL-AC-880753',3,DATE_SUB(CURDATE(),INTERVAL 280 DAY),DATE_ADD(CURDATE(),INTERVAL 85 DAY),42100),
 (18,8,'Fitness','FIT-880099',5,DATE_SUB(CURDATE(),INTERVAL 390 DAY),DATE_SUB(CURDATE(),INTERVAL 18 DAY),1800);

INSERT INTO `trips` (`id`,`no`,`vehicle_id`,`driver_id`,`date`,`from`,`to`,`odo_start`,`odo_end`,`purpose`,`revenue`) VALUES
 (1,'TRP-0001',1,1,DATE_SUB(CURDATE(),INTERVAL 26 DAY),'Patancheru','Medchal',41200,41268,'Delivery',1800),
 (2,'TRP-0002',1,1,DATE_SUB(CURDATE(),INTERVAL 18 DAY),'Patancheru','Sangareddy',41268,41330,'Delivery',1500),
 (3,'TRP-0003',1,1,DATE_SUB(CURDATE(),INTERVAL 6 DAY),'Patancheru','Zaheerabad',41330,41512,'Delivery',3600),
 (4,'TRP-0004',2,2,DATE_SUB(CURDATE(),INTERVAL 22 DAY),'Patancheru','Vijayawada',78400,78672,'Long haul',7200),
 (5,'TRP-0005',2,2,DATE_SUB(CURDATE(),INTERVAL 9 DAY),'Patancheru','Warangal',78672,78820,'Long haul',4100),
 (6,'TRP-0006',3,3,DATE_SUB(CURDATE(),INTERVAL 20 DAY),'Uppal','Karimnagar',33600,33765,'Distribution',3900),
 (7,'TRP-0007',3,3,DATE_SUB(CURDATE(),INTERVAL 4 DAY),'Uppal','Nizamabad',33765,33940,'Distribution',4300),
 (8,'TRP-0008',4,4,DATE_SUB(CURDATE(),INTERVAL 15 DAY),'Uppal','Secunderabad',21800,21862,'City courier',1200),
 (9,'TRP-0009',4,4,DATE_SUB(CURDATE(),INTERVAL 3 DAY),'Uppal','Kukatpally',21862,21908,'City courier',900),
 (10,'TRP-0010',5,5,DATE_SUB(CURDATE(),INTERVAL 19 DAY),'Patancheru','Nagpur',142500,143020,'Inter-state',18500),
 (11,'TRP-0011',5,5,DATE_SUB(CURDATE(),INTERVAL 7 DAY),'Patancheru','Vijayawada',143020,143296,'Inter-state',9800),
 (12,'TRP-0012',6,6,DATE_SUB(CURDATE(),INTERVAL 5 DAY),'Shamshabad','Mehdipatnam',18900,18988,'Last mile',1400);

INSERT INTO `fuel_logs` (`id`,`vehicle_id`,`driver_id`,`date`,`odo`,`qty`,`rate`,`vendor_id`,`full`) VALUES
 (1,1,1,DATE_SUB(CURDATE(),INTERVAL 25 DAY),41265,18,96.5,2,1),
 (2,1,1,DATE_SUB(CURDATE(),INTERVAL 7 DAY),41500,14,96.8,2,1),
 (3,2,2,DATE_SUB(CURDATE(),INTERVAL 23 DAY),78500,42,96.5,2,1),
 (4,2,2,DATE_SUB(CURDATE(),INTERVAL 10 DAY),78790,38,96.8,2,1),
 (5,3,3,DATE_SUB(CURDATE(),INTERVAL 21 DAY),33640,28,96.5,2,1),
 (6,3,3,DATE_SUB(CURDATE(),INTERVAL 5 DAY),33900,30,96.8,2,1),
 (7,4,4,DATE_SUB(CURDATE(),INTERVAL 16 DAY),21820,22,108.2,2,1),
 (8,4,4,DATE_SUB(CURDATE(),INTERVAL 4 DAY),21895,9,108.5,2,1),
 (9,5,5,DATE_SUB(CURDATE(),INTERVAL 20 DAY),142560,78,96.5,2,1),
 (10,5,5,DATE_SUB(CURDATE(),INTERVAL 8 DAY),143200,84,96.8,2,1),
 (11,6,6,DATE_SUB(CURDATE(),INTERVAL 6 DAY),18950,6,89.0,2,1),
 (12,1,1,DATE_SUB(CURDATE(),INTERVAL 17 DAY),41360,16,96.6,2,1);

INSERT INTO `services` (`id`,`no`,`vehicle_id`,`date`,`odo`,`type`,`vendor_id`,`parts`,`labor`,`desc`,`next_odo`,`next_date`,`status`) VALUES
 (1,'SVC-0001',1,DATE_SUB(CURDATE(),INTERVAL 40 DAY),40800,'Scheduled',1,2400,800,'10k service: oil, filter, brake check',46000,DATE_ADD(CURDATE(),INTERVAL 150 DAY),'Closed'),
 (2,'SVC-0002',2,DATE_SUB(CURDATE(),INTERVAL 60 DAY),77900,'Scheduled',1,3100,1100,'Periodic service + tyre rotation',83000,DATE_ADD(CURDATE(),INTERVAL 95 DAY),'Closed'),
 (3,'SVC-0003',3,DATE_SUB(CURDATE(),INTERVAL 12 DAY),33500,'Repair',1,5600,1800,'Clutch plate replacement',39000,DATE_ADD(CURDATE(),INTERVAL 170 DAY),'Closed'),
 (4,'SVC-0004',5,DATE_SUB(CURDATE(),INTERVAL 30 DAY),141900,'Scheduled',1,6800,2400,'Major service, injector clean',148000,DATE_ADD(CURDATE(),INTERVAL 60 DAY),'Closed'),
 (5,'SVC-0005',5,DATE_SUB(CURDATE(),INTERVAL 2 DAY),143280,'Breakdown',1,4200,1500,'Roadside: alternator failure',0,NULL,'Closed'),
 (6,'SVC-0006',8,DATE_SUB(CURDATE(),INTERVAL 1 DAY),198700,'Repair',1,18500,6500,'Gearbox overhaul (in progress)',0,NULL,'Open'),
 (7,'SVC-0007',4,DATE_SUB(CURDATE(),INTERVAL 70 DAY),20900,'Scheduled',1,1900,700,'First service',25000,DATE_ADD(CURDATE(),INTERVAL 110 DAY),'Closed'),
 (8,'SVC-0008',6,DATE_SUB(CURDATE(),INTERVAL 25 DAY),18600,'Scheduled',1,900,400,'CNG kit inspection + tune-up',22000,DATE_ADD(CURDATE(),INTERVAL 80 DAY),'Closed');

INSERT INTO `expenses` (`id`,`vehicle_id`,`date`,`category`,`amount`,`vendor_id`,`remarks`) VALUES
 (1,5,DATE_SUB(CURDATE(),INTERVAL 19 DAY),'Toll',1240,5,'NH-44 Nagpur run'),
 (2,5,DATE_SUB(CURDATE(),INTERVAL 7 DAY),'Toll',680,5,'Vijayawada highway'),
 (3,2,DATE_SUB(CURDATE(),INTERVAL 22 DAY),'Toll',520,5,'Vijayawada haul'),
 (4,1,DATE_SUB(CURDATE(),INTERVAL 14 DAY),'Fine',1000,5,'Overspeed challan'),
 (5,3,DATE_SUB(CURDATE(),INTERVAL 10 DAY),'Parking',150,0,'Karimnagar yard'),
 (6,4,DATE_SUB(CURDATE(),INTERVAL 8 DAY),'Fine',500,5,'No-parking challan'),
 (7,2,DATE_SUB(CURDATE(),INTERVAL 5 DAY),'Misc',300,0,'Cabin cleaning'),
 (8,5,DATE_SUB(CURDATE(),INTERVAL 2 DAY),'Toll',410,5,'Return leg');

-- Keep AUTO_INCREMENT well clear of seeded IDs (mirrors prototype's seq gaps).
ALTER TABLE `vehicles`      AUTO_INCREMENT = 51;
ALTER TABLE `drivers`       AUTO_INCREMENT = 51;
ALTER TABLE `vendors`       AUTO_INCREMENT = 51;
ALTER TABLE `depots`        AUTO_INCREMENT = 21;
ALTER TABLE `vehicle_types` AUTO_INCREMENT = 21;
ALTER TABLE `fuel_types`    AUTO_INCREMENT = 21;
ALTER TABLE `documents`     AUTO_INCREMENT = 201;
ALTER TABLE `trips`         AUTO_INCREMENT = 201;
ALTER TABLE `fuel_logs`     AUTO_INCREMENT = 201;
ALTER TABLE `services`      AUTO_INCREMENT = 201;
ALTER TABLE `expenses`      AUTO_INCREMENT = 201;