"""Migration: Add coin_source_mappings table and new columns to coins table.

Run: python3 scripts/migrate_coin_sources.py
"""
import sys
import os
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
from app.models.coin import Coin
from app.models.coin_source import CoinSourceMapping


def migrate():
    app = create_app()
    with app.app_context():
        from app.helpers.market_db import switch_market_schema
        switch_market_schema('crypto')

        print('=== Migration: Coin Source Mappings ===\n')

        # 1. Add new columns to coins table (if not exist)
        print('1. Adding new columns to coins table...')
        new_columns = {
            'icon_thumb_url': 'VARCHAR(500)',
            'website': 'VARCHAR(500)',
            'blockchain': 'VARCHAR(100)',
            'contract_address': 'VARCHAR(200)',
            'coingecko_score': 'DECIMAL(5,2)',
        }

        for col_name, col_type in new_columns.items():
            try:
                db.session.execute(db.text(
                    f'ALTER TABLE coins ADD COLUMN {col_name} {col_type}'
                ))
                print(f'   + Added coins.{col_name}')
            except Exception as e:
                if 'Duplicate column' in str(e):
                    print(f'   ~ coins.{col_name} already exists')
                else:
                    print(f'   ! Error adding {col_name}: {e}')
            db.session.commit()

        # 2. Create coin_source_mappings table
        print('\n2. Creating coin_source_mappings table...')
        try:
            CoinSourceMapping.__table__.create(db.engine, checkfirst=True)
            print('   + Table coin_source_mappings created (or already exists)')
        except Exception as e:
            print(f'   ! Error: {e}')

        # 3. Auto-populate CoinGecko mappings for existing coins
        print('\n3. Populating CoinGecko mappings for existing coins...')
        coins = Coin.query.all()
        count = 0
        for coin in coins:
            existing = CoinSourceMapping.query.filter_by(
                coin_id=coin.id, source='coingecko'
            ).first()
            if not existing:
                mapping = CoinSourceMapping(
                    coin_id=coin.id,
                    source='coingecko',
                    source_coin_id=coin.id,
                    source_url=f'https://www.coingecko.com/id/coins/{coin.id}',
                    is_available=True,
                )
                db.session.add(mapping)
                count += 1

        db.session.commit()
        print(f'   + {count} CoinGecko mappings added ({len(coins) - count} already existed)')

        # 4. Check Indodax availability for existing coins
        print('\n4. Checking Indodax availability...')
        try:
            from app.services.data_sync.indodax import IndodaxProvider
            indodax = IndodaxProvider()
            indodax_count = 0

            for coin in coins:
                existing = CoinSourceMapping.query.filter_by(
                    coin_id=coin.id, source='indodax'
                ).first()
                if existing:
                    continue

                pair_info = indodax._find_pair_by_coin_id(coin.id)
                if pair_info:
                    symbol_lower = pair_info['traded_currency'].lower()
                    mapping = CoinSourceMapping(
                        coin_id=coin.id,
                        source='indodax',
                        source_coin_id=pair_info.get('ticker_id') or pair_info.get('id', coin.id),
                        source_pair=f"{pair_info['traded_currency']}/IDR",
                        source_url=f'https://indodax.com/market/{symbol_lower}idr',
                        is_available=True,
                    )
                    db.session.add(mapping)
                    indodax_count += 1

            db.session.commit()
            print(f'   + {indodax_count} Indodax mappings added')
        except Exception as e:
            print(f'   ! Indodax check failed (offline?): {e}')

        print('\n=== Migration complete! ===')
        print(f'Total coins: {len(coins)}')
        total_mappings = CoinSourceMapping.query.count()
        print(f'Total source mappings: {total_mappings}')


if __name__ == '__main__':
    migrate()