-- Migration: Add Facility and Assessment Columns to Posts Table
-- Date: 2024
-- Description: Adds facility type, owner phone/address, location, and detailed assessment factors

ALTER TABLE `posts` 
ADD COLUMN IF NOT EXISTS `facility_type` VARCHAR(100) COMMENT 'Type of facility (Bank, Hospital, etc.)',
ADD COLUMN IF NOT EXISTS `owner_name` VARCHAR(255) COMMENT 'Name of facility owner',
ADD COLUMN IF NOT EXISTS `owner_phone` VARCHAR(20) COMMENT 'Owner telephone number',
ADD COLUMN IF NOT EXISTS `owner_address` TEXT COMMENT 'Owner address',
ADD COLUMN IF NOT EXISTS `location` TEXT COMMENT 'Facility street address/location',
ADD COLUMN IF NOT EXISTS `active` BOOLEAN DEFAULT TRUE COMMENT 'Facility is active',
-- Internal Assessment Factors (0-100 scale)
ADD COLUMN IF NOT EXISTS `internal_access_control` INT DEFAULT 50 COMMENT 'Access Control assessment (0-100)',
ADD COLUMN IF NOT EXISTS `internal_surveillance` INT DEFAULT 50 COMMENT 'CCTV & Surveillance assessment (0-100)',
ADD COLUMN IF NOT EXISTS `internal_alarm_system` INT DEFAULT 50 COMMENT 'Alarm System assessment (0-100)',
ADD COLUMN IF NOT EXISTS `internal_lighting` INT DEFAULT 50 COMMENT 'Interior Lighting assessment (0-100)',
ADD COLUMN IF NOT EXISTS `internal_fire_safety` INT DEFAULT 50 COMMENT 'Fire Safety Systems assessment (0-100)',
-- External Assessment Factors (0-100 scale)
ADD COLUMN IF NOT EXISTS `external_perimeter_security` INT DEFAULT 50 COMMENT 'Perimeter Security assessment (0-100)',
ADD COLUMN IF NOT EXISTS `external_external_lighting` INT DEFAULT 50 COMMENT 'External Lighting assessment (0-100)',
ADD COLUMN IF NOT EXISTS `external_parking_area` INT DEFAULT 50 COMMENT 'Parking Area Safety assessment (0-100)',
ADD COLUMN IF NOT EXISTS `external_surrounding_area` INT DEFAULT 50 COMMENT 'Surrounding Area Risk assessment (0-100)',
ADD COLUMN IF NOT EXISTS `external_access_points` INT DEFAULT 50 COMMENT 'Number of Access Points assessment (0-100)',
-- Overall Risk Score
ADD COLUMN IF NOT EXISTS `overall_risk_score` INT DEFAULT 50 COMMENT 'Overall Risk Score (0-100)';
