-- CEMS Project Status & Schema Update (MySQL)
USE cems_db;

-- 1. Create Project Status Table
CREATE TABLE IF NOT EXISTS project_status_tb (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    color_bg VARCHAR(20),
    color_text VARCHAR(20),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Seed Default Statuses
INSERT INTO project_status_tb (name, color_bg, color_text) VALUES 
('Pending', '#e0f2fe', '#075985'),
('Ongoing', '#fef9c3', '#854d0e'),
('Completed', '#dcfce7', '#166534'),
('On-Hold', '#f1f5f9', '#475569'),
('Cancelled', '#fee2e2', '#991b1b')
ON DUPLICATE KEY UPDATE name=VALUES(name);

-- 3. Update Project Table to use Status FK
-- First, add the column if it doesn't exist
ALTER TABLE project_tb ADD COLUMN IF NOT EXISTS status_id INT;

-- Update existing records to map string status to ID
UPDATE project_tb p 
JOIN project_status_tb s ON LOWER(p.status) = LOWER(s.name)
SET p.status_id = s.id;

-- Fallback for 'ongoing' which might be different in existing data
UPDATE project_tb SET status_id = (SELECT id FROM project_status_tb WHERE name='Ongoing') WHERE status_id IS NULL;

-- 4. Create Notification Table (if not exists)
CREATE TABLE IF NOT EXISTS notification_tb (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    message TEXT,
    is_read BOOLEAN DEFAULT FALSE,
    user_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 5. Seed Initial Notifications
INSERT INTO notification_tb (title, message, is_read) VALUES 
('Welcome to CEMS', 'Your enterprise dashboard is ready for use.', 0),
('Site Inspection Due', 'Kathmandu View Tower inspection is scheduled for tomorrow.', 0),
('New Budget Approval', 'Lumbini Master Plan budget has been approved.', 0);