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

CREATE TABLE IF NOT EXISTS `HazopStudies` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `data` JSON NOT NULL COMMENT 'Stores all study data including studyType, title, processType, facilityName, processDescription, materials, operating conditions, nodes/hazards, status, and statistics',
  `ProjectId` INT NOT NULL COMMENT 'Foreign key to Projects table',
  `createdById` INT NOT NULL COMMENT 'Foreign key to Users table - user who created the study',
  `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ProjectId` (`ProjectId`),
  KEY `createdById` (`createdById`),
  KEY `idx_study_type` ((CAST(JSON_UNQUOTE(JSON_EXTRACT(`data`, '$.studyType')) AS CHAR(10)))),
  KEY `idx_status` ((CAST(JSON_UNQUOTE(JSON_EXTRACT(`data`, '$.status')) AS CHAR(20)))),
  KEY `idx_process_type` ((CAST(JSON_UNQUOTE(JSON_EXTRACT(`data`, '$.processType')) AS CHAR(20)))),
  CONSTRAINT `HazopStudies_ibfk_1` FOREIGN KEY (`ProjectId`) REFERENCES `Projects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `HazopStudies_ibfk_2` FOREIGN KEY (`createdById`) 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:
-- {
--   "studyType": "hazop" | "hazid",
--   "title": "string",
--   "processType": "oil-gas" | "chemical" | "mining" | "power" | "pharma" | "construction",
--   "facilityName": "string",
--   "processDescription": "string",
--   "materials": "string",
--   "operatingPressure": "string" (HAZOP only),
--   "operatingTemperature": "string" (HAZOP only),
--   "studyPhase": "string" (HAZOP only: design/operation/modification/revalidation),
--   "projectPhase": "string" (HAZID only: concept/design/construction/operation/decommission),
--   "studyLeader": "string",
--   "studyDate": "date",
--   "nodes": [HazopNode] (HAZOP only - array of nodes with findings),
--   "hazards": [HazidHazard] (HAZID only - array of hazards),
--   "status": "draft" | "in-progress" | "approved" | "closed",
--   "totalFindings": number,
--   "openActions": number,
--   "closedActions": number
-- }

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