-- ============================================================
-- Phase 5 — CRM schema (customers, leads, test_drives)
-- Executed via mysqli multi_query by install_phase5.php
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;

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

CREATE TABLE `customers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(190) NOT NULL,
  `mobile` varchar(20) DEFAULT NULL,
  `email` varchar(190) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `pincode` varchar(20) DEFAULT NULL,
  `source` varchar(50) DEFAULT NULL,
  `remarks` text DEFAULT NULL,
  `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`),
  KEY `customers_email_index` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `leads` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) unsigned DEFAULT NULL,
  `vehicle_id` int(10) unsigned DEFAULT NULL,
  `lead_source` enum('Website','WhatsApp','Facebook','Instagram','Google','Walk-in','Phone','Referral','Other') NOT NULL DEFAULT 'Website',
  `budget` decimal(12,2) DEFAULT NULL,
  `status` enum('New','Contacted','Interested','Test Drive','Negotiation','Booked','Sold','Lost') NOT NULL DEFAULT 'New',
  `assigned_to` int(10) unsigned DEFAULT NULL,
  `followup_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `leads_customer_id_index` (`customer_id`),
  KEY `leads_vehicle_id_index` (`vehicle_id`),
  KEY `leads_status_index` (`status`),
  KEY `leads_followup_date_index` (`followup_date`),
  CONSTRAINT `leads_customer_id_fk` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `leads_vehicle_id_fk` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `leads_assigned_to_fk` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `test_drives` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) unsigned DEFAULT NULL,
  `vehicle_id` int(10) unsigned DEFAULT NULL,
  `date` date DEFAULT NULL,
  `time` time DEFAULT NULL,
  `salesperson_id` int(10) unsigned DEFAULT NULL,
  `license_no` varchar(50) DEFAULT NULL,
  `status` enum('Requested','Confirmed','Completed','Cancelled','No Show') NOT NULL DEFAULT 'Requested',
  `feedback` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `test_drives_customer_id_index` (`customer_id`),
  KEY `test_drives_vehicle_id_index` (`vehicle_id`),
  KEY `test_drives_date_index` (`date`),
  CONSTRAINT `test_drives_customer_id_fk` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `test_drives_vehicle_id_fk` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `test_drives_salesperson_id_fk` FOREIGN KEY (`salesperson_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------------
-- Seed sample CRM data (Phase 5 schema). Guarded so the file can be
-- re-applied on an existing database without duplicating rows.
-- -------------------------------------------------------------
INSERT INTO `customers` (`name`, `mobile`, `email`, `address`, `city`, `state`, `pincode`, `source`, `remarks`)
SELECT * FROM (SELECT 'Maria Santos' AS name, '09181234567' AS mobile, 'maria.santos@example.com' AS email, '12 Luna St' AS address, 'Quezon City' AS city, 'Metro Manila' AS state, '1100' AS pincode, 'Website' AS source, 'Looking for a compact sedan.' AS remarks) AS s
WHERE NOT EXISTS (SELECT 1 FROM `customers` WHERE `email` = 'maria.santos@example.com');

INSERT INTO `customers` (`name`, `mobile`, `email`, `address`, `city`, `state`, `pincode`, `source`, `remarks`)
SELECT * FROM (SELECT 'Juan Dela Cruz' AS name, '09173214567' AS mobile, 'juandc@example.com' AS email, '45 Mabini Ave' AS address, 'Mandaluyong' AS city, 'Metro Manila' AS state, '1550' AS pincode, 'Walk-in' AS source, 'Interested in an SUV.' AS remarks) AS s
WHERE NOT EXISTS (SELECT 1 FROM `customers` WHERE `email` = 'juandc@example.com');

INSERT INTO `customers` (`name`, `mobile`, `email`, `address`, `city`, `state`, `pincode`, `source`, `remarks`)
SELECT * FROM (SELECT 'Carla Mendoza' AS name, '09175678901' AS mobile, 'carla.mendoza@example.com' AS email, '8 Bonifacio St' AS address, 'Makati' AS city, 'Metro Manila' AS state, '1200' AS pincode, 'Referral' AS source, 'Returning customer, prefers installment.' AS remarks) AS s
WHERE NOT EXISTS (SELECT 1 FROM `customers` WHERE `email` = 'carla.mendoza@example.com');

INSERT INTO `customers` (`name`, `mobile`, `email`, `address`, `city`, `state`, `pincode`, `source`, `remarks`)
SELECT * FROM (SELECT 'Paolo Reyes' AS name, '09179876543' AS mobile, 'paolo.reyes@example.com' AS email, '22 Gen Luna Rd' AS address, 'Taguig' AS city, 'Metro Manila' AS state, '1630' AS pincode, 'Facebook' AS source, 'Wants to trade in his pickup.' AS remarks) AS s
WHERE NOT EXISTS (SELECT 1 FROM `customers` WHERE `email` = 'paolo.reyes@example.com');

INSERT INTO `customers` (`name`, `mobile`, `email`, `address`, `city`, `state`, `pincode`, `source`, `remarks`)
SELECT * FROM (SELECT 'Angelica Cruz' AS name, '09172123345' AS mobile, 'angelica.cruz@example.com' AS email, '9 Sampaguita St' AS address, 'Pasig' AS city, 'Metro Manila' AS state, '1600' AS pincode, 'Website' AS source, 'First-time buyer.' AS remarks) AS s
WHERE NOT EXISTS (SELECT 1 FROM `customers` WHERE `email` = 'angelica.cruz@example.com');

INSERT INTO `customers` (`name`, `mobile`, `email`, `address`, `city`, `state`, `pincode`, `source`, `remarks`)
SELECT * FROM (SELECT 'Mark Villanueva' AS name, '09172345678' AS mobile, 'mark.v@example.com' AS email, '101 Retail Row' AS address, 'San Juan' AS city, 'Metro Manila' AS state, '1500' AS pincode, 'Instagram' AS source, 'Likes the Kia Seltos.' AS remarks) AS s
WHERE NOT EXISTS (SELECT 1 FROM `customers` WHERE `email` = 'mark.v@example.com');

SET FOREIGN_KEY_CHECKS = 1;