#!/usr/bin/env python3
import os
import re
from collections import defaultdict

import mysql.connector


def connect_mysql():
    return mysql.connector.connect(
        host=os.getenv("MYSQL_HOST", "127.0.0.1"),
        port=int(os.getenv("MYSQL_PORT", "3306")),
        user=os.getenv("MYSQL_USER", "app_user"),
        password=os.getenv("MYSQL_PASSWORD", "apppass"),
        database=os.getenv("MYSQL_DATABASE", "app_db"),
    )


def get_tables(cursor) -> dict[str, list[str]]:
    """Get all tables and their columns."""
    cursor.execute(
        "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE()"
    )
    tables = {row[0]: [] for row in cursor.fetchall()}

    for table_name in tables.keys():
        cursor.execute(
            f"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s",
            (table_name,),
        )
        tables[table_name] = [row[0] for row in cursor.fetchall()]

    return tables


def infer_fk_relationships(tables: dict[str, list[str]]) -> list[tuple[str, str, str, str]]:
    """
    Infer FK relationships from column naming patterns and explicit mapping.
    Returns list of (source_table, source_column, target_table, target_column)
    """
    explicit_fk_map = {
        "id_salarie": "employes",
        "id_contrat": "contrats",
        "id_avenant": "avenants_contrat",
        "id_classification": "classifications",
        "id_administrateurs": "administrateurs",
    }

    relationships = []

    for source_table, columns in tables.items():
        for column in columns:
            col_lower = column.lower()

            if col_lower == "id":
                continue

            target_table = None

            if col_lower in explicit_fk_map:
                target_table = explicit_fk_map[col_lower]
            else:
                pattern_matches = [
                    (r"^id_(.+)$", lambda m: m.group(1)),
                    (r"^(.+)_id$", lambda m: m.group(1)),
                ]

                for pattern, extractor in pattern_matches:
                    match = re.match(pattern, col_lower)
                    if match:
                        potential_target = extractor(match)
                        potential_target_plural = potential_target + "s"

                        if potential_target in tables:
                            target_table = potential_target
                            break
                        elif potential_target_plural in tables:
                            target_table = potential_target_plural
                            break

            if target_table and target_table in tables:
                target_column = "id"
                if target_column in tables.get(target_table, []):
                    relationships.append((source_table, column, target_table, target_column))

    return relationships


def validate_fk_reference(cursor, source_table: str, source_col: str, target_table: str, target_col: str) -> tuple[int, int]:
    """
    Check how many source values exist in target table.
    Returns (total_non_null, valid_refs, orphan_count)
    """
    cursor.execute(f"SELECT COUNT(*) FROM {source_table} WHERE {source_col} IS NOT NULL")
    total_non_null = cursor.fetchone()[0]

    cursor.execute(
        f"SELECT COUNT(DISTINCT t1.{source_col}) FROM {source_table} t1 "
        f"LEFT JOIN {target_table} t2 ON t1.{source_col} = t2.{target_col} "
        f"WHERE t1.{source_col} IS NOT NULL AND t2.{target_col} IS NULL"
    )
    orphan_count = cursor.fetchone()[0]

    valid_refs = total_non_null - orphan_count
    return total_non_null, valid_refs, orphan_count


def main():
    connection = connect_mysql()
    cursor = connection.cursor()

    print("=" * 80)
    print("Foreign Key Relationship Analysis")
    print("=" * 80)

    tables = get_tables(cursor)
    print(f"\n📊 Found {len(tables)} tables\n")

    relationships = infer_fk_relationships(tables)
    print(f"🔍 Inferred {len(relationships)} potential FK relationships:\n")

    valid_fks = []
    problem_fks = []

    unique_columns = set()

    for source_table, source_col, target_table, target_col in relationships:
        total, valid, orphans = validate_fk_reference(cursor, source_table, source_col, target_table, target_col)

        status = "✓" if orphans == 0 else "⚠"
        print(f"{status} {source_table}.{source_col} → {target_table}.{target_col}")
        print(f"   Non-null values: {total}, Valid refs: {valid}, Orphans: {orphans}")

        if orphans == 0:
            valid_fks.append((source_table, source_col, target_table, target_col))
        else:
            problem_fks.append((source_table, source_col, target_table, target_col, orphans))
            unique_columns.add((target_table, target_col))

    print(f"\n{'=' * 80}")
    print(f"Summary: {len(valid_fks)} FKs ready to add, {len(problem_fks)} with orphan values\n")

    if unique_columns:
        print("📋 Step 1: Create UNIQUE indices on source ID columns:\n")
        for table, column in sorted(unique_columns):
            index_name = f"uk_{table}_{column}"
            print(f"ALTER TABLE `{table}` ADD UNIQUE INDEX `{index_name}` (`{column}`);")

    if valid_fks:
        print("\n✓ Step 2: Ready to add FKs (no orphans):\n")
        for source_table, source_col, target_table, target_col in valid_fks:
            constraint_name = f"fk_{source_table}_{source_col}"
            alter_sql = (
                f"ALTER TABLE `{source_table}` ADD CONSTRAINT `{constraint_name}` "
                f"FOREIGN KEY (`{source_col}`) REFERENCES `{target_table}`(`{target_col}`)"
            )
            print(alter_sql + ";")

    if problem_fks:
        print(f"\n⚠ Step 3: Problem FKs (use source columns instead of PK):\n")
        for source_table, source_col, target_table, target_col, orphan_count in problem_fks:
            constraint_name = f"fk_{source_table}_{source_col}"
            alter_sql = (
                f"-- {orphan_count} orphans detected\n"
                f"-- ALTER TABLE `{source_table}` ADD CONSTRAINT `{constraint_name}` "
                f"FOREIGN KEY (`{source_col}`) REFERENCES `{target_table}`(`id`);"
            )
            print(alter_sql)
            print()

    cursor.close()
    connection.close()


if __name__ == "__main__":
    main()