#!/usr/bin/env python3
"""
SQLite to PostgreSQL Migration Script

Reads candle data from a SQLite database and writes it to PostgreSQL.
This is a one-time migration tool used to transfer existing historical
data from the old SQLite database to the new PostgreSQL database.

Usage:
    python migrate_sqlite_to_pg.py --sqlite-path _data/market_data.db --log-level normal

The script:
1. Connects to both SQLite (source) and PostgreSQL (destination)
2. Enumerates all candle tables (skipping legacy tables like market_cap)
3. For each table, reads data from SQLite and upserts to PostgreSQL
4. Handles table name sanitization (colons → underscores)
"""

import argparse
import logging
import os
import sqlite3
import sys

import pandas as pd

from logging_utils import setup_logging
from db import get_connection, sanitize_table_name, upsert_candles, create_candle_table

TIMEFRAMES = [
    '1m', '3m', '5m', '15m', '30m', '37m', '148m',
    '1h', '2h', '4h', '8h', '12h', '1d', '3d', '1w', '1month'
]


def get_candle_tables(sqlite_conn):
    """Get all candle table names from SQLite (excluding legacy tables)."""
    cursor = sqlite_conn.cursor()
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    all_tables = [row[0] for row in cursor.fetchall()]

    candle_tables = []
    for table in all_tables:
        for tf in TIMEFRAMES:
            if table.endswith(f'_{tf}'):
                candle_tables.append(table)
                break

    return candle_tables


def parse_table_name(table_name):
    """Parse a table name into (coin, timeframe)."""
    for tf in TIMEFRAMES:
        suffix = f'_{tf}'
        if table_name.endswith(suffix):
            coin = table_name[:-len(suffix)]
            return coin, tf
    return table_name, '1m'


def main():
    parser = argparse.ArgumentParser(description="Migrate data from SQLite to PostgreSQL.")
    parser.add_argument("--sqlite-path", default="_data/market_data.db",
                        help="Path to the SQLite database file.")
    parser.add_argument("--log-level", default="normal", choices=['off', 'normal', 'debug'])
    args = parser.parse_args()

    setup_logging(args.log_level, 'Migrator')

    if not os.path.exists(args.sqlite_path):
        logging.error(f"SQLite database not found at '{args.sqlite_path}'")
        sys.exit(1)

    sqlite_conn = sqlite3.connect(args.sqlite_path)
    pg_conn = get_connection()

    tables = get_candle_tables(sqlite_conn)
    logging.info(f"Found {len(tables)} candle tables to migrate")

    for table_name in tables:
        coin, timeframe = parse_table_name(table_name)
        pg_table = sanitize_table_name(coin, timeframe)

        logging.info(f"Migrating {table_name} -> {pg_table}")

        df = pd.read_sql(f'SELECT * FROM "{table_name}"', sqlite_conn)
        if df.empty:
            logging.warning(f"Table {table_name} is empty, skipping")
            continue

        create_candle_table(pg_conn, pg_table)

        records = list(df.itertuples(index=False, name=None))
        upsert_candles(pg_conn, pg_table, records)

        logging.info(f"Migrated {len(records)} rows to {pg_table}")

    sqlite_conn.close()
    pg_conn.close()
    logging.info("Migration complete!")


if __name__ == "__main__":
    main()