-- Migration: Maintenance RFQ and Quotes Tables
-- Run: cd database && mysql -u root -p wi_db < 2024_10_maintenance_rfq.sql (replace wi_db with DB name)

USE wi_db; -- Replace with your DB name

CREATE TABLE IF NOT EXISTS `maintenance_rfqs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `maintenance_job_id` bigint(20) UNSIGNED NOT NULL,
  `workshop_id` bigint(20) UNSIGNED NOT NULL,
  `rfq_number` varchar(50) DEFAULT NULL,
  `rfq_sent_date` date DEFAULT NULL,
  `status` enum('sent','quoted','selected','rejected') NOT NULL DEFAULT 'sent',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_job_workshop` (`maintenance_job_id`,`workshop_id`),
  KEY `fk_workshop` (`workshop_id`),
  CONSTRAINT `fk_rfqs_job` FOREIGN KEY (`maintenance_job_id`) REFERENCES `maintenance_jobs` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rfqs_workshop` FOREIGN KEY (`workshop_id`) REFERENCES `workshops` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `workshop_quotes` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `rfq_id` bigint(20) UNSIGNED NOT NULL,
  `quote_file` varchar(255) DEFAULT NULL,
  `quote_amount` decimal(12,2) DEFAULT NULL,
  `quote_received_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_quote_rfq` (`rfq_id`),
  CONSTRAINT `fk_quotes_rfq` FOREIGN KEY (`rfq_id`) REFERENCES `maintenance_rfqs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add column to maintenance_jobs for RFQ flag if needed
ALTER TABLE `maintenance_jobs` ADD COLUMN IF NOT EXISTS `is_rfq` tinyint(1) DEFAULT 0 AFTER `workshop`;