"""Migration: Add cycle frequency columns to range_trading_scores.

Run:  python3 scripts/migrate_cycle_frequency.py
"""
from __future__ import annotations

import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parent.parent))

from app import create_app
from app.extensions import db
from sqlalchemy import text


def run():
    app = create_app()
    with app.app_context():
        conn = db.engine.connect()
        cols = {
            'cycle_complete_count':     'INT DEFAULT NULL',
            'cycle_failed_count':       'INT DEFAULT NULL',
            'cycles_per_day':           'DECIMAL(8,4) DEFAULT NULL',
            'avg_cycle_duration_hours': 'DECIMAL(10,2) DEFAULT NULL',
            'cycle_success_rate_pct':   'DECIMAL(5,1) DEFAULT NULL',
            'cycle_confidence':         'VARCHAR(15) DEFAULT NULL',
            'adaptive_support':         'DECIMAL(20,8) DEFAULT NULL',
            'adaptive_resistance':      'DECIMAL(20,8) DEFAULT NULL',
            'adaptive_range_pct':       'DECIMAL(8,2) DEFAULT NULL',
            'adaptive_lookback':        'VARCHAR(10) DEFAULT NULL',
            'adaptive_est_profit_pct':  'DECIMAL(8,4) DEFAULT NULL',
            'cycle_scanned_at':         'DATETIME DEFAULT NULL',
        }
        for col_name, col_def in cols.items():
            try:
                conn.execute(text(
                    f'ALTER TABLE range_trading_scores '
                    f'ADD COLUMN {col_name} {col_def}'
                ))
                conn.commit()
                print(f'  ✅ Added column: {col_name}')
            except Exception as e:
                conn.rollback()
                if 'Duplicate column' in str(e) or 'already exists' in str(e):
                    print(f'  ⏭️  Column {col_name} already exists, skipping.')
                else:
                    print(f'  ❌ Error adding {col_name}: {e}')

        # Add indexes
        indexes = {
            'idx_range_cpd': 'cycles_per_day',
            'idx_range_success': 'cycle_success_rate_pct',
        }
        for idx_name, idx_col in indexes.items():
            try:
                conn.execute(text(
                    f'CREATE INDEX {idx_name} '
                    f'ON range_trading_scores ({idx_col})'
                ))
                conn.commit()
                print(f'  ✅ Added index: {idx_name}')
            except Exception as e:
                conn.rollback()
                if 'Duplicate' in str(e) or 'already exists' in str(e):
                    print(f'  ⏭️  Index {idx_name} already exists, skipping.')
                else:
                    print(f'  ❌ Error adding index {idx_name}: {e}')

        conn.close()
        print('\nMigration complete.')


if __name__ == '__main__':
    run()