-- =============================================================
-- 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');