-- =====================================================
-- SIMPLIFIED CONSTRUCTION REPORTING - DATABASE UPDATE
-- =====================================================

-- Create Areas table
CREATE TABLE IF NOT EXISTS areas (
    area_id INT PRIMARY KEY AUTO_INCREMENT,
    area_code VARCHAR(20) NOT NULL UNIQUE,
    area_name VARCHAR(100) NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert sample areas
INSERT INTO areas (area_code, area_name, description) VALUES
('AREA-1', 'Area 1', 'Construction Area 1'),
('AREA-2', 'Area 2', 'Construction Area 2'),
('AREA-3', 'Area 3', 'Construction Area 3'),
('PPU', 'Product Processing Unit', 'Product Processing Unit'),
('OFFSITE', 'Offsite', 'Offsite Facilities');

-- Add area_id to daily_reports table (if not exists)
ALTER TABLE daily_reports 
ADD COLUMN IF NOT EXISTS area_id INT AFTER project_id,
ADD CONSTRAINT fk_daily_reports_area FOREIGN KEY (area_id) REFERENCES areas(area_id);

-- Make previously required fields nullable for simplified reporting
ALTER TABLE daily_reports 
MODIFY COLUMN shift ENUM('Day', 'Night', 'Full Day') NULL,
MODIFY COLUMN quantity_completed DECIMAL(12,2) NULL DEFAULT NULL,
MODIFY COLUMN progress_percentage DECIMAL(5,2) NULL DEFAULT NULL,
MODIFY COLUMN cumulative_percentage DECIMAL(5,2) NULL DEFAULT NULL,
MODIFY COLUMN manpower_count INT NULL DEFAULT NULL,
MODIFY COLUMN safety_incidents INT NULL DEFAULT NULL,
MODIFY COLUMN quality_issues INT NULL DEFAULT NULL;

-- Verify the changes
SELECT 'Areas table created successfully' as status;
SELECT * FROM areas;
