-- ============================================
-- Permit to Work Table Creation Script
-- ============================================
-- This script creates the PermitToWorks table for the PTW system
-- Run this in cPanel phpMyAdmin or MySQL interface

CREATE TABLE IF NOT EXISTS `PermitToWorks` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `data` JSON DEFAULT NULL COMMENT 'Stores all permit form data including work type, location, dates, questions, etc.',
  `status` ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending' COMMENT 'Approval status of the permit',
  `aiAnalysis` JSON DEFAULT NULL COMMENT 'Stores AI analysis results including risk level, hazards, controls, PPE, etc.',
  `ProjectId` INT NOT NULL COMMENT 'Foreign key to Projects table',
  `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ProjectId` (`ProjectId`),
  CONSTRAINT `PermitToWorks_ibfk_1` FOREIGN KEY (`ProjectId`) REFERENCES `Projects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Verification Query
-- ============================================
-- Run this after creating the table to verify it was created successfully
-- SHOW CREATE TABLE PermitToWorks;
-- SELECT * FROM PermitToWorks LIMIT 1;
