-- =============================================================
-- Used Car Dealer Pro — database (Phase 1 + 2 + 3)
--
-- Import this file into phpMyAdmin (or via the mysql CLI):
--
--     mysql -u root < database/database.sql
--
-- Admin seed credentials
--     email:    admin@example.com
--     password: admin123
--
-- The password below is a password_hash() bcrypt hash generated by PHP,
-- so authentication always runs through password_verify().
--
-- Phase 3 adds the full inventory system:
--   brands, car_models, car_variants, vehicles, vehicle_images,
--   vehicle_features — plus Brand / Model / Variant / Vehicle CRUD.
-- =============================================================

CREATE DATABASE IF NOT EXISTS `used_car_pro`
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE `used_car_pro`;

-- -------------------------------------------------------------
-- users
-- -------------------------------------------------------------

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
    `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`       VARCHAR(100) NOT NULL,
    `email`      VARCHAR(190) NOT NULL,
    `mobile`     VARCHAR(20)  DEFAULT NULL,
    `password`   VARCHAR(255) NOT NULL,
    `role`       ENUM('admin', 'staff', 'customer') NOT NULL DEFAULT 'customer',
    `status`     TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1 = active, 0 = inactive',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `users` (`name`, `email`, `mobile`, `password`, `role`, `status`)
VALUES (
    'Administrator',
    'admin@example.com',
    '09171234567',
    '$2y$10$jHsEMnl5vhh0oARu09xPVea.xIH/vT3plfE3sf277LK9MtEZAy9YW',
    'admin',
    1
);

-- =============================================================
-- PHASE 3 — Vehicle inventory schema
-- =============================================================

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `vehicle_features`;
DROP TABLE IF EXISTS `vehicle_images`;
DROP TABLE IF EXISTS `vehicles`;
DROP TABLE IF EXISTS `car_variants`;
DROP TABLE IF EXISTS `car_models`;
DROP TABLE IF EXISTS `brands`;

-- -------------------------------------------------------------
-- brands
-- -------------------------------------------------------------

