"""Migrate coin IDs to namespaced format.

COIN.bitcoin, IDX.BBCA — eliminates case-insensitive PK collisions.

Usage:
    1. Backup: mysqldump -u root koinkamu > backup_$(date +%Y%m%d).sql
    2. Run: python3 scripts/migrate_namespace_ids.py
    3. Validate: python3 scripts/migrate_namespace_ids.py --validate

This script is idempotent — running it twice won't double-prefix IDs.
"""
from __future__ import annotations

import json
import sys
import os
from datetime import datetime

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

from dotenv import load_dotenv
load_dotenv()

from app import create_app
from app.extensions import db

# All 12 FK tables that reference coins.id
FK_TABLES = [
    'coin_profiles',
    'ohlcv_data',
    'trading_signals',
    'market_tickers',
    'watchlist',
    'capital_allocations',
    'portfolio',
    'trade_history',
    'prediction_queues',
    'bullish_momentum_scores',
    'range_trading_scores',
    'coin_source_mappings',
]


def is_already_namespaced(coin_id: str) -> bool:
    """Check if a coin_id already has a valid namespace prefix."""
    from app.helpers.coin_id import is_namespaced
    return is_namespaced(coin_id)


def migrate():
    """Run the namespace migration."""
    app = create_app()
    with app.app_context():
        print('=' * 60)
        print('Koinkamu — Namespace ID Migration')
        print('=' * 60)

        # Check current state
        result = db.session.execute(db.text(
            "SELECT asset_type, COUNT(*) as cnt FROM coins GROUP BY asset_type"
        ))
        counts = {row[0]: row[1] for row in result}
        print(f"\nCurrent data:")
        for at, cnt in counts.items():
            print(f"  {at}: {cnt:,} coins")

        # Check if already migrated
        result = db.session.execute(db.text(
            "SELECT COUNT(*) FROM coins WHERE id LIKE 'COIN.%' OR id LIKE 'IDX.%'"
        ))
        already_namespaced = result.scalar()
        total = sum(counts.values())
        if already_namespaced == total and total > 0:
            print(f"\nAll {total:,} coins already have namespace prefixes. Nothing to do.")
            return

        if already_namespaced > 0:
            print(f"\nWARNING: {already_namespaced}/{total} coins already namespaced.")
            print("This is a partial migration. Will only migrate non-namespaced IDs.")

        # Count what needs migrating
        result = db.session.execute(db.text(
            "SELECT COUNT(*) FROM coins WHERE asset_type='crypto' AND id NOT LIKE 'COIN.%'"
        ))
        crypto_to_migrate = result.scalar()

        result = db.session.execute(db.text(
            "SELECT COUNT(*) FROM coins WHERE asset_type='stock' AND id NOT LIKE 'IDX.%'"
        ))
        stock_to_migrate = result.scalar()

        print(f"\nTo migrate:")
        print(f"  Crypto: {crypto_to_migrate:,} coins → COIN.xxx")
        print(f"  Stock:  {stock_to_migrate:,} coins → IDX.xxx")
        print(f"  FK tables: {len(FK_TABLES)}")

        if crypto_to_migrate == 0 and stock_to_migrate == 0:
            print("\nNothing to migrate!")
            return

        # Save rollback mapping
        rollback_file = f'scripts/namespace_rollback_{datetime.now().strftime("%Y%m%d_%H%M%S")}.json'

        print(f"\nStarting migration...")
        start = datetime.now()

        # Disable FK checks for bulk update
        db.session.execute(db.text("SET FOREIGN_KEY_CHECKS = 0"))

        migrated = {'crypto': 0, 'stock': 0}
        rollback_map = {}

        # --- Migrate crypto coins (COIN. prefix) ---
        if crypto_to_migrate > 0:
            print(f"\n[1/2] Migrating {crypto_to_migrate:,} crypto coins...")

            # Get IDs to migrate
            result = db.session.execute(db.text(
                "SELECT id FROM coins WHERE asset_type='crypto' AND id NOT LIKE 'COIN.%%'"
            ))
            crypto_ids = [row[0] for row in result]

            # Update FK tables first
            for table in FK_TABLES:
                try:
                    result = db.session.execute(db.text(f"""
                        UPDATE {table} SET coin_id = CONCAT('COIN.', coin_id)
                        WHERE coin_id IN (
                            SELECT id FROM coins WHERE asset_type='crypto' AND id NOT LIKE 'COIN.%%'
                        )
                    """))
                    if result.rowcount > 0:
                        print(f"  {table}: {result.rowcount:,} rows updated")
                except Exception as e:
                    print(f"  {table}: SKIPPED ({e})")

            # Update PK table
            result = db.session.execute(db.text(
                "UPDATE coins SET id = CONCAT('COIN.', id) "
                "WHERE asset_type='crypto' AND id NOT LIKE 'COIN.%%'"
            ))
            print(f"  coins (PK): {result.rowcount:,} rows updated")
            migrated['crypto'] = result.rowcount

            for old_id in crypto_ids:
                rollback_map[f'COIN.{old_id}'] = old_id

        # --- Migrate stock coins (IDX. prefix) ---
        if stock_to_migrate > 0:
            print(f"\n[2/2] Migrating {stock_to_migrate:,} stock coins...")

            # Get IDs to migrate
            result = db.session.execute(db.text(
                "SELECT id FROM coins WHERE asset_type='stock' AND id NOT LIKE 'IDX.%%'"
            ))
            stock_ids = [row[0] for row in result]

            # Update FK tables first
            for table in FK_TABLES:
                try:
                    result = db.session.execute(db.text(f"""
                        UPDATE {table} SET coin_id = CONCAT('IDX.', coin_id)
                        WHERE coin_id IN (
                            SELECT id FROM coins WHERE asset_type='stock' AND id NOT LIKE 'IDX.%%'
                        )
                    """))
                    if result.rowcount > 0:
                        print(f"  {table}: {result.rowcount:,} rows updated")
                except Exception as e:
                    print(f"  {table}: SKIPPED ({e})")

            # Update PK table
            result = db.session.execute(db.text(
                "UPDATE coins SET id = CONCAT('IDX.', id) "
                "WHERE asset_type='stock' AND id NOT LIKE 'IDX.%%'"
            ))
            print(f"  coins (PK): {result.rowcount:,} rows updated")
            migrated['stock'] = result.rowcount

            for old_id in stock_ids:
                rollback_map[f'IDX.{old_id}'] = old_id

        # Re-enable FK checks and commit
        db.session.execute(db.text("SET FOREIGN_KEY_CHECKS = 1"))
        db.session.commit()

        elapsed = (datetime.now() - start).total_seconds()

        # Save rollback mapping
        with open(rollback_file, 'w') as f:
            json.dump(rollback_map, f, indent=2)

        print(f"\n{'=' * 60}")
        print(f"Migration complete in {elapsed:.1f}s!")
        print(f"  Crypto: {migrated['crypto']:,} migrated")
        print(f"  Stock:  {migrated['stock']:,} migrated")
        print(f"  Rollback file: {rollback_file}")
        print(f"{'=' * 60}")


