-- ============================================================================
-- PRODUCTION MIGRATION: Create Workshops and Workshop Inquiries Tables
-- ============================================================================
-- This script creates both the 'workshops' and 'workshop_inquiries' tables
-- for your production database.
--
-- IMPORTANT: Run this script in your production database using one of:
--   1. MySQL Workbench / phpMyAdmin
--   2. Command line: mysql -u username -p database_name < this_file.sql
--   3. Node.js script (see CREATE_WORKSHOPS_AND_INQUIRIES_PRODUCTION.js)
--
-- ============================================================================

-- Step 1: Update User role enum to include 'workshop' (if not already present)
-- Note: This will fail gracefully if 'workshop' is already in the enum
SET @enum_exists = (
  SELECT COUNT(*) 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'users' 
    AND COLUMN_NAME = 'role'
    AND COLUMN_TYPE LIKE '%workshop%'
);

SET @sql = IF(@enum_exists = 0,
  'ALTER TABLE `users` MODIFY COLUMN `role` ENUM(''customer'', ''admin'', ''manager'', ''supplier'', ''workshop'') NOT NULL DEFAULT ''customer'';',
  'SELECT ''Role enum already includes workshop'' AS message;'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Step 2: Create workshops table
CREATE TABLE IF NOT EXISTS `workshops` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `userId` INT NOT NULL,
  `workshopName` VARCHAR(200) NOT NULL,
  `workshopAddress` TEXT NOT NULL,
  `contactNumber` VARCHAR(20) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `services` JSON NULL COMMENT 'Array of services this workshop offers (e.g., engine, transmission, etc.)',
  `vehicleTypes` JSON NULL COMMENT 'Array of vehicle types this workshop handles (e.g., car, motorcycle, truck, etc.)',
  `locationLat` DECIMAL(10, 8) NULL,
  `locationLng` DECIMAL(11, 8) NULL,
  `mapLink` VARCHAR(500) NULL,
  `isActive` TINYINT(1) NOT NULL DEFAULT 1,
  `bankName` VARCHAR(200) NULL COMMENT 'Bank name for payment processing',
  `accountHolderName` VARCHAR(200) NULL COMMENT 'Account holder name as it appears on bank account',
  `accountNumber` VARCHAR(50) NULL COMMENT 'Bank account number',
  `branchName` VARCHAR(200) NULL COMMENT 'Bank branch name',
  `bankCode` VARCHAR(20) NULL COMMENT 'Bank code or routing number',
  `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `userId` (`userId`),
  CONSTRAINT `workshops_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  INDEX `idx_workshops_isActive` (`isActive`),
  INDEX `idx_workshops_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Step 3: Create workshop_inquiries table
-- Note: This table depends on workshops table, so workshops must be created first
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;

-- ============================================================================
-- Verification Queries (optional - run these to verify tables were created)
-- ============================================================================
-- SELECT 'workshops table' AS table_name, COUNT(*) AS row_count FROM workshops;
-- SELECT 'workshop_inquiries table' AS table_name, COUNT(*) AS row_count FROM workshop_inquiries;
-- SHOW CREATE TABLE workshops;
-- SHOW CREATE TABLE workshop_inquiries;
-- ============================================================================