-- ============================================================
-- CEMS Migration: Project Categories + Project Members
-- MySQL version — run against your cems_db database.
-- ============================================================

-- 1. project_category_tb
CREATE TABLE IF NOT EXISTS project_category_tb (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL UNIQUE,
    description     TEXT,
    color           VARCHAR(20),
    icon            VARCHAR(50),
    is_active       TINYINT(1) NOT NULL DEFAULT 1,
    created_by      INT,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_updated_by INT,
    last_updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- 2. Add category_id FK to project_tb (safe — only if column does not exist)
ALTER TABLE project_tb
    ADD COLUMN IF NOT EXISTS category_id INT,
    ADD CONSTRAINT fk_project_category
        FOREIGN KEY (category_id) REFERENCES project_category_tb(id)
        ON DELETE SET NULL;


-- 3. project_member_tb
CREATE TABLE IF NOT EXISTS project_member_tb (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    project_id       INT NOT NULL,
    employee_id      INT NOT NULL,
    role_in_project  VARCHAR(100),
    is_active        TINYINT(1) NOT NULL DEFAULT 1,
    created_by       INT,
    created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_updated_by  INT,
    last_updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_employee (project_id, employee_id),
    CONSTRAINT fk_member_project  FOREIGN KEY (project_id)  REFERENCES project_tb(id)   ON DELETE CASCADE,
    CONSTRAINT fk_member_employee FOREIGN KEY (employee_id) REFERENCES employee_tb(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- 4. Seed default project categories (civil engineering domain)
INSERT IGNORE INTO project_category_tb (name, description, color, icon, is_active) VALUES
    ('Road & Highway',     'Road construction, expansion, and maintenance projects',       '#F59E0B', '🛣️',  1),
    ('Bridge & Culvert',   'Bridge design and structural construction projects',            '#3B82F6', '🌉',  1),
    ('Building & Infra',   'Commercial and residential building construction',              '#10B981', '🏗️',  1),
    ('Water & Sanitation', 'Water supply, drainage, and sewage projects',                  '#06B6D4', '💧',  1),
    ('Irrigation',         'Irrigation canal and dam construction projects',                '#6366F1', '🌊',  1),
    ('Electrical Works',   'Electrical infrastructure and substation projects',             '#F97316', '⚡',  1),
    ('Survey & Design',    'Feasibility, survey, and engineering design projects',          '#8B5CF6', '📐',  1),
    ('Maintenance',        'Repair and maintenance of existing infrastructure',             '#64748B', '🔧',  1);