-- Migration: Add Workshop Support
-- This migration adds support for workshop users in the system

-- Step 1: Update User role enum to include 'workshop'
-- Note: MySQL doesn't support ALTER ENUM directly, so we need to modify the column
ALTER TABLE `users` 
MODIFY COLUMN `role` ENUM('customer', 'admin', 'manager', 'supplier', 'workshop') 
NOT NULL DEFAULT 'customer';

-- 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',
  `vehicleTypes` JSON NULL COMMENT 'Array of vehicle types this workshop handles',
  `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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Step 3: Add indexes for better query performance
CREATE INDEX `idx_workshops_isActive` ON `workshops` (`isActive`);
CREATE INDEX `idx_workshops_email` ON `workshops` (`email`);