-- Create workshop_inquiries table for vehicle repair service inquiries
CREATE TABLE IF NOT EXISTS `workshop_inquiries` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `customerId` INT NULL COMMENT 'FK to users table (nullable for guest customers)',
  `customerName` VARCHAR(100) NOT NULL,
  `customerEmail` VARCHAR(100) NOT NULL,
  `customerContact` VARCHAR(20) NOT NULL,
  `customerWhatsApp` VARCHAR(20) NULL,
  `vehicleMake` VARCHAR(100) NOT NULL,
  `vehicleModel` VARCHAR(100) NOT NULL,
  `vehicleYear` INT NOT NULL,
  `vehicleMileage` INT NULL COMMENT 'Vehicle mileage in km',
  `chassisNumber` VARCHAR(100) NULL,
  `engineNumber` VARCHAR(100) NULL,
  `issueDescription` TEXT NOT NULL COMMENT 'What is wrong with the vehicle',
  `symptoms` TEXT NULL COMMENT 'Symptoms observed (e.g., strange noise, warning light)',
  `repairType` ENUM('engine', 'transmission', 'electrical', 'ac', 'body_work', 'general_service', 'other') NOT NULL DEFAULT 'other',
  `urgency` ENUM('normal', 'urgent', 'emergency') NOT NULL DEFAULT 'normal',
  `preferredDate` DATE NULL COMMENT 'Preferred service date',
  `preferredTime` TIME NULL COMMENT 'Preferred service time',
  `vehicleLocation` TEXT NULL COMMENT 'Where the vehicle is currently located',
  `vehiclePhoto` VARCHAR(500) NULL COMMENT 'Vehicle photo path',
  `issuePhotos` JSON NULL COMMENT 'Array of issue photo paths',
  `category` VARCHAR(100) NULL COMMENT 'Assigned by admin/manager',
  `vehicleModelCategory` VARCHAR(100) NULL COMMENT 'Optional secondary category for vehicle model',
  `status` ENUM('pending', 'categorized', 'sent_to_workshops', 'quotes_received', 'quote_accepted', 'in_progress', 'completed', 'cancelled') NOT NULL DEFAULT 'pending',
  `assignedWorkshopId` INT NULL COMMENT 'FK to workshops table',
  `profitMargin` DECIMAL(5, 2) NULL DEFAULT 10.00 COMMENT 'Commission margin percentage',
  `isUrgent` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'If true, customer receives notification for each quote individually',
  `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_customerId` (`customerId`),
  INDEX `idx_status` (`status`),
  INDEX `idx_assignedWorkshopId` (`assignedWorkshopId`),
  INDEX `idx_repairType` (`repairType`),
  CONSTRAINT `fk_workshop_inquiry_customer` FOREIGN KEY (`customerId`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_workshop_inquiry_workshop` FOREIGN KEY (`assignedWorkshopId`) REFERENCES `workshops` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;