-- Migration: Ensure All Assessment Fields are Present in Posts Table
-- Date: 2024
-- Description: Adds missing assessment fields for infrastructure, zone, and asset risk calculations
-- This ensures compatibility with the facility assessment form

-- Add missing assessment fields if they don't exist
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `facility_type` VARCHAR(100) COMMENT 'Type of facility (Bank, Hospital, etc.)';
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `location` TEXT COMMENT 'Facility street address/location';
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `owner_name` VARCHAR(255) COMMENT 'Name of facility owner';
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `owner_phone` VARCHAR(20) COMMENT 'Owner telephone number';
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `owner_address` TEXT COMMENT 'Owner address';

-- Infrastructure Assessment Fields (Good/Fair/Poor)
ALTER TABLE `posts` MODIFY COLUMN `assessment_security_post` ENUM('Good', 'Fair', 'Poor') DEFAULT 'Fair' COMMENT 'Security Post Condition';
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `assessment_fence` ENUM('Good', 'Fair', 'Poor') DEFAULT 'Fair' COMMENT 'Fence/Perimeter Condition';
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `assessment_cctv_alarms` ENUM('Good', 'Fair', 'Poor') DEFAULT 'Fair' COMMENT 'CCTV & Alarms Condition';
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `assessment_lighting` ENUM('Good', 'Fair', 'Poor') DEFAULT 'Fair' COMMENT 'Interior Lighting Condition';
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `assessment_toilet` ENUM('Good', 'Fair', 'Poor') DEFAULT 'Fair' COMMENT 'Toilet Facilities Condition';
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `assessment_fire_extinguishers` ENUM('Good', 'Fair', 'Poor') DEFAULT 'Fair' COMMENT 'Fire Safety Condition';
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `assessment_accessibility` ENUM('Good', 'Fair', 'Poor') DEFAULT 'Fair' COMMENT 'Road Accessibility Condition';

-- Zone Safety Assessment Fields (Low/Medium/High or High/Moderate/Far)
ALTER TABLE `posts` MODIFY COLUMN `assessment_crime_rate` ENUM('Low', 'Medium', 'High') DEFAULT 'Medium' COMMENT 'Crime Rate in Area';
ALTER TABLE `posts` MODIFY COLUMN `assessment_living_standards` ENUM('Low', 'Medium', 'High') DEFAULT 'Medium' COMMENT 'Living Standards in Area';
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `assessment_public_security_proximity` ENUM('Close', 'Moderate', 'Far') DEFAULT 'Moderate' COMMENT 'Proximity to Public Security';
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `assessment_labour_availability` ENUM('Abundant', 'Moderate', 'Scarce') DEFAULT 'Moderate' COMMENT 'Labour Availability';

-- Physical Assets Assessment Fields
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `surface_area` VARCHAR(50) COMMENT 'Surface area category (petite, normale, vaste, tres_vaste)';
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `access_points` VARCHAR(50) COMMENT 'Access points category (1_2, 3_4, 5, 5_plus)';
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `num_buildings` VARCHAR(50) COMMENT 'Number of buildings category (1, 2, 3_plus)';
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `content_value` VARCHAR(50) COMMENT 'Content value category (low, moderate, high, very_high)';

-- Services and Cost Fields
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `grand_total` DECIMAL(15, 2) DEFAULT 0 COMMENT 'Total cost of all services';

-- Assessment Observations and Recommendations
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `observations` LONGTEXT COMMENT 'General observations about the facility';
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `recommendations` LONGTEXT COMMENT 'Security recommendations';

-- Create post_services table if it doesn't exist
CREATE TABLE IF NOT EXISTS `post_services` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `post_id` INT NOT NULL,
    `service_ref` VARCHAR(50),
    `service_name` VARCHAR(255) NOT NULL,
    `quantity` INT NOT NULL DEFAULT 1,
    `unit_cost` DECIMAL(15, 2) NOT NULL DEFAULT 0,
    `total_cost` DECIMAL(15, 2) NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE CASCADE,
    INDEX `post_id` (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Drop old assessment columns that are no longer used
ALTER TABLE `posts` DROP COLUMN IF EXISTS `assessment_post`;
ALTER TABLE `posts` DROP COLUMN IF EXISTS `assessment_content_value`;
ALTER TABLE `posts` DROP COLUMN IF EXISTS `assessment_overall_risk`;
ALTER TABLE `posts` DROP COLUMN IF EXISTS `internal_access_control`;
ALTER TABLE `posts` DROP COLUMN IF EXISTS `internal_surveillance`;
ALTER TABLE `posts` DROP COLUMN IF EXISTS `internal_alarm_system`;
ALTER TABLE `posts` DROP COLUMN IF EXISTS `internal_lighting`;
ALTER TABLE `posts` DROP COLUMN IF EXISTS `internal_fire_safety`;
ALTER TABLE `posts` DROP COLUMN IF EXISTS `external_perimeter_security`;
ALTER TABLE `posts` DROP COLUMN IF EXISTS `external_external_lighting`;
ALTER TABLE `posts` DROP COLUMN IF EXISTS `external_parking_area`;
ALTER TABLE `posts` DROP COLUMN IF EXISTS `external_surrounding_area`;
ALTER TABLE `posts` DROP COLUMN IF EXISTS `external_access_points`;
ALTER TABLE `posts` DROP COLUMN IF EXISTS `overall_risk_score`;

-- Verify the final schema
-- SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS 
-- WHERE TABLE_SCHEMA = 'ssci' AND TABLE_NAME = 'posts' 
-- ORDER BY ORDINAL_POSITION;