CREATE TABLE `brands` (
    `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`       VARCHAR(100) NOT NULL,
    `slug`       VARCHAR(120) NOT NULL,
    `logo`       VARCHAR(255) DEFAULT NULL,
    `status`     ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `brands_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------------
-- car_models
-- -------------------------------------------------------------

CREATE TABLE `car_models` (
    `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id`   INT UNSIGNED NOT NULL,
    `name`       VARCHAR(120) NOT NULL,
    `slug`       VARCHAR(140) NOT NULL,
    `status`     ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `car_models_slug_unique` (`slug`),
    KEY `car_models_brand_id_index` (`brand_id`),
    CONSTRAINT `car_models_brand_id_fk`
        FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------------
-- car_variants
-- -------------------------------------------------------------

CREATE TABLE `car_variants` (
    `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `model_id`   INT UNSIGNED NOT NULL,
    `name`       VARCHAR(140) NOT NULL,
    `status`     ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `car_variants_model_id_index` (`model_id`),
    CONSTRAINT `car_variants_model_id_fk`
        FOREIGN KEY (`model_id`) REFERENCES `car_models` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------------
-- vehicles
-- -------------------------------------------------------------

CREATE TABLE `vehicles` (
    `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `stock_no`         VARCHAR(50)  NOT NULL,
    `brand_id`         INT UNSIGNED NOT NULL,
    `model_id`         INT UNSIGNED NOT NULL,
    `variant_id`       INT UNSIGNED DEFAULT NULL,
    `registration_no`  VARCHAR(50)  DEFAULT NULL,
    `year`             SMALLINT UNSIGNED DEFAULT NULL,
    `registration_year` SMALLINT UNSIGNED DEFAULT NULL,
    `fuel_type`        ENUM('Petrol','Diesel','Hybrid','Electric') NOT NULL DEFAULT 'Petrol',
    `transmission`     ENUM('Automatic','Manual','CVT') NOT NULL DEFAULT 'Automatic',
    `body_type`        VARCHAR(60)  DEFAULT NULL,
    `color`            VARCHAR(40)  DEFAULT NULL,
    `km_driven`        INT UNSIGNED NOT NULL DEFAULT 0,
    `ownership`        ENUM('First Owner','Second Owner','Third Owner+','Company') DEFAULT NULL,
    `engine`           VARCHAR(80)  DEFAULT NULL,
    `mileage`          DECIMAL(6,2) NOT NULL DEFAULT 0 COMMENT 'Fuel economy estimate (km/L)',
    `purchase_price`   DECIMAL(12,2) NOT NULL DEFAULT 0,
    `expected_price`   DECIMAL(12,2) NOT NULL DEFAULT 0,
    `selling_price`    DECIMAL(12,2) NOT NULL DEFAULT 0,
    `status`           ENUM('Purchased','Inspection','Ready','Available','Reserved','Sold','Delivered') NOT NULL DEFAULT 'Purchased',
    `featured`         TINYINT(1) NOT NULL DEFAULT 0,
    `description`      TEXT,
    `meta_title`       VARCHAR(190) DEFAULT NULL,
    `meta_description` VARCHAR(255) DEFAULT NULL,
    `created_at`       TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`       TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `vehicles_stock_no_unique` (`stock_no`),
    KEY `vehicles_brand_id_index` (`brand_id`),
    KEY `vehicles_model_id_index` (`model_id`),
    KEY `vehicles_status_index` (`status`),
    CONSTRAINT `vehicles_brand_id_fk`
        FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `vehicles_model_id_fk`
        FOREIGN KEY (`model_id`) REFERENCES `car_models` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `vehicles_variant_id_fk`
        FOREIGN KEY (`variant_id`) REFERENCES `car_variants` (`id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------------
-- vehicle_images
-- -------------------------------------------------------------

CREATE TABLE `vehicle_images` (
    `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `vehicle_id` INT UNSIGNED NOT NULL,
    `image`      VARCHAR(255) NOT NULL,
    `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
    `is_primary` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `vehicle_images_vehicle_id_index` (`vehicle_id`),
    CONSTRAINT `vehicle_images_vehicle_id_fk`
        FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------------
-- vehicle_features
-- -------------------------------------------------------------

CREATE TABLE `vehicle_features` (
    `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `vehicle_id`   INT UNSIGNED NOT NULL,
    `feature_name` VARCHAR(190) NOT NULL,
    `created_at`   TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `vehicle_features_vehicle_id_index` (`vehicle_id`),
    CONSTRAINT `vehicle_features_vehicle_id_fk`
        FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------------
-- Phase 2 tables (customers, leads, test_drives, sales, purchases)
-- -------------------------------------------------------------

DROP TABLE IF EXISTS `sales`;
DROP TABLE IF EXISTS `purchases`;
DROP TABLE IF EXISTS `test_drives`;
DROP TABLE IF EXISTS `leads`;
DROP TABLE IF EXISTS `customers`;

CREATE TABLE `customers` (
    `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`       VARCHAR(190) NOT NULL,
    `email`      VARCHAR(190) DEFAULT NULL,
    `mobile`     VARCHAR(20)  DEFAULT NULL,
    `address`    VARCHAR(255) DEFAULT NULL,
    `status`     ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `customers_mobile_index` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `leads` (
    `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`        VARCHAR(190) NOT NULL,
    `email`       VARCHAR(190) DEFAULT NULL,
    `mobile`      VARCHAR(20)  DEFAULT NULL,
    `source`      ENUM('contact','sell-your-car','test-drive','walk-in','referral') NOT NULL DEFAULT 'contact',
    `message`     TEXT,
    `status`      ENUM('new','contacted','qualified','won','lost') NOT NULL DEFAULT 'new',
    `created_at`  TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`  TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `leads_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `test_drives` (
    `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `vehicle_id`     INT UNSIGNED DEFAULT NULL,
    `name`           VARCHAR(190) NOT NULL,
    `email`          VARCHAR(190) DEFAULT NULL,
    `mobile`         VARCHAR(20)  DEFAULT NULL,
    `preferred_date` DATE DEFAULT NULL,
    `preferred_time` TIME DEFAULT NULL,
    `notes`          TEXT,
    `status`         ENUM('pending','confirmed','completed','cancelled') NOT NULL DEFAULT 'pending',
    `created_at`     TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`     TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `test_drives_vehicle_id_index` (`vehicle_id`),
    CONSTRAINT `test_drives_vehicle_id_fk`
        FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sales` (
    `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `vehicle_id`     INT UNSIGNED DEFAULT NULL,
    `customer_id`    INT UNSIGNED DEFAULT NULL,
    `sale_price`     DECIMAL(12,2) NOT NULL DEFAULT 0,
    `payment_method` ENUM('cash','bank-transfer','installment','financing') NOT NULL DEFAULT 'cash',
    `sold_at`        DATE NOT NULL,
    `status`         ENUM('completed','pending','cancelled') NOT NULL DEFAULT 'completed',
    `created_at`     TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`     TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `sales_sold_at_index` (`sold_at`),
    KEY `sales_vehicle_id_index` (`vehicle_id`),
    KEY `sales_customer_id_index` (`customer_id`),
    CONSTRAINT `sales_vehicle_id_fk`
        FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `sales_customer_id_fk`
        FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `purchases` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `supplier_name` VARCHAR(190) DEFAULT NULL,
    `invoice_ref`   VARCHAR(100) DEFAULT NULL,
    `vehicle_id`    INT UNSIGNED DEFAULT NULL,
    `amount`        DECIMAL(12,2) NOT NULL DEFAULT 0,
    `purchase_date` DATE NOT NULL,
    `status`        ENUM('received','pending','returned') NOT NULL DEFAULT 'received',
    `created_at`    TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `purchases_purchase_date_index` (`purchase_date`),
    KEY `purchases_vehicle_id_index` (`vehicle_id`),
    CONSTRAINT `purchases_vehicle_id_fk`
        FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- =============================================================
-- Seed: brands
-- =============================================================

INSERT INTO `brands` (`id`, `name`, `slug`, `logo`, `status`) VALUES
    (1,  'Toyota',    'toyota',    'uploads/brands/toyota.svg',    'active'),
    (2,  'Honda',     'honda',     'uploads/brands/honda.svg',     'active'),
    (3,  'Mitsubishi','mitsubishi','uploads/brands/mitsubishi.svg','active'),
    (4,  'Nissan',    'nissan',    'uploads/brands/nissan.svg',    'active'),
    (5,  'Suzuki',    'suzuki',    'uploads/brands/suzuki.svg',    'active'),
    (6,  'Ford',      'ford',      'uploads/brands/ford.svg',      'active'),
    (7,  'Hyundai',   'hyundai',   'uploads/brands/hyundai.svg',   'active'),
    (8,  'Mazda',     'mazda',     'uploads/brands/mazda.svg',     'active'),
    (9,  'Kia',       'kia',       'uploads/brands/kia.svg',       'active'),
    (10, 'Chevrolet', 'chevrolet', 'uploads/brands/chevrolet.svg', 'inactive');

-- =============================================================
-- Seed: car_models
-- =============================================================

INSERT INTO `car_models` (`id`, `brand_id`, `name`, `slug`, `status`) VALUES
    (1,  1, 'Corolla Altis', 'corolla-altis', 'active'),
    (2,  1, 'Fortuner',      'fortuner',      'active'),
    (3,  1, 'Vios',          'vios',          'active'),
    (4,  2, 'Civic',         'civic',         'active'),
    (5,  2, 'HR-V',          'hr-v',          'active'),
    (6,  3, 'Montero Sport', 'montero-sport', 'active'),
    (7,  4, 'Navara',        'navara',        'active'),
    (8,  4, 'Almera',        'almera',        'active'),
    (9,  5, 'Swift',         'swift',         'active'),
    (10, 6, 'Ranger',        'ranger',        'active'),
    (11, 7, 'Tucson',        'tucson',        'active'),
    (12, 8, 'Mazda 3',       'mazda-3',       'active'),
    (13, 9, 'Seltos',        'seltos',        'active');

-- =============================================================
-- Seed: car_variants
-- =============================================================

INSERT INTO `car_variants` (`id`, `model_id`, `name`, `status`) VALUES
    (1,  1,  '1.6 G',            'active'),
    (2,  1,  'XLE',              'active'),
    (3,  2,  '2.8 V 4x4',        'active'),
    (4,  3,  '1.3 E',            'active'),
    (5,  4,  'RS 1.8',           'active'),
    (6,  5,  '1.8 S',            'active'),
    (7,  6,  '2.4 GLS',          'active'),
    (8,  7,  'VL 4x4',           'active'),
    (9,  8,  '1.0 VL',           'active'),
    (10, 9,  'GL',               'active'),
    (11, 10, 'Raptor 2.0 Bi-Turbo','active'),
    (12, 11, '2.0 GLS',          'active'),
    (13, 12, 'SkyActiv Hatchback','active'),
    (14, 13, 'EX',               'active');

-- =============================================================
-- Seed: vehicles
-- =============================================================

INSERT INTO `vehicles`
    (`id`, `stock_no`, `brand_id`, `model_id`, `variant_id`, `registration_no`, `year`,
     `registration_year`, `fuel_type`, `transmission`, `body_type`, `color`, `km_driven`,
     `ownership`, `engine`, `mileage`, `purchase_price`, `expected_price`, `selling_price`,
     `status`, `featured`, `description`, `meta_title`, `meta_description`)
VALUES
    (1, 'UCP-2026-001', 2, 4, 5, 'NBE A1 2341', 2021, 2021, 'Petrol', 'Automatic', 'Sedan', 'Gray', 32500,
     'First Owner', '1.8L DOHC i-VTEC', 12.5, 760000.00, 860000.00, 890000.00,
     'Available', 1,
     'Well-maintained Civic RS with full service history, leather seats, 18-inch alloys and a clean CAR plate. Selling because of relocation.',
     'Used Honda Civic RS 1.8 2021 for Sale', 'Certified pre-owned Honda Civic RS 2021, only 32,500 km, full service history.'),
    (2, 'UCP-2026-002', 1, 1, 1, 'NXE A2 4478', 2022, 2022, 'Petrol', 'CVT', 'Sedan', 'Silver', 22100,
     'First Owner', '1.6L Dual VVT-i', 14.1, 930000.00, 1030000.00, 1080000.00,
     'Available', 1,
     'First-owner Altis, garage kept, Toyota dealer maintained, keyless entry and reverse camera included.',
     'Used Toyota Corolla Altis 1.6 G 2022 for Sale', 'Certified pre-owned Corolla Altis 2022, one owner, dealer maintained.'),
    (3, 'UCP-2026-003', 3, 6, 7, 'MDA A3 8812', 2020, 2020, 'Diesel', 'Automatic', 'SUV', 'White', 48900,
     'Second Owner', '2.4L MIVEC Turbo', 9.8, 1090000.00, 1200000.00, 1250000.00,
     'Available', 1,
     '7-seater family SUV with 4x4 capability, roof rails and recently replaced tires. Perfect for family road trips.',
     'Used Mitsubishi Montero Sport 2.4 GLS 2020 for Sale', 'Certified pre-owned Montero Sport 2020, 4x4 7-seater SUV.'),
    (4, 'UCP-2026-004', 4, 7, 8, 'NVH A4 1904', 2019, 2019, 'Diesel', 'Automatic', 'Pickup', 'Black', 64200,
     'Second Owner', '2.5L YD25DDTi', 10.2, 840000.00, 950000.00, 998000.00,
     'Available', 0,
     'Dual-cab pickup with bedliner, hard tonneau cover and rear parking sensors. Overland-ready.',
     'Used Nissan Navara VL 4x4 2019 for Sale', 'Certified pre-owned Nissan Navara VL 4x4 2019 pickup truck.'),
    (5, 'UCP-2026-005', 5, 9, 10, 'SZW A5 5621', 2022, 2022, 'Petrol', 'Automatic', 'Hatchback', 'Red', 18900,
     'First Owner', '1.2L Dualjet', 18.5, 610000.00, 690000.00, 728000.00,
     'Available', 1,
     'Fuel-sipping city hatchback, one owner, still under factory warranty, all receipts available.',
     'Used Suzuki Swift GL 2022 for Sale', 'Certified pre-owned Suzuki Swift GL 2022, under factory warranty.'),
    (6, 'UCP-2026-006', 6, 10, 11, 'FOX A6 7320', 2021, 2021, 'Diesel', 'Automatic', 'Pickup', 'Orange', 37600,
     'First Owner', '2.0L Bi-Turbo EcoBlue', 11.4, 1720000.00, 1820000.00, 1890000.00,
     'Available', 0,
     'Aggressive 4x4 pickup with Fox shocks, KO2 tires and a Stage-1 ECU tune. Showroom condition.',
     'Used Ford Ranger Raptor 2.0 Bi-Turbo 2021 for Sale', 'Certified pre-owned Ford Ranger Raptor 2021, showroom condition.'),
    (7, 'UCP-2026-007', 7, 11, 12, 'HYU A7 6153', 2020, 2020, 'Petrol', 'Automatic', 'SUV', 'Blue', 41500,
     'Second Owner', '2.0L Theta II', 10.6, 830000.00, 920000.00, 950000.00,
     'Sold', 0,
     'Compact crossover traded in on a 2023 unit. Body is clean, interior fresh, runs smoothly.',
     'Used Hyundai Tucson 2.0 GLS 2020', 'Certified pre-owned Hyundai Tucson 2020, clean interior.'),
    (8, 'UCP-2026-008', 1, 2, 3, 'TOF A8 3922', 2019, 2019, 'Diesel', 'Automatic', 'SUV', 'White', 55800,
     'Second Owner', '2.8L 1GD-FTV', 9.4, 1540000.00, 1630000.00, 1690000.00,
     'Sold', 0,
     'Popular 7-seater diesel SUV. Recently serviced, new brakes and fresh undercoat.',
     'Used Toyota Fortuner 2.8 V 4x4 2019', 'Certified pre-owned Toyota Fortuner 2019 V 4x4.'),
    (9, 'UCP-2026-009', 8, 12, 13, 'MZA A9 8705', 2021, 2021, 'Petrol', 'Automatic', 'Hatchback', 'Soul Red', 28900,
     'First Owner', '2.0L SkyActiv-G', 14.8, 1030000.00, 1130000.00, 1200000.00,
     'Reserved', 1,
     'Premium-feel hatch with Bose sound, HUD and adaptive cruise. Reserved for a pre-sale inspection.',
     'Used Mazda 3 SkyActiv Hatchback 2021', 'Certified pre-owned Mazda 3 hatchback 2021, Bose sound.'),
    (10, 'UCP-2026-010', 9, 13, 14, 'KIA A10 2774', 2022, 2022, 'Petrol', 'CVT', 'SUV', 'Gravity Gray', 16700,
     'First Owner', '1.5L Smartstream', 14.2, 980000.00, 1080000.00, 1150000.00,
     'Reserved', 1,
     'Compact crossover with panoramic roof and wireless CarPlay. Holding reservation for a bank-approved buyer.',
     'Used Kia Seltos EX 2022', 'Certified pre-owned Kia Seltos EX 2022, panoramic roof.'),
    (11, 'UCP-2026-011', 1, 3, 4, 'TOV A11 9046', 2021, 2021, 'Petrol', 'Manual', 'Sedan', 'Metallic', 47500,
     'Third Owner+', '1.3L 1NR-FE', 16.3, 440000.00, 500000.00, 555000.00,
     'Purchased', 0,
     'Clean daily-driver Vios purchased at auction. Undergoing final checks before being listed.',
     'Used Toyota Vios 1.3 E 2021', 'Pre-purchase Toyota Vios 1.3 E 2021 inventory unit.'),
    (12, 'UCP-2026-012', 4, 8, 9, 'NIA A12 5551', 2022, 2022, 'Petrol', 'CVT', 'Sedan', 'White', 13900,
     'First Owner', '1.0L Turboflex', 17.1, 620000.00, 700000.00, 745000.00,
     'Inspection', 0,
     'Low-mileage Almera received on trade-in. Currently parked in the inspection bay.',
     'Used Nissan Almera 1.0 VL 2022', 'Incoming Nissan Almera 1.0 VL 2022 under inspection.'),
    (13, 'UCP-2026-013', 2, 5, 6, 'HOV A13 0913', 2020, 2020, 'Petrol', 'CVT', 'SUV', 'Carnelian Red', 33800,
     'Second Owner', '1.8L SOHC i-VTEC', 12.9, 715000.00, 790000.00, 835000.00,
     'Ready', 1,
     'Compact crossover back from the detailer. Ready to list once the final photos are uploaded.',
     'Used Honda HR-V 1.8 S 2020', 'Ready-to-list Honda HR-V 1.8 S 2020.'),
    (14, 'UCP-2026-014', 6, 10, 11, 'FOX A14 6682', 2018, 2018, 'Diesel', 'Manual', 'Pickup', 'Black', 91500,
     'Third Owner+', '2.2L Duratorq', 9.1, 730000.00, 810000.00, 855000.00,
     'Available', 0,
     'Workhorse Ranger XL with canopy and tow package. Solid four-wheel drive, ready for a new home.',
     'Used Ford Ranger XL 2018', 'Used Ford Ranger pickup truck 2018, 4x4, tow package.');

-- =============================================================
-- Seed: vehicle_images (primary first; extras keep sort order)
-- =============================================================

INSERT INTO `vehicle_images` (`vehicle_id`, `image`, `sort_order`, `is_primary`) VALUES
    (1,  'uploads/vehicles/vehicle-1-1.svg', 1, 1),
    (1,  'uploads/vehicles/vehicle-1-2.svg', 2, 0),
    (2,  'uploads/vehicles/vehicle-2-1.svg', 1, 1),
    (2,  'uploads/vehicles/vehicle-2-2.svg', 2, 0),
    (3,  'uploads/vehicles/vehicle-3-1.svg', 1, 1),
    (3,  'uploads/vehicles/vehicle-3-2.svg', 2, 0),
    (4,  'uploads/vehicles/vehicle-4-1.svg', 1, 1),
    (4,  'uploads/vehicles/vehicle-4-2.svg', 2, 0),
    (5,  'uploads/vehicles/vehicle-5-1.svg', 1, 1),
    (5,  'uploads/vehicles/vehicle-5-2.svg', 2, 0),
    (6,  'uploads/vehicles/vehicle-6-1.svg', 1, 1),
    (6,  'uploads/vehicles/vehicle-6-2.svg', 2, 0),
    (7,  'uploads/vehicles/vehicle-7-1.svg', 1, 1),
    (8,  'uploads/vehicles/vehicle-8-1.svg', 1, 1),
    (9,  'uploads/vehicles/vehicle-9-1.svg', 1, 1),
    (9,  'uploads/vehicles/vehicle-9-2.svg', 2, 0),
    (10, 'uploads/vehicles/vehicle-10-1.svg', 1, 1),
    (11, 'uploads/vehicles/vehicle-11-1.svg', 1, 1),
    (12, 'uploads/vehicles/vehicle-12-1.svg', 1, 1),
    (13, 'uploads/vehicles/vehicle-13-1.svg', 1, 1),
    (14, 'uploads/vehicles/vehicle-14-1.svg', 1, 1);

-- =============================================================
-- Seed: vehicle_features
-- =============================================================

INSERT INTO `vehicle_features` (`vehicle_id`, `feature_name`) VALUES
    (1, 'Leather seats'),
    (1, '18-inch alloy wheels'),
    (1, 'Reverse camera'),
    (1, 'Keyless entry'),
    (2, 'Keyless entry'),
    (2, 'Reverse camera'),
    (2, 'Toyota dealer maintained'),
    (2, 'Garage kept'),
    (3, '4x4 capability'),
    (3, 'Roof rails'),
    (3, '7-seater seating'),
    (3, 'New tires'),
    (4, 'Bedliner'),
    (4, 'Hard tonneau cover'),
    (4, 'Rear parking sensors'),
    (5, 'Under factory warranty'),
    (5, 'One owner'),
    (5, 'Complete receipts'),
    (6, 'Fox shocks'),
    (6, 'KO2 off-road tires'),
    (6, 'Stage-1 ECU tune'),
    (7, 'Clean body'),
    (7, 'Fresh interior'),
    (8, 'New brakes'),
    (8, 'Fresh undercoat'),
    (8, 'Recently serviced'),
    (9, 'Bose premium sound'),
    (9, 'Head-up display'),
    (9, 'Adaptive cruise control'),
    (10, 'Panoramic roof'),
    (10, 'Wireless Apple CarPlay'),
    (11, 'Single front owner'),
    (11, 'Full service records'),
    (12, 'Low mileage'),
    (12, 'One owner'),
    (13, 'Reverse camera'),
    (13, 'Push-button start'),
    (14, 'Tow package'),
    (14, 'Canopy'),
    (14, 'Rack bedliner');

-- =============================================================
-- Seed: customers
-- =============================================================

INSERT INTO `customers` (`id`, `name`, `email`, `mobile`, `address`, `status`, `created_at`) VALUES
    (1, 'Maria Santos',    'maria.santos@example.com',   '09181234567', 'Quezon City', 'active',     DATE_SUB(NOW(), INTERVAL 90 DAY)),
    (2, 'Juan Dela Cruz',  'juandc@example.com',         '09173214567', 'Mandaluyong', 'active',    DATE_SUB(NOW(), INTERVAL 80 DAY)),
    (3, 'Carla Mendoza',   'carla.mendoza@example.com',  '09175678901', 'Makati',      'active',     DATE_SUB(NOW(), INTERVAL 64 DAY)),
    (4, 'Paolo Reyes',     'paolo.reyes@example.com',    '09179876543', 'Taguig',      'active',     DATE_SUB(NOW(), INTERVAL 41 DAY)),
    (5, 'Angelica Cruz',   'angelica.cruz@example.com',  '09172123345', 'Pasig',       'active',     DATE_SUB(NOW(), INTERVAL 33 DAY)),
    (6, 'Mark Villanueva', 'mark.v@example.com',         '09172345678', 'San Juan',    'inactive',   DATE_SUB(NOW(), INTERVAL 20 DAY));

-- =============================================================
-- Seed: leads
-- =============================================================

INSERT INTO `leads` (`name`, `email`, `mobile`, `source`, `message`, `status`, `created_at`) VALUES
    ('Liza Reyes',     'liza.reyes@example.com',     '09351234567', 'contact',      'Interested in a Civic unit for a replacement. Any OTR pricing with LTO?',            'new',       DATE_SUB(NOW(), INTERVAL 2 HOUR)),
    ('Ben Ocampo',     'ben.ocampo@example.com',     '09179887766', 'test-drive',   'Requested a test drive for the Montero Sport this weekend.',                          'new',       DATE_SUB(NOW(), INTERVAL 5 HOUR)),
    ('Sandra Lim',     'sandra.lim@example.com',     '09281233445', 'sell-your-car', 'Selling a 2018 Toyota Vios, 45k mileage, open to trade-in.',                            'new',       DATE_SUB(NOW(), INTERVAL 1 DAY)),
    ('Diego Ramos',    'diego.ramos@example.com',    '09174567890', 'walk-in',      'Walked in, looked at the Kia Seltos, wants a payment plan comparison.',                 'contacted', DATE_SUB(NOW(), INTERVAL 2 DAY)),
    ('Jenny Tan',      'jenny.tan@example.com',      '09175678932', 'referral',     'Referred by a previous buyer; wants the Navara in white if available.',                 'contacted', DATE_SUB(NOW(), INTERVAL 4 DAY)),
    ('Roland Diaz',    'roland.diaz@example.com',    '09172345691', 'contact',      'Company fleet manager, needs 3 units, asking for corporate discount.',                   'qualified', DATE_SUB(NOW(), INTERVAL 7 DAY)),
    ('Glenn Mercado',  'glenn.m@example.com',        '09181234589', 'walk-in',      'Closed the deal on the Fortuner, financing approved by bank.',                            'won',       DATE_SUB(NOW(), INTERVAL 4 DAY)),
    ('Aiko Rosales',   'aiko.rosales@example.com',   '09274658021', 'contact',      'Budget is too low for current inventory, will call back next quarter during clearance.',  'lost',      DATE_SUB(NOW(), INTERVAL 12 DAY));

-- =============================================================
-- Seed: test drives
-- =============================================================

INSERT INTO `test_drives` (`vehicle_id`, `name`, `email`, `mobile`, `preferred_date`, `preferred_time`, `notes`, `status`, `created_at`) VALUES
    (3, 'Ben Ocampo',    'ben.ocampo@example.com',   '09179887766', DATE_ADD(CURDATE(), INTERVAL 2 DAY), '14:00:00', 'Bringing family for a second look.',   'pending',   DATE_SUB(NOW(), INTERVAL 5 HOUR)),
    (4, 'Carla Mendoza', 'carla.mendoza@example.com','09175678901', DATE_ADD(CURDATE(), INTERVAL 1 DAY), '10:30:00', 'Wants highway test.',                   'pending',   DATE_SUB(NOW(), INTERVAL 1 DAY)),
    (1, 'Paolo Reyes',   'paolo.reyes@example.com',  '09179876543', CURDATE(),                          '16:00:00', 'After work slot.',                       'confirmed', DATE_SUB(NOW(), INTERVAL 2 DAY)),
    (5, 'Angelica Cruz', 'angelica.cruz@example.com','09172123345', DATE_SUB(CURDATE(), INTERVAL 1 DAY), '11:00:00', NULL,                                    'completed', DATE_SUB(NOW(), INTERVAL 3 DAY)),
    (9, 'Diego Ramos',   'diego.ramos@example.com',  '09174567890', DATE_SUB(CURDATE(), INTERVAL 2 DAY), '15:30:00', NULL,                                    'cancelled', DATE_SUB(NOW(), INTERVAL 4 DAY)),
    (2, 'Glenn Mercado', 'glenn.m@example.com',      '09181234589', DATE_SUB(CURDATE(), INTERVAL 40 DAY),'09:00:00', 'Test drove the Altis before purchase.',  'completed', DATE_SUB(NOW(), INTERVAL 40 DAY));

-- =============================================================
-- Seed: sales (includes current-month rows for the dashboard)
-- =============================================================

INSERT INTO `sales` (`vehicle_id`, `customer_id`, `sale_price`, `payment_method`, `sold_at`, `status`, `created_at`) VALUES
    (8, 5, 1690000.00, 'bank-transfer', DATE_SUB(CURDATE(), INTERVAL 5 DAY),  'completed', DATE_SUB(NOW(), INTERVAL 5 DAY)),
    (7, 4, 950000.00,  'cash',          DATE_SUB(CURDATE(), INTERVAL 9 DAY),  'completed', DATE_SUB(NOW(), INTERVAL 9 DAY)),
    (3, 3, 1250000.00, 'installment',   DATE_SUB(CURDATE(), INTERVAL 1 MONTH), 'completed', DATE_SUB(NOW(), INTERVAL 32 DAY)),
    (5, 2, 728000.00,  'financing',     DATE_SUB(CURDATE(), INTERVAL 1 MONTH), 'completed', DATE_SUB(NOW(), INTERVAL 36 DAY)),
    (1, 1, 890000.00,  'cash',          DATE_SUB(CURDATE(), INTERVAL 2 MONTH), 'completed', DATE_SUB(NOW(), INTERVAL 62 DAY)),
    (4, 3, 998000.00,  'bank-transfer', DATE_SUB(CURDATE(), INTERVAL 3 MONTH), 'completed', DATE_SUB(NOW(), INTERVAL 90 DAY)),
    (6, 2, 1890000.00, 'installment',   DATE_SUB(CURDATE(), INTERVAL 4 MONTH), 'completed', DATE_SUB(NOW(), INTERVAL 120 DAY)),
    (2, 5, 1080000.00, 'cash',          DATE_SUB(CURDATE(), INTERVAL 5 MONTH), 'completed', DATE_SUB(NOW(), INTERVAL 150 DAY));

-- =============================================================
-- Seed: purchases
-- =============================================================

INSERT INTO `purchases` (`supplier_name`, `invoice_ref`, `vehicle_id`, `amount`, `purchase_date`, `status`, `created_at`) VALUES
    ('Auto Auction Manila',  'INV-2026-081', 9, 1020000.00, DATE_SUB(CURDATE(), INTERVAL 6 DAY),  'received', DATE_SUB(NOW(), INTERVAL 6 DAY)),
    ('Toyota Cebu',          'INV-2026-079', 10, 960000.00, DATE_SUB(CURDATE(), INTERVAL 3 DAY),  'received', DATE_SUB(NOW(), INTERVAL 3 DAY)),
    ('Dealer Direct',        'INV-2026-062', NULL, 640000.00, DATE_SUB(CURDATE(), INTERVAL 1 MONTH), 'received', DATE_SUB(NOW(), INTERVAL 30 DAY)),
    ('Auto Auction Manila',  'INV-2026-045', NULL, 520000.00, DATE_SUB(CURDATE(), INTERVAL 2 MONTH), 'received', DATE_SUB(NOW(), INTERVAL 58 DAY)),
    ('Stocklane Motors',     'INV-2026-030', NULL, 720000.00, DATE_SUB(CURDATE(), INTERVAL 3 MONTH), 'received', DATE_SUB(NOW(), INTERVAL 95 DAY)),
    ('Provincial Trade-Ins', 'INV-2026-018', NULL, 680000.00, DATE_SUB(CURDATE(), INTERVAL 4 MONTH), 'received', DATE_SUB(NOW(), INTERVAL 122 DAY)),
    ('Auctioneer PH',        'INV-2026-004', NULL, 880000.00, DATE_SUB(CURDATE(), INTERVAL 5 MONTH), 'received', DATE_SUB(NOW(), INTERVAL 149 DAY));-- =============================================================
-- Phase 4 -- vehicle_purchases (vehicle acquisition from sellers)
-- =============================================================

DROP TABLE IF EXISTS `vehicle_inspection_items`;
DROP TABLE IF EXISTS `vehicle_inspections`;
DROP TABLE IF EXISTS `vehicle_expenses`;
DROP TABLE IF EXISTS `expense_categories`;
DROP TABLE IF EXISTS `vehicle_purchases`;

CREATE TABLE `vehicle_purchases` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `purchase_no` VARCHAR(50) NOT NULL,
    `vehicle_id` INT UNSIGNED NOT NULL,
    `seller_name` VARCHAR(190) NOT NULL,
    `seller_mobile` VARCHAR(20) NOT NULL,
    `seller_address` VARCHAR(255) DEFAULT NULL,
    `purchase_date` DATE NOT NULL,
    `purchase_price` DECIMAL(12,2) NOT NULL DEFAULT 0,
    `advance_amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
    `remaining_amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
    `payment_method` ENUM('cash','bank-transfer','check','installment','other') NOT NULL DEFAULT 'cash',
    `remarks` TEXT,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `vehicle_purchases_purchase_no_index` (`purchase_no`),
    KEY `vehicle_purchases_vehicle_id_index` (`vehicle_id`),
    KEY `vehicle_purchases_purchase_date_index` (`purchase_date`),
    CONSTRAINT `vehicle_purchases_vehicle_id_fk` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `vehicle_inspections` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `vehicle_id` INT UNSIGNED NOT NULL,
    `inspection_no` VARCHAR(50) NOT NULL,
    `inspector_name` VARCHAR(190) DEFAULT NULL,
    `inspection_date` DATE NOT NULL,
    `overall_condition` ENUM('Excellent','Good','Average','Poor','Needs Repair') DEFAULT NULL,
    `notes` TEXT,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `vehicle_inspections_vehicle_id_index` (`vehicle_id`),
    KEY `vehicle_inspections_inspection_no_index` (`inspection_no`),
    CONSTRAINT `vehicle_inspections_vehicle_id_fk` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `vehicle_inspection_items` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `inspection_id` INT UNSIGNED NOT NULL,
    `category` VARCHAR(50) NOT NULL,
    `condition` ENUM('Excellent','Good','Average','Poor','Needs Repair') NOT NULL,
    `notes` TEXT,
    `images` TEXT,
    `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `vehicle_inspection_items_inspection_id_index` (`inspection_id`),
    CONSTRAINT `vehicle_inspection_items_inspection_id_fk` FOREIGN KEY (`inspection_id`) REFERENCES `vehicle_inspections` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `expense_categories` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(120) NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `expense_categories_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `vehicle_expenses` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `vehicle_id` INT UNSIGNED NOT NULL,
    `expense_category_id` INT UNSIGNED NOT NULL,
    `amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
    `expense_date` DATE NOT NULL,
    `description` TEXT,
    `receipt` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `vehicle_expenses_vehicle_id_index` (`vehicle_id`),
    KEY `vehicle_expenses_category_index` (`expense_category_id`),
    KEY `vehicle_expenses_expense_date_index` (`expense_date`),
    CONSTRAINT `vehicle_expenses_vehicle_id_fk` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `vehicle_expenses_category_fk` FOREIGN KEY (`expense_category_id`) REFERENCES `expense_categories` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

INSERT INTO `expense_categories` (`id`, `name`, `slug`) VALUES
    (1, 'Repair', 'repair'),
    (2, 'Service', 'service'),
    (3, 'Insurance', 'insurance'),
    (4, 'RTO', 'rto'),
    (5, 'Cleaning', 'cleaning'),
    (6, 'Accessories', 'accessories'),
    (7, 'Transport', 'transport'),
    (8, 'Documentation', 'documentation'),
    (9, 'Other', 'other');