#!/usr/bin/env python3
"""Migra os dados do SQLite local para o MySQL.

Uso:
  1) Edite MYSQL_URL abaixo (ou exporte DATABASE_URL) com as credenciais do MySQL do cPanel.
  2) Rode:  python3 migrate_to_mysql.py

Requisitos: pymysql e cryptography instalados (pip install pymysql cryptography).
"""
import os
import sys
from datetime import datetime, date
from sqlalchemy import create_engine, text, inspect as sa_inspect, MetaData
from sqlalchemy.engine import make_url

SQLITE_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'instance', 'troco_play.db')
MYSQL_URL = os.environ.get(
    'DATABASE_URL',
    'mysql+pymysql://SEU_USUARIO:SUA_SENHA@localhost/SEU_BANCO?charset=utf8mb4'
)

IGNORE_TABLES = {'alembic_version'}

DATE_COLUMNS = {
    # (tabela, coluna)
    ('payment', 'expires_at'), ('payment', 'paid_at'), ('payment', 'created_at'),
    ('user', 'paid_until'), ('user', 'last_free_reset'), ('user', 'created_at'),
    ('session_token', 'created_at'), ('session_token', 'expires_at'),
    ('active_session', 'started_at'), ('active_session', 'last_heartbeat'),
    ('user_streaming', 'started_at'), ('user_streaming', 'last_heartbeat'),
    ('plan_history', 'started_at'), ('plan_history', 'expires_at'), ('plan_history', 'created_at'),
    ('audit_log', 'created_at'), ('notification', 'created_at'),
    ('support_ticket', 'created_at'), ('support_ticket', 'updated_at'),
    ('support_message', 'created_at'), ('watch_log', 'started_at'), ('watch_log', 'last_seen'),
    ('report', 'created_at'), ('report', 'resolved_at'), ('canal', 'data_added'),
    ('suggestion', 'created_at'), ('channel_health', 'last_checked'),
    ('channel_health', 'offline_since'), ('epg_program', 'start_time'), ('epg_program', 'end_time'),
    ('term', 'updated_at'), ('terms_acceptance', 'accepted_at'),
    ('cookie_consent', 'consented_at'), ('affiliate_config', 'created_at'),
    ('affiliate_config', 'updated_at'), ('affiliate_wallet', 'updated_at'),
    ('commission', 'created_at'), ('payout_request', 'created_at'),
    ('sport_event', 'created_at'), ('sport_event', 'event_date'),
    ('referral', 'created_at'), ('plan', 'created_at'), ('favorite', 'created_at'),
}


def is_date_col(table, col):
    return (table, col) in DATE_COLUMNS


def clean_value(table, col, value):
    if value is None:
        return None
    if isinstance(value, (datetime, date)):
        if isinstance(value, date) and not isinstance(value, datetime):
            return value.strftime('%Y-%m-%d')
        return value.strftime('%Y-%m-%d %H:%M:%S')
    if isinstance(value, str):
        s = value.strip()
        if is_date_col(table, col):
            if s == '':
                return None
            # Tenta converter texto de data vindo do SQLite (YYYY-MM-DD HH:MM:SS)
            for fmt in ('%Y-%m-%d %H:%M:%S', '%Y-%m-%d %H:%M', '%Y-%m-%d'):
                try:
                    return datetime.strptime(s[:19], fmt).strftime('%Y-%m-%d %H:%M:%S')
                except ValueError:
                    continue
            return None
        return value
    if isinstance(value, bool):
        return 1 if value else 0
    if isinstance(value, (int, float)):
        return value
    return value


def main():
    sqlite_uri = f'sqlite:///{SQLITE_PATH}'
    src_engine = create_engine(sqlite_uri)
    dst_engine = create_engine(MYSQL_URL)

    # Valida credenciais
    u = make_url(MYSQL_URL)
    if 'SEU_USUARIO' in u.username or 'SEU_BANCO' in u.database:
        print('ERRO: edite MYSQL_URL (ou DATABASE_URL) com as credenciais reais do MySQL.')
        sys.exit(1)

    meta = MetaData()
    src = sa_inspect(src_engine)
    dst = sa_inspect(dst_engine)

    src_tables = [t for t in src.get_table_names() if t not in IGNORE_TABLES]
    dst_tables = set(dst.get_table_names())

    for table in src_tables:
        if table not in dst_tables:
            print(f'[skip] {table}: tabela nao existe no MySQL')
            continue

        src_cols = [c['name'] for c in src.get_columns(table)]
        dst_cols = [c['name'] for c in dst.get_columns(table)]
        cols = [c for c in src_cols if c in dst_cols]

        rows = src_engine.execute(text(f'SELECT {", ".join(cols)} FROM "{table}"')).fetchall()
        if not rows:
            print(f'[ok] {table}: 0 linhas')
            continue

        # Desliga FK checks para preservar IDs
        with dst_engine.begin() as conn:
            conn.execute(text('SET FOREIGN_KEY_CHECKS=0'))

        placeholders = ', '.join([':' + c for c in cols])
        insert_sql = f'INSERT INTO {table} ({", ".join(cols)}) VALUES ({placeholders})'
        inserted = 0
        batch = []
        try:
            with dst_engine.begin() as conn:
                for i, row in enumerate(rows, 1):
                    data = {c: clean_value(table, c, row[idx]) for idx, c in enumerate(cols)}
                    batch.append(data)
                    if len(batch) >= 500:
                        conn.execute(text(insert_sql), batch)
                        inserted += len(batch)
                        batch = []
                        print(f'  {table}: {inserted}/{len(rows)}')
                if batch:
                    conn.execute(text(insert_sql), batch)
                    inserted += len(batch)
            with dst_engine.begin() as conn:
                conn.execute(text('SET FOREIGN_KEY_CHECKS=1'))
            print(f'[ok] {table}: {inserted} linhas migradas')
        except Exception as e:
            with dst_engine.begin() as conn:
                conn.execute(text('SET FOREIGN_KEY_CHECKS=1'))
            print(f'[ERRO] {table}: {e}')
            sys.exit(1)

    print('\nMigracao concluida!')


if __name__ == '__main__':
    main()