-- Migration: Update Posts Table with New Assessment Columns and Services
-- Description: Add infrastructure assessment, observations, recommendations, and services fields

-- Add new assessment and infrastructure fields to posts table
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `assessment_security_post` ENUM('Good', 'Fair', 'Poor') DEFAULT 'Fair' AFTER `assessment_post`;
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `observations` LONGTEXT AFTER `assessment_overall_risk`;
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `recommendations` LONGTEXT AFTER `observations`;
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `surface_area` DECIMAL(10, 2) AFTER `recommendations`;
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `access_points` INT AFTER `surface_area`;
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `num_buildings` INT AFTER `access_points`;
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `content_value` DECIMAL(15, 2) AFTER `num_buildings`;
ALTER TABLE `posts` ADD COLUMN IF NOT EXISTS `grand_total` DECIMAL(15, 2) AFTER `content_value`;

-- Create Services table for repeating services
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;
