-- Migration: Workshops Setup Table
-- Run: mysql -u root -p wi_db_name < this_file (adjust DB)

CREATE TABLE IF NOT EXISTS `workshops` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `contact_person` varchar(255) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `email` varchar(191) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `inactive_reason` 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 `idx_workshops_status` (`status`),
  KEY `idx_workshops_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed sample data
INSERT IGNORE INTO `workshops` (`name`, `contact_person`, `phone`, `email`, `address`) VALUES
('Main Workshop', 'John Doe', '08012345678', 'john@workshop.com', 'Plot 123, Industrial Area'),
('ABC Repairs Ltd', 'Jane Smith', '08087654321', 'jane@abc.com', '15 Lagos Road'),
('FleetPro Garage', 'Mike Johnson', '08123456789', 'mike@fleetpro.com', 'Behind Airport, Abuja');

-- Optional: Add FK to maintenance_jobs later
-- ALTER TABLE maintenance_jobs ADD COLUMN workshop_id BIGINT UNSIGNED NULL AFTER workshop,
-- ADD FOREIGN KEY (workshop_id) REFERENCES workshops(id);