-- ============================================
-- HAZOP Action Table Creation Script
-- ============================================
-- This script creates the HazopActions table for the HAZOP/HAZID module
-- Run this in cPanel phpMyAdmin or MySQL interface

CREATE TABLE IF NOT EXISTS `HazopActions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `data` JSON NOT NULL COMMENT 'Stores action details including findingRef, description, node/parameter/guideword (HAZOP), category (HAZID), riskScore, riskLevel, evidence, and closureNotes',
  `status` ENUM('open', 'in-progress', 'closed', 'cancelled') NOT NULL DEFAULT 'open' COMMENT 'Current status of the action',
  `dueDate` DATE NOT NULL COMMENT 'Due date for action completion',
  `closedAt` DATE DEFAULT NULL COMMENT 'Date when action was closed',
  `HazopStudyId` INT NOT NULL COMMENT 'Foreign key to HazopStudies table',
  `ownerId` INT NOT NULL COMMENT 'Foreign key to Users table - user responsible for the action',
  `closedById` INT DEFAULT NULL COMMENT 'Foreign key to Users table - user who closed the action',
  `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `HazopStudyId` (`HazopStudyId`),
  KEY `ownerId` (`ownerId`),
  KEY `closedById` (`closedById`),
  KEY `idx_status` (`status`),
  KEY `idx_due_date` (`dueDate`),
  CONSTRAINT `HazopActions_ibfk_1` FOREIGN KEY (`HazopStudyId`) REFERENCES `HazopStudies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `HazopActions_ibfk_2` FOREIGN KEY (`ownerId`) REFERENCES `Users` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `HazopActions_ibfk_3` FOREIGN KEY (`closedById`) REFERENCES `Users` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- JSON Data Structure Documentation
-- ============================================
-- The `data` JSON field contains the following structure:
-- {
--   "findingRef": "string" (e.g., "N-01-F-02" for HAZOP or "HZD-03" for HAZID),
--   "description": "string" (recommendation/action description),
--   "nodeNumber": "string" (HAZOP only - e.g., "N-01"),
--   "parameter": "string" (HAZOP only - e.g., "Flow", "Pressure"),
--   "guideword": "string" (HAZOP only - e.g., "No", "More", "Less"),
--   "category": "string" (HAZID only - e.g., "Process", "Structural", "Human", "Environment", "External"),
--   "riskScore": number (1-25, calculated as likelihood × severity),
--   "riskLevel": "Low" | "Medium" | "High" | "Critical",
--   "evidence": "string" (evidence of action completion),
--   "closureNotes": "string" (notes about how action was closed)
-- }

-- ============================================
-- Verification Queries
-- ============================================
-- Run these after creating the table to verify it was created successfully
-- SHOW CREATE TABLE HazopActions;
-- SELECT * FROM HazopActions LIMIT 1;
-- DESCRIBE HazopActions;

-- ============================================
-- Example Query: Get Overdue Actions
-- ============================================
-- SELECT a.id, a.data->>'$.findingRef' as findingRef, a.data->>'$.description' as description,
--        a.status, a.dueDate, u.name as ownerName
-- FROM HazopActions a
-- JOIN Users u ON a.ownerId = u.id
-- WHERE a.status != 'closed' AND a.dueDate < CURDATE()
-- ORDER BY a.dueDate ASC;
