from __future__ import annotations

from sqlalchemy import inspect, text
from sqlalchemy.engine import Engine


LEGACY_COLUMNS: dict[str, dict[str, str]] = {
    "evaluations": {"school_year_id": "INTEGER NULL"},
    "period_plannings": {"school_year_id": "INTEGER NULL"},
}


def _upgrade_period_planning_unique(engine: Engine) -> None:
    inspector = inspect(engine)
    if "period_plannings" not in inspector.get_table_names():
        return
    desired = ["teacher_id", "section_id", "subject_id", "period", "school_year_id"]
    constraints = inspector.get_unique_constraints("period_plannings")
    current = next((item for item in constraints if item.get("name") == "uq_period_planning"), None)
    if current and current.get("column_names") == desired:
        return

    if engine.dialect.name in {"mysql", "mariadb"}:
        with engine.begin() as connection:
            if current:
                connection.execute(text("ALTER TABLE period_plannings DROP INDEX uq_period_planning"))
            connection.execute(text(
                "ALTER TABLE period_plannings ADD CONSTRAINT uq_period_planning "
                "UNIQUE (teacher_id, section_id, subject_id, period, school_year_id)"
            ))
        return

    if engine.dialect.name == "sqlite":
        # SQLite cannot drop a UNIQUE constraint in place. Rebuild only this table,
        # preserving every row and all public column names.
        raw = engine.raw_connection()
        try:
            cursor = raw.cursor()
            cursor.execute("PRAGMA foreign_keys=OFF")
            cursor.execute("DROP TABLE IF EXISTS period_plannings_v3")
            cursor.execute(
                """
                CREATE TABLE period_plannings_v3 (
                    id INTEGER NOT NULL PRIMARY KEY,
                    teacher_id INTEGER NOT NULL,
                    section_id INTEGER NOT NULL,
                    subject_id INTEGER NOT NULL,
                    school_year_id INTEGER NULL,
                    period VARCHAR(40) NOT NULL,
                    purpose TEXT NOT NULL DEFAULT '',
                    contents TEXT NOT NULL DEFAULT '',
                    competencies TEXT NOT NULL DEFAULT '',
                    strategies TEXT NOT NULL DEFAULT '',
                    evaluation_schedule TEXT NOT NULL DEFAULT '',
                    submitted_at DATETIME NOT NULL,
                    locked_at DATETIME NOT NULL,
                    updated_at DATETIME NOT NULL,
                    CONSTRAINT uq_period_planning UNIQUE
                        (teacher_id, section_id, subject_id, period, school_year_id),
                    FOREIGN KEY(teacher_id) REFERENCES users (id),
                    FOREIGN KEY(section_id) REFERENCES sections (id),
                    FOREIGN KEY(subject_id) REFERENCES subjects (id),
                    FOREIGN KEY(school_year_id) REFERENCES school_years (id)
                )
                """
            )
            cursor.execute(
                """
                INSERT INTO period_plannings_v3 (
                    id, teacher_id, section_id, subject_id, school_year_id, period,
                    purpose, contents, competencies, strategies, evaluation_schedule,
                    submitted_at, locked_at, updated_at
                )
                SELECT id, teacher_id, section_id, subject_id, school_year_id, period,
                    purpose, contents, competencies, strategies, evaluation_schedule,
                    submitted_at, locked_at, updated_at
                FROM period_plannings
                """
            )
            cursor.execute("DROP TABLE period_plannings")
            cursor.execute("ALTER TABLE period_plannings_v3 RENAME TO period_plannings")
            cursor.execute("CREATE INDEX ix_period_plannings_teacher_id ON period_plannings (teacher_id)")
            cursor.execute("CREATE INDEX ix_period_plannings_section_id ON period_plannings (section_id)")
            cursor.execute("CREATE INDEX ix_period_plannings_subject_id ON period_plannings (subject_id)")
            cursor.execute("CREATE INDEX ix_period_plannings_school_year_id ON period_plannings (school_year_id)")
            cursor.execute("CREATE INDEX ix_period_plannings_period ON period_plannings (period)")
            raw.commit()
            cursor.execute("PRAGMA foreign_keys=ON")
        except Exception:
            raw.rollback()
            raise
        finally:
            raw.close()


def ensure_legacy_columns(engine: Engine) -> None:
    """Apply additive, cPanel-safe upgrades without deleting historical data."""
    inspector = inspect(engine)
    existing_tables = set(inspector.get_table_names())
    with engine.begin() as connection:
        for table_name, columns in LEGACY_COLUMNS.items():
            if table_name not in existing_tables:
                continue
            existing_columns = {item["name"] for item in inspector.get_columns(table_name)}
            for column_name, definition in columns.items():
                if column_name not in existing_columns:
                    connection.execute(text(f"ALTER TABLE {table_name} ADD COLUMN {column_name} {definition}"))

    _upgrade_period_planning_unique(engine)

    inspector = inspect(engine)
    with engine.begin() as connection:
        for table_name, column_name, index_name in (
            ("evaluations", "school_year_id", "ix_evaluations_school_year_id"),
            ("period_plannings", "school_year_id", "ix_period_plannings_school_year_id"),
        ):
            if table_name not in inspector.get_table_names():
                continue
            indexes = {item["name"] for item in inspector.get_indexes(table_name)}
            if index_name in indexes:
                continue
            try:
                connection.execute(text(f"CREATE INDEX {index_name} ON {table_name} ({column_name})"))
            except Exception:
                pass