-- =============================================================
-- Phase 7 — Sales Management
--
-- Tables added:
--   vehicle_sales        (sales pipeline + payments + finance)
--   vehicle_bookings     (reservation / booking holds)
--   payments             (money received against a sale)
--   finance_applications (bank/company financing on a sale)
--   vehicle_documents    (RC, insurance, agreements, etc.)
--
-- Run through install_phase7.php (idempotent) - do NOT apply raw via
-- phpMyAdmin, the installer matches the existing used-car-pro tables.
-- =============================================================

CREATE TABLE IF NOT EXISTS `vehicle_sales` (
    `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `sale_no`         VARCHAR(30)  NOT NULL,
    `vehicle_id`      INT UNSIGNED DEFAULT NULL,
    `customer_id`     INT UNSIGNED DEFAULT NULL,
    `salesperson_id`  INT UNSIGNED DEFAULT NULL,
    `sale_date`       DATE DEFAULT NULL,
    `sale_price`      DECIMAL(12,2) NOT NULL DEFAULT 0,
    `discount`        DECIMAL(12,2) NOT NULL DEFAULT 0,
    `exchange_value`  DECIMAL(12,2) NOT NULL DEFAULT 0,
    `net_amount`      DECIMAL(12,2) NOT NULL DEFAULT 0,
    `finance_amount`  DECIMAL(12,2) NOT NULL DEFAULT 0,
    `down_payment`    DECIMAL(12,2) NOT NULL DEFAULT 0,
    `payment_status`  ENUM('Pending','Partial','Paid') NOT NULL DEFAULT 'Pending',
    `status`          ENUM('Booking','Payment','Finance','Ready For Delivery','Delivered','Sold') NOT NULL DEFAULT 'Booking',
    `delivery_date`   DATE DEFAULT NULL,
    `remarks`         TEXT,
    `created_at`      DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_vehicle_sales_sale_no` (`sale_no`),
    KEY `idx_vehicle_sales_vehicle_id` (`vehicle_id`),
    KEY `idx_vehicle_sales_customer_id` (`customer_id`),
    KEY `idx_vehicle_sales_salesperson_id` (`salesperson_id`),
    KEY `idx_vehicle_sales_status` (`status`),
    KEY `idx_vehicle_sales_sale_date` (`sale_date`),
    CONSTRAINT `fk_vehicle_sales_vehicle_id` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_vehicle_sales_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_vehicle_sales_salesperson_id` FOREIGN KEY (`salesperson_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `vehicle_bookings` (
    `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `booking_no`     VARCHAR(30)  NOT NULL,
    `vehicle_id`     INT UNSIGNED DEFAULT NULL,
    `customer_id`    INT UNSIGNED DEFAULT NULL,
    `booking_amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
    `booking_date`   DATE DEFAULT NULL,
    `status`         ENUM('Pending','Confirmed','Cancelled','Converted') NOT NULL DEFAULT 'Pending',
    `remarks`        TEXT,
    `created_at`     DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_vehicle_bookings_booking_no` (`booking_no`),
    KEY `idx_vehicle_bookings_vehicle_id` (`vehicle_id`),
    KEY `idx_vehicle_bookings_customer_id` (`customer_id`),
    KEY `idx_vehicle_bookings_status` (`status`),
    CONSTRAINT `fk_vehicle_bookings_vehicle_id` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_vehicle_bookings_customer_id` 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 IF NOT EXISTS `payments` (
    `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `customer_id`     INT UNSIGNED DEFAULT NULL,
    `vehicle_id`      INT UNSIGNED DEFAULT NULL,
    `sale_id`         INT UNSIGNED DEFAULT NULL,
    `amount`          DECIMAL(12,2) NOT NULL DEFAULT 0,
    `payment_method`  ENUM('Cash','UPI','Bank Transfer','Cheque','Finance','Other') NOT NULL DEFAULT 'Cash',
    `transaction_no`  VARCHAR(60) DEFAULT NULL,
    `payment_date`    DATE DEFAULT NULL,
    `remarks`         TEXT,
    `created_at`      DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_payments_customer_id` (`customer_id`),
    KEY `idx_payments_vehicle_id` (`vehicle_id`),
    KEY `idx_payments_sale_id` (`sale_id`),
    KEY `idx_payments_payment_date` (`payment_date`),
    CONSTRAINT `fk_payments_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_payments_vehicle_id` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_payments_sale_id` FOREIGN KEY (`sale_id`) REFERENCES `vehicle_sales` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `finance_applications` (
    `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ref_no`           VARCHAR(30)  NOT NULL,
    `customer_id`      INT UNSIGNED DEFAULT NULL,
    `vehicle_id`       INT UNSIGNED DEFAULT NULL,
    `sale_id`          INT UNSIGNED DEFAULT NULL,
    `finance_company`  VARCHAR(190) DEFAULT NULL,
    `loan_amount`      DECIMAL(12,2) NOT NULL DEFAULT 0,
    `interest_rate`    DECIMAL(6,3) NOT NULL DEFAULT 0,
    `tenure`           INT UNSIGNED NOT NULL DEFAULT 0,
    `emi`              DECIMAL(12,2) NOT NULL DEFAULT 0,
    `processing_fee`   DECIMAL(12,2) NOT NULL DEFAULT 0,
    `status`           ENUM('Applied','Documents Pending','Approved','Rejected','Disbursed') NOT NULL DEFAULT 'Applied',
    `remarks`          TEXT,
    `created_at`       DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at`       DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_finance_applications_ref_no` (`ref_no`),
    KEY `idx_finance_applications_customer_id` (`customer_id`),
    KEY `idx_finance_applications_vehicle_id` (`vehicle_id`),
    KEY `idx_finance_applications_sale_id` (`sale_id`),
    KEY `idx_finance_applications_status` (`status`),
    CONSTRAINT `fk_finance_applications_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_finance_applications_vehicle_id` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_finance_applications_sale_id` FOREIGN KEY (`sale_id`) REFERENCES `vehicle_sales` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `vehicle_documents` (
    `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ref_no`         VARCHAR(30)  NOT NULL,
    `vehicle_id`     INT UNSIGNED DEFAULT NULL,
    `customer_id`    INT UNSIGNED DEFAULT NULL,
    `document_type`  ENUM('RC','Insurance','PUC','NOC','Sale Agreement','Purchase Agreement','ID Proof','Address Proof','Finance Documents','Delivery Documents') NOT NULL DEFAULT 'RC',
    `document_file`  VARCHAR(255) DEFAULT NULL,
    `status`         ENUM('Pending','Verified','Rejected','Expired') NOT NULL DEFAULT 'Pending',
    `expiry_date`    DATE DEFAULT NULL,
    `remarks`        TEXT,
    `created_at`     DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_vehicle_documents_ref_no` (`ref_no`),
    KEY `idx_vehicle_documents_vehicle_id` (`vehicle_id`),
    KEY `idx_vehicle_documents_customer_id` (`customer_id`),
    KEY `idx_vehicle_documents_status` (`status`),
    CONSTRAINT `fk_vehicle_documents_vehicle_id` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_vehicle_documents_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;