# -*- coding: utf-8 -*-
"""
Run the project_category + project_member migration against cems_db.
"""
import sys, io
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8', errors='replace')
import pymysql
import sys

DB_CONFIG = dict(
    host="localhost",
    port=3306,
    user="root",
    password="",
    db="cems_db",
    charset="utf8mb4",
)

STATEMENTS = [
    # ── 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. category_id column on project_tb (skip if already present) ───────
    # We use a stored procedure trick to handle IF NOT EXISTS on old MySQL
    """
    SET @col_exists = (
        SELECT COUNT(*) FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = 'cems_db'
          AND TABLE_NAME   = 'project_tb'
          AND COLUMN_NAME  = 'category_id'
    )
    """,

    # ── 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 civil engineering categories ───────────────────────
    """
    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)
    """,
]

def add_category_column(conn):
    """Add category_id to project_tb only if it does not already exist."""
    with conn.cursor() as cur:
        cur.execute("""
            SELECT COUNT(*) FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = %s AND TABLE_NAME = 'project_tb' AND COLUMN_NAME = 'category_id'
        """, ("cems_db",))
        (exists,) = cur.fetchone()

    if exists:
        print("  ✓ category_id column already exists — skipping ALTER TABLE")
        return

    with conn.cursor() as cur:
        cur.execute("""
            ALTER TABLE project_tb
                ADD COLUMN category_id INT,
                ADD CONSTRAINT fk_project_category
                    FOREIGN KEY (category_id) REFERENCES project_category_tb(id)
                    ON DELETE SET NULL
        """)
    print("  ✓ Added category_id column to project_tb")


def main():
    print("=== CEMS Database Migration ===\n")
    try:
        conn = pymysql.connect(**DB_CONFIG)
        print("[OK] Connected to MySQL (cems_db)\n")
    except Exception as e:
        print(f"[FAIL] Connection failed: {e}")
        sys.exit(1)

    step_names = [
        "Create project_category_tb",
        "Pre-check category_id column",
        "Create project_member_tb",
        "Seed 8 default categories",
    ]

    with conn:
        conn.autocommit(False)
        try:
            # Run statements 1, 3, 4  (skip statement 2 -- handled specially)
            for i, sql in enumerate(STATEMENTS):
                label = step_names[i]
                if i == 1:
                    add_category_column(conn)
                    continue
                with conn.cursor() as cur:
                    cur.execute(sql.strip())
                print(f"  [OK] Step {i+1}: {label}")

            conn.commit()
            print("\n[DONE] Migration completed successfully!")
            print("\nNew tables / changes:")
            print("  - project_category_tb  : created with 8 seeded categories")
            print("  - project_tb.category_id: FK column added")
            print("  - project_member_tb    : created")

        except Exception as e:
            conn.rollback()
            print(f"\n[FAIL] Migration failed: {e}")
            sys.exit(1)


if __name__ == "__main__":
    main()