-- Risk Assessment System Database Schema
-- Version: 2.0.0
-- Date: January 2026
-- Total Tables: 16

START TRANSACTION;

-- Database Creation
CREATE DATABASE IF NOT EXISTS `risk_assessment_db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `risk_assessment_db`;

-- =====================================================
-- 2. LOCATIONS TABLE
-- =====================================================
CREATE TABLE `locations` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `address` TEXT NULL,
  `city` VARCHAR(100) NULL,
  `state` VARCHAR(50) NULL,
  `postal_code` VARCHAR(20) NULL,
  `country` VARCHAR(100) NULL,
  `contact_name` VARCHAR(100) NULL,
  `contact_email` VARCHAR(100) NULL,
  `contact_phone` VARCHAR(50) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` INT(11) UNSIGNED NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_active` (`is_active`),
  KEY `fk_locations_created_by` (`created_by`),
  CONSTRAINT `fk_locations_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 3. RISK CATEGORIES TABLE
-- =====================================================
CREATE TABLE `risk_categories` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `description` TEXT NULL,
  `display_order` INT(11) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_display_order` (`display_order`),
  KEY `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 4. RISK ITEMS TABLE
-- =====================================================
CREATE TABLE `risk_items` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` INT(11) UNSIGNED NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  `description` TEXT NULL,
  `default_control_measures` TEXT NULL,
  `display_order` INT(11) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_category` (`category_id`),
  KEY `idx_display_order` (`display_order`),
  KEY `idx_active` (`is_active`),
  CONSTRAINT `fk_risk_items_category` FOREIGN KEY (`category_id`) REFERENCES `risk_categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 5. ASSESSMENTS TABLE
-- =====================================================
CREATE TABLE `assessments` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `assessment_number` VARCHAR(50) NOT NULL,
  `location_id` INT(11) UNSIGNED NOT NULL,
  `title` VARCHAR(200) NOT NULL,
  `description` TEXT NULL,
  `assessor_id` INT(11) UNSIGNED NOT NULL,
  `assessment_date` DATE NOT NULL,
  `status` ENUM('draft', 'in_progress', 'completed', 'archived') NOT NULL DEFAULT 'draft',
  `overall_risk_level` ENUM('low', 'medium', 'high') NULL,
  `notes` TEXT NULL,
  `created_by` INT(11) UNSIGNED NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `assessment_number` (`assessment_number`),
  KEY `idx_location` (`location_id`),
  KEY `idx_assessor` (`assessor_id`),
  KEY `idx_status` (`status`),
  KEY `idx_date` (`assessment_date`),
  KEY `idx_created_by` (`created_by`),
  CONSTRAINT `fk_assessments_location` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_assessments_assessor` FOREIGN KEY (`assessor_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_assessments_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 6. ASSESSMENT_RISKS TABLE
-- =====================================================
CREATE TABLE `assessment_risks` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `assessment_id` INT(11) UNSIGNED NOT NULL,
  `risk_item_id` INT(11) UNSIGNED NOT NULL,
  `likelihood` INT(11) NOT NULL CHECK (`likelihood` BETWEEN 1 AND 5),
  `severity` INT(11) NOT NULL CHECK (`severity` BETWEEN 1 AND 5),
  `risk_score` INT(11) GENERATED ALWAYS AS (`likelihood` * `severity`) STORED,
  `risk_level` VARCHAR(20) GENERATED ALWAYS AS (
    CASE 
      WHEN (`likelihood` * `severity`) <= 6 THEN 'low'
      WHEN (`likelihood` * `severity`) <= 12 THEN 'medium'
      ELSE 'high'
    END
  ) STORED,
  `control_measures` TEXT NULL,
  `notes` TEXT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_assessment` (`assessment_id`),
  KEY `idx_risk_item` (`risk_item_id`),
  KEY `idx_risk_score` (`risk_score`),
  CONSTRAINT `fk_assessment_risks_assessment` FOREIGN KEY (`assessment_id`) REFERENCES `assessments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_assessment_risks_risk_item` FOREIGN KEY (`risk_item_id`) REFERENCES `risk_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 7. ACTION_ITEMS TABLE
-- =====================================================
CREATE TABLE `action_items` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `assessment_id` INT(11) UNSIGNED NOT NULL,
  `assessment_risk_id` INT(11) UNSIGNED NULL,
  `title` VARCHAR(200) NOT NULL,
  `description` TEXT NULL,
  `responsible_person` VARCHAR(100) NULL,
  `due_date` DATE NULL,
  `priority` ENUM('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'medium',
  `status` ENUM('pending', 'in_progress', 'completed', 'cancelled') NOT NULL DEFAULT 'pending',
  `completed_date` DATE NULL,
  `notes` TEXT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_assessment` (`assessment_id`),
  KEY `idx_assessment_risk` (`assessment_risk_id`),
  KEY `idx_status` (`status`),
  KEY `idx_priority` (`priority`),
  KEY `idx_due_date` (`due_date`),
  CONSTRAINT `fk_action_items_assessment` FOREIGN KEY (`assessment_id`) REFERENCES `assessments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_action_items_assessment_risk` FOREIGN KEY (`assessment_risk_id`) REFERENCES `assessment_risks` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 8. PERMISSIONS TABLE
-- =====================================================
CREATE TABLE `permissions` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `role` ENUM('admin', 'assessor', 'viewer') NOT NULL,
  `resource` VARCHAR(50) NOT NULL,
  `can_create` TINYINT(1) NOT NULL DEFAULT 0,
  `can_read` TINYINT(1) NOT NULL DEFAULT 0,
  `can_update` TINYINT(1) NOT NULL DEFAULT 0,
  `can_delete` TINYINT(1) NOT NULL DEFAULT 0,
  `can_export` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `role_resource` (`role`, `resource`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 9. CUSTOM_FIELDS TABLE
-- =====================================================
CREATE TABLE `custom_fields` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `entity_type` ENUM('location', 'assessment', 'user', 'risk_item') NOT NULL,
  `field_name` VARCHAR(50) NOT NULL,
  `field_label` VARCHAR(100) NOT NULL,
  `field_type` ENUM('text', 'textarea', 'number', 'date', 'select', 'checkbox', 'email', 'url') NOT NULL,
  `field_options` TEXT NULL,
  `is_required` TINYINT(1) NOT NULL DEFAULT 0,
  `display_order` INT(11) NOT NULL DEFAULT 0,
  `help_text` VARCHAR(255) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_entity_type` (`entity_type`),
  KEY `idx_display_order` (`display_order`),
  KEY `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 10. CUSTOM_FIELD_VALUES TABLE
-- =====================================================
CREATE TABLE `custom_field_values` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `custom_field_id` INT(11) UNSIGNED NOT NULL,
  `entity_id` INT(11) UNSIGNED NOT NULL,
  `field_value` TEXT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_custom_field` (`custom_field_id`),
  KEY `idx_entity` (`entity_id`),
  CONSTRAINT `fk_custom_field_values_field` FOREIGN KEY (`custom_field_id`) REFERENCES `custom_fields` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 11. SYSTEM_SETTINGS TABLE
-- =====================================================
CREATE TABLE `system_settings` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `setting_key` VARCHAR(50) NOT NULL,
  `setting_value` TEXT NULL,
  `setting_type` ENUM('string', 'number', 'boolean', 'json') NOT NULL DEFAULT 'string',
  `description` VARCHAR(255) NULL,
  `is_public` TINYINT(1) NOT NULL DEFAULT 0,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 12. AUDIT_LOG TABLE
-- =====================================================
CREATE TABLE `audit_log` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) UNSIGNED NULL,
  `action` VARCHAR(50) NOT NULL,
  `entity_type` VARCHAR(50) NOT NULL,
  `entity_id` INT(11) UNSIGNED NULL,
  `old_values` TEXT NULL,
  `new_values` TEXT NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` VARCHAR(255) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_entity` (`entity_type`, `entity_id`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_audit_log_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 13. EMAIL_QUEUE TABLE
-- =====================================================
CREATE TABLE `email_queue` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `to_email` VARCHAR(100) NOT NULL,
  `subject` VARCHAR(200) NOT NULL,
  `body` TEXT NOT NULL,
  `priority` ENUM('low', 'normal', 'high') NOT NULL DEFAULT 'normal',
  `status` ENUM('pending', 'sending', 'sent', 'failed') NOT NULL DEFAULT 'pending',
  `attempts` INT(11) NOT NULL DEFAULT 0,
  `max_attempts` INT(11) NOT NULL DEFAULT 3,
  `error_message` TEXT NULL,
  `scheduled_at` DATETIME NULL,
  `sent_at` DATETIME NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `idx_priority` (`priority`),
  KEY `idx_scheduled` (`scheduled_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 14. FILE_VERSIONS TABLE
-- =====================================================
CREATE TABLE `file_versions` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `entity_type` VARCHAR(50) NOT NULL,
  `entity_id` INT(11) UNSIGNED NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_path` VARCHAR(500) NOT NULL,
  `file_size` BIGINT UNSIGNED NOT NULL,
  `mime_type` VARCHAR(100) NULL,
  `version_number` INT(11) NOT NULL DEFAULT 1,
  `file_hash` VARCHAR(64) NOT NULL,
  `uploaded_by` INT(11) UNSIGNED NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_entity` (`entity_type`, `entity_id`),
  KEY `idx_uploaded_by` (`uploaded_by`),
  KEY `idx_hash` (`file_hash`),
  CONSTRAINT `fk_file_versions_uploaded_by` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 16. SYNC_QUEUE TABLE (for offline sync)
-- =====================================================
CREATE TABLE `sync_queue` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) UNSIGNED NOT NULL,
  `action` VARCHAR(50) NOT NULL,
  `entity_type` VARCHAR(50) NOT NULL,
  `entity_id` INT(11) UNSIGNED NULL,
  `data` TEXT NOT NULL,
  `status` ENUM('pending', 'processing', 'completed', 'failed') NOT NULL DEFAULT 'pending',
  `error_message` TEXT NULL,
  `attempts` INT(11) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `processed_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_sync_queue_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default permissions
INSERT INTO `permissions` (`role`, `resource`, `can_create`, `can_read`, `can_update`, `can_delete`, `can_export`) VALUES
-- Admin permissions (full access)
('admin', 'assessments', 1, 1, 1, 1, 1),
('admin', 'locations', 1, 1, 1, 1, 1),
('admin', 'users', 1, 1, 1, 1, 1),
('admin', 'risk_items', 1, 1, 1, 1, 1),
('admin', 'settings', 1, 1, 1, 1, 1),
('admin', 'reports', 1, 1, 1, 1, 1),
('admin', 'action_items', 1, 1, 1, 1, 1),
-- Assessor permissions
('assessor', 'assessments', 1, 1, 1, 0, 1),
('assessor', 'locations', 0, 1, 0, 0, 0),
('assessor', 'users', 0, 1, 0, 0, 0),
('assessor', 'risk_items', 0, 1, 0, 0, 0),
('assessor', 'settings', 0, 1, 0, 0, 0),
('assessor', 'reports', 0, 1, 0, 0, 1),
('assessor', 'action_items', 1, 1, 1, 0, 1),
-- Viewer permissions (read-only)
('viewer', 'assessments', 0, 1, 0, 0, 1),
('viewer', 'locations', 0, 1, 0, 0, 0),
('viewer', 'users', 0, 0, 0, 0, 0),
('viewer', 'risk_items', 0, 1, 0, 0, 0),
('viewer', 'settings', 0, 0, 0, 0, 0),
('viewer', 'reports', 0, 1, 0, 0, 1),
('viewer', 'action_items', 0, 1, 0, 0, 0);

-- Insert default risk categories
INSERT INTO `risk_categories` (`name`, `description`, `display_order`) VALUES
('Physical Hazards', 'Risks related to physical environment and equipment', 1),
('Chemical Hazards', 'Risks from hazardous substances and chemicals', 2),
('Biological Hazards', 'Risks from biological agents and organisms', 3),
('Ergonomic Hazards', 'Risks from workplace design and repetitive tasks', 4),
('Psychosocial Hazards', 'Risks affecting mental health and wellbeing', 5),
('Fire Safety', 'Risks related to fire hazards and prevention', 6),
('Electrical Safety', 'Risks from electrical equipment and systems', 7),
('Slip, Trip and Fall', 'Risks of slipping, tripping, or falling', 8),
('Manual Handling', 'Risks from lifting and moving objects', 9),
('Environmental Hazards', 'Risks from environmental conditions', 10);

-- Insert default risk items
INSERT INTO `risk_items` (`category_id`, `name`, `description`, `default_control_measures`, `display_order`) VALUES
-- Physical Hazards
(1, 'Moving machinery', 'Risk of injury from moving parts of machinery', 'Machine guarding, safety training, lockout/tagout procedures', 1),
(1, 'Working at height', 'Risk of falls from elevated work areas', 'Fall protection equipment, guardrails, safety harnesses', 2),
(1, 'Confined spaces', 'Risk of injury or death in confined spaces', 'Permit system, atmospheric testing, rescue plan', 3),
-- Chemical Hazards
(2, 'Hazardous substances', 'Exposure to harmful chemicals', 'PPE, ventilation, safety data sheets, proper storage', 1),
(2, 'Flammable materials', 'Risk of fire or explosion', 'Proper storage, no ignition sources, fire suppression', 2),
(2, 'Toxic materials', 'Risk of poisoning or health damage', 'Containment, PPE, emergency procedures, training', 3),
-- Biological Hazards
(3, 'Blood-borne pathogens', 'Risk of infection from bodily fluids', 'Universal precautions, PPE, vaccination programs', 1),
(3, 'Mold and fungi', 'Risk of respiratory issues', 'Moisture control, proper ventilation, remediation', 2),
(3, 'Animal hazards', 'Risk from animals or insects', 'Protective equipment, awareness training, first aid', 3),
-- Ergonomic Hazards
(4, 'Repetitive motion', 'Risk of musculoskeletal disorders', 'Job rotation, ergonomic equipment, regular breaks', 1),
(4, 'Poor workstation design', 'Risk of strain and discomfort', 'Ergonomic assessment, adjustable furniture, training', 2),
(4, 'Prolonged standing/sitting', 'Risk of circulatory and back problems', 'Anti-fatigue mats, sit-stand desks, movement breaks', 3),
-- Psychosocial Hazards
(5, 'Work-related stress', 'Mental health impacts from work pressure', 'Workload management, support systems, wellness programs', 1),
(5, 'Violence and aggression', 'Risk of physical or verbal abuse', 'Security measures, conflict resolution training, reporting systems', 2),
(5, 'Bullying and harassment', 'Risk to mental wellbeing', 'Anti-bullying policy, reporting procedures, training', 3),
-- Fire Safety
(6, 'Fire hazards', 'Risk of fire outbreak', 'Fire alarms, extinguishers, evacuation plan, regular drills', 1),
(6, 'Blocked exits', 'Impeded emergency evacuation', 'Keep exits clear, emergency lighting, signage', 2),
(6, 'Flammable storage', 'Fire risk from stored materials', 'Proper storage cabinets, ventilation, no ignition sources', 3),
-- Electrical Safety
(7, 'Electrical equipment', 'Risk of electrocution or fire', 'Regular inspections, qualified electricians, proper grounding', 1),
(7, 'Damaged cords/plugs', 'Risk of electric shock', 'Regular inspections, immediate repair/replacement', 2),
(7, 'Overloaded circuits', 'Risk of overheating and fire', 'Proper load management, circuit breaker protection', 3),
-- Slip, Trip and Fall
(8, 'Wet or slippery floors', 'Risk of slipping', 'Warning signs, non-slip surfaces, immediate cleanup', 1),
(8, 'Uneven surfaces', 'Risk of tripping', 'Repair damaged surfaces, clear marking, good lighting', 2),
(8, 'Obstructions in walkways', 'Risk of tripping', 'Housekeeping procedures, clear pathways, storage systems', 3),
-- Manual Handling
(9, 'Heavy lifting', 'Risk of back injury', 'Lifting equipment, proper technique training, weight limits', 1),
(9, 'Awkward postures', 'Risk of musculoskeletal injury', 'Ergonomic tools, work height adjustment, training', 2),
(9, 'Repetitive lifting', 'Risk of chronic injury', 'Job rotation, mechanical aids, task redesign', 3),
-- Environmental Hazards
(10, 'Extreme temperatures', 'Risk of heat stress or hypothermia', 'Climate control, appropriate clothing, rest breaks', 1),
(10, 'Poor lighting', 'Risk of eye strain and accidents', 'Adequate lighting levels, regular maintenance, task lighting', 2),
(10, 'Noise exposure', 'Risk of hearing damage', 'Hearing protection, noise barriers, equipment maintenance', 3);

-- Insert default system settings
INSERT INTO `system_settings` (`setting_key`, `setting_value`, `setting_type`, `description`, `is_public`) VALUES
('system_name', 'Risk Assessment System', 'string', 'Name of the system', 1),
('company_name', 'Your Company Name', 'string', 'Company name', 1),
('date_format', 'Y-m-d', 'string', 'PHP date format', 0),
('timezone', 'UTC', 'string', 'System timezone', 0),
('theme', 'light', 'string', 'Default theme (light/dark)', 1),
('enable_offline_mode', '1', 'boolean', 'Enable PWA offline capabilities', 1),
('enable_pdf_export', '1', 'boolean', 'Enable PDF export feature', 1),
('max_upload_size', '10485760', 'number', 'Max file upload size in bytes (10MB)', 0),
('session_timeout', '3600', 'number', 'Session timeout in seconds (1 hour)', 0),
('assessment_prefix', 'RA', 'string', 'Assessment number prefix', 0);

COMMIT;

-- View for overdue action items
CREATE OR REPLACE VIEW `v_overdue_actions` AS
SELECT 
    ai.*,
    a.assessment_number,
    a.title as assessment_title,
    l.name as location_name,
    DATEDIFF(CURDATE(), ai.due_date) as days_overdue
FROM action_items ai
JOIN assessments a ON ai.assessment_id = a.id
JOIN locations l ON a.location_id = l.id
WHERE ai.status != 'completed' 
AND ai.due_date < CURDATE();

-- Success message
SELECT 'Database schema created successfully!' as message;
SELECT 'Total tables created: 16' as info;
SELECT 'Default admin user created - Username: admin, Password: admin123' as credentials;
