-- Hazard Library Enhancement
USE risk_assessment_db;

-- Update risk_items table to include photos and more details
ALTER TABLE risk_items 
ADD COLUMN IF NOT EXISTS photos JSON NULL COMMENT 'Array of photo filenames',
ADD COLUMN IF NOT EXISTS typical_controls TEXT NULL COMMENT 'Standard control measures',
ADD COLUMN IF NOT EXISTS residual_risk VARCHAR(20) DEFAULT 'medium',
ADD COLUMN IF NOT EXISTS affected_persons TEXT NULL COMMENT 'Who is at risk',
ADD COLUMN IF NOT EXISTS is_template TINYINT(1) DEFAULT 1 COMMENT 'Is this a library template',
ADD COLUMN IF NOT EXISTS usage_count INT DEFAULT 0 COMMENT 'Times used in assessments',
ADD COLUMN IF NOT EXISTS last_used DATETIME NULL,
ADD COLUMN IF NOT EXISTS created_by INT NULL,
ADD COLUMN IF NOT EXISTS created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
ADD COLUMN IF NOT EXISTS updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
ADD FOREIGN KEY (created_by) REFERENCES users(id);

-- Control Measures Library
CREATE TABLE IF NOT EXISTS control_measures (
    id INT AUTO_INCREMENT PRIMARY KEY,
    risk_item_id INT NOT NULL,
    control_type ENUM('elimination', 'substitution', 'engineering', 'administrative', 'ppe') NOT NULL,
    control_description TEXT NOT NULL,
    implementation_notes TEXT NULL,
    cost_estimate VARCHAR(50) NULL,
    effectiveness VARCHAR(20) DEFAULT 'high',
    display_order INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (risk_item_id) REFERENCES risk_items(id) ON DELETE CASCADE,
    INDEX idx_risk_item (risk_item_id),
    INDEX idx_type (control_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Hazard Photos Library (for reference images)
CREATE TABLE IF NOT EXISTS hazard_library_photos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    risk_item_id INT NOT NULL,
    filename VARCHAR(255) NOT NULL,
    caption TEXT NULL,
    is_primary TINYINT(1) DEFAULT 0,
    display_order INT DEFAULT 0,
    uploaded_by INT NULL,
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (risk_item_id) REFERENCES risk_items(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id),
    INDEX idx_risk_item (risk_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Assessment Risk Templates (links assessments to library items)
ALTER TABLE assessment_risks
ADD COLUMN IF NOT EXISTS template_id INT NULL COMMENT 'Original library template used',
ADD FOREIGN KEY (template_id) REFERENCES risk_items(id) ON DELETE SET NULL;

-- Insert enhanced default hazards with controls
INSERT INTO risk_items (category_id, name, description, default_likelihood, default_severity, typical_controls, affected_persons, residual_risk) VALUES
-- Vehicle Operations (category_id = 1)
(1, 'Reversing Collisions', 'Risk of collision when reversing HGV in confined spaces', 4, 4, 
'Install reversing cameras and sensors\nUse banksman for guidance\nDesignated reversing areas with clear signage\nRegular driver training on reversing procedures', 
'Pedestrians, Other drivers, Site workers', 'low'),

(1, 'Vehicle Rollover', 'Risk of vehicle tipping when turning or on uneven ground', 2, 5,
'Speed limits in yard areas\nLevel and maintained surfaces\nWide turning circles\nDriver awareness training\nProper load securing',
'Driver, Nearby workers', 'low'),

(1, 'Blind Spot Incidents', 'Limited visibility around large vehicles', 4, 4,
'Multiple mirror systems\nProximity sensors\n360-degree camera systems\nExclusion zones around vehicle\nHigh-visibility clothing for all personnel',
'Pedestrians, Cyclists, Site staff', 'medium'),

-- Loading/Unloading (category_id = 2)
(2, 'Manual Handling Injuries', 'Strains and injuries from lifting heavy items', 5, 3,
'Mechanical handling equipment\nTeam lifting procedures\nWeight limits enforced\nLifting technique training\nRegular breaks',
'Delivery drivers, Warehouse staff', 'low'),

(2, 'Falls from Height', 'Falls from trailer bed or loading dock', 3, 5,
'Edge protection on loading bays\nFall arrest equipment\nProper access equipment\nLevel with trailer\nSafety barriers',
'Delivery drivers, Loading staff', 'low'),

(2, 'Crushing Injuries', 'Risk of being caught between vehicle and fixed object', 3, 5,
'Segregated loading areas\nTraffic management system\nClear communication protocols\nEmergency stop systems\nExclusion zones',
'All site personnel', 'low'),

(2, 'Falling Objects', 'Items falling during loading/unloading', 4, 4,
'Proper stacking procedures\nLoad securing equipment\nHard hat requirements\nRestricted access zones\nLoad integrity checks',
'Delivery crew, Nearby workers', 'low'),

-- Access & Egress (category_id = 3)
(3, 'Slips and Trips', 'Slipping on wet surfaces or tripping on obstacles', 5, 2,
'Regular cleaning and maintenance\nAnti-slip surfaces\nGood housekeeping\nAdequate lighting\nClear walkways',
'All personnel', 'low'),

(3, 'Unsafe Access Points', 'Inadequate or dangerous entry/exit points', 3, 4,
'Designated access routes\nProper steps and handrails\nLevel surfaces\nClear signage\nRegular inspections',
'Drivers, Staff', 'low'),

(3, 'Door Strike Injuries', 'Being struck by opening vehicle doors', 4, 3,
'Controlled door opening procedures\nWarning signs\nSafe working distances\nDriver training\nHigh-visibility clothing',
'Nearby workers', 'low'),

-- Environmental (category_id = 4)
(4, 'Poor Visibility', 'Reduced visibility due to weather or lighting', 4, 3,
'Adequate site lighting\nReflective markings\nSlower speeds in poor conditions\nWeather delay procedures\nHigh-vis requirements',
'All site users', 'medium'),

(4, 'Ice and Snow Hazards', 'Slippery surfaces in winter conditions', 4, 4,
'Gritting procedures\nSnow clearance\nWinter tyres requirement\nDelay procedures in severe weather\nExtra caution signage',
'All personnel, Drivers', 'medium'),

(4, 'Extreme Weather', 'High winds, heavy rain affecting operations', 3, 4,
'Weather monitoring\nSuspension procedures\nSecuring loose items\nProtected waiting areas\nEmergency protocols',
'All site personnel', 'low'),

-- People & Pedestrians (category_id = 5)
(5, 'Vehicle-Pedestrian Collision', 'Collision between vehicles and people on foot', 4, 5,
'Segregated walkways\nCrossing points with barriers\nTraffic management system\nSpeed limits\nBanksman use\nHigh-vis clothing mandatory',
'All pedestrians on site', 'medium'),

(5, 'Contractor Coordination', 'Multiple contractors working simultaneously', 4, 3,
'Site induction for all\nCoordination meetings\nClear communication\nDedicated site supervisor\nPermit systems',
'All contractors and staff', 'low'),

(5, 'Lone Working', 'Staff working alone during deliveries', 3, 4,
'Buddy system\nCheck-in procedures\nMobile communication\nEmergency alarms\nRisk-specific training',
'Lone workers', 'low');

-- Insert control measures for first hazard as example
INSERT INTO control_measures (risk_item_id, control_type, control_description, effectiveness) VALUES
(1, 'engineering', 'Install reversing cameras and audible warning systems on all vehicles', 'high'),
(1, 'administrative', 'Implement banksman procedures for all reversing operations in confined spaces', 'high'),
(1, 'administrative', 'Create and mark designated reversing zones with clear signage', 'medium'),
(1, 'administrative', 'Provide regular training to drivers on reversing procedures and blind spots', 'high'),
(1, 'ppe', 'Require high-visibility clothing for all personnel in vehicle operating areas', 'medium');

SELECT 'Hazard library schema created successfully!' as Status;