def validate():
    """Validate the migration."""
    app = create_app()
    with app.app_context():
        print('=' * 60)
        print('Koinkamu — Namespace Migration Validation')
        print('=' * 60)

        errors = []

        # 1. Check all coins have valid prefix
        result = db.session.execute(db.text(
            "SELECT COUNT(*) FROM coins WHERE id NOT LIKE 'COIN.%%' AND id NOT LIKE 'IDX.%%'"
        ))
        no_prefix = result.scalar()
        if no_prefix > 0:
            errors.append(f"{no_prefix} coins without namespace prefix!")
        else:
            print("[OK] All coins have namespace prefix")

        # 2. Check counts per prefix
        result = db.session.execute(db.text(
            "SELECT "
            "  CASE WHEN id LIKE 'COIN.%%' THEN 'COIN' "
            "       WHEN id LIKE 'IDX.%%' THEN 'IDX' "
            "       ELSE 'OTHER' END AS prefix, "
            "  COUNT(*) as cnt "
            "FROM coins GROUP BY prefix"
        ))
        for row in result:
            print(f"  {row[0]}: {row[1]:,} coins")

        # 3. Check for orphan FK records
        for table in FK_TABLES:
            try:
                result = db.session.execute(db.text(f"""
                    SELECT COUNT(*) FROM {table} t
                    LEFT JOIN coins c ON t.coin_id = c.id
                    WHERE c.id IS NULL
                """))
                orphans = result.scalar()
                if orphans > 0:
                    errors.append(f"{table}: {orphans} orphan records (coin_id not in coins)")
                else:
                    print(f"[OK] {table}: no orphans")
            except Exception as e:
                print(f"[??] {table}: could not check ({e})")

        # 4. Summary
        print(f"\n{'=' * 60}")
        if errors:
            print(f"VALIDATION FAILED — {len(errors)} issues:")
            for e in errors:
                print(f"  [!!] {e}")
        else:
            print("VALIDATION PASSED — all checks OK!")
        print(f"{'=' * 60}")


if __name__ == '__main__':
    if '--validate' in sys.argv:
        validate()
    else:
        migrate()