"""
Add accounting_dimension_values FKs on credit_notes (cost_center, project, department).

Safe to run multiple times. SQLite: adds department_id column; FK enforcement is best-effort.
PostgreSQL: adds FK constraints when missing.
"""
import os
import sys

sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from sqlalchemy import inspect, text
from app.core.database import engine


def column_exists(inspector, table: str, column: str) -> bool:
    return column in {c["name"] for c in inspector.get_columns(table)}


def run_migration() -> None:
    inspector = inspect(engine)
    dialect = engine.dialect.name
    print(f"Running credit_notes dimension FK migration ({dialect})...")

    with engine.begin() as conn:
        if not column_exists(inspector, "credit_notes", "department_id"):
            conn.execute(
                text(
                    "ALTER TABLE credit_notes ADD COLUMN department_id VARCHAR NULL"
                )
            )
            print("  + department_id column")

        for col in ("cost_center_id", "project_id", "department_id"):
            try:
                conn.execute(
                    text(
                        f"CREATE INDEX IF NOT EXISTS idx_credit_notes_{col} "
                        f"ON credit_notes({col})"
                    )
                )
            except Exception as exc:
                print(f"  index {col}: {exc}")

        if dialect == "postgresql":
            fk_specs = [
                (
                    "fk_credit_notes_cost_center",
                    "cost_center_id",
                ),
                (
                    "fk_credit_notes_project",
                    "project_id",
                ),
                (
                    "fk_credit_notes_department",
                    "department_id",
                ),
            ]
            existing = {
                fk["name"]
                for fk in inspector.get_foreign_keys("credit_notes")
            }
            for fk_name, col in fk_specs:
                if fk_name in existing:
                    continue
                try:
                    conn.execute(
                        text(
                            f"""
                            ALTER TABLE credit_notes
                            ADD CONSTRAINT {fk_name}
                            FOREIGN KEY ({col})
                            REFERENCES accounting_dimension_values(id)
                            ON DELETE SET NULL
                            """
                        )
                    )
                    print(f"  + {fk_name}")
                except Exception as exc:
                    print(f"  skip {fk_name}: {exc}")

    print("Credit note dimension migration complete.")


if __name__ == "__main__":
    run_migration()
