-- ========================================
-- Fix Missing Columns in Database
-- Adds columns referenced in PHP but missing from schema
-- ========================================

USE risk_assessment_db;

-- Add missing columns to assessments table
ALTER TABLE assessments 
ADD COLUMN IF NOT EXISTS visit_time TIME NULL COMMENT 'Time of visit' AFTER visit_date;

ALTER TABLE assessments 
ADD COLUMN IF NOT EXISTS description TEXT NULL COMMENT 'Assessment description' AFTER additional_notes;

ALTER TABLE assessments 
ADD COLUMN IF NOT EXISTS is_locked BOOLEAN DEFAULT 0 COMMENT 'Lock assessment from editing' AFTER status;

-- Add missing columns to assessment_risks table (if needed)
ALTER TABLE assessment_risks
ADD COLUMN IF NOT EXISTS hazard_name VARCHAR(255) NULL COMMENT 'Name of the hazard' AFTER risk_item_id;

ALTER TABLE assessment_risks
ADD COLUMN IF NOT EXISTS location_area VARCHAR(255) NULL COMMENT 'Specific area within location' AFTER hazard_name;

-- Verify changes
SELECT 'Missing columns added successfully!' as Status;

-- Show updated assessments structure
DESCRIBE assessments;

SELECT '---' as Separator;

-- Show updated assessment_risks structure  
DESCRIBE assessment_risks;
