import sys

sys.dont_write_bytecode = True

import argparse
import os
import sqlite3

from scanner_db import ScannerDB


TABLES = [
    'runs',
    'source_cycles',
    'target_scans',
    'target_queue',
    'scan_publication_outbox',
    'findings',
    'errors',
    'queue_snapshots',
    'config_snapshots',
    'package_repo_candidates',
    'keycheck_results',
    'keycheck_event_map',
    'finding_uid_map',
]


def sqlite_connect_ro(path):
    uri = 'file:' + os.path.abspath(path).replace('\\', '/') + '?mode=ro'
    conn = sqlite3.connect(uri, uri=True)
    conn.row_factory = sqlite3.Row
    return conn


def sqlite_columns(conn, table):
    return [row['name'] for row in conn.execute(f'PRAGMA table_info({table})').fetchall()]


def sqlite_count(conn, table):
    return int(conn.execute(f'SELECT COUNT(*) AS count FROM {table}').fetchone()['count'])


def sqlite_row_estimate(conn, table, exact=False):
    if exact:
        return sqlite_count(conn, table)
    try:
        row = conn.execute('SELECT seq FROM sqlite_sequence WHERE name = ?', (table,)).fetchone()
        if row and row['seq'] is not None:
            return int(row['seq'])
    except sqlite3.Error:
        pass
    return None


def pg_reset_identity(db, table):
    db.conn.execute(f'''
        SELECT setval(
            pg_get_serial_sequence('{table}', 'id'),
            COALESCE((SELECT MAX(id) FROM {table}), 1),
            (SELECT MAX(id) IS NOT NULL FROM {table})
        )
    ''')


def postgres_safe_value(value):
    if isinstance(value, str) and '\x00' in value:
        return value.replace('\x00', '\\u0000')
    return value


def copy_table(source, target, table, batch_size, dry_run=False, exact_counts=False):
    columns = sqlite_columns(source, table)
    if not columns:
        print(f'{table}: missing or empty schema in SQLite, skipped', flush=True)
        return 0
    total = sqlite_row_estimate(source, table, exact=exact_counts)
    total_label = total if total is not None else 'unknown'
    print(f'{table}: source_rows={total_label}' + ('' if exact_counts else ' estimated'), flush=True)
    if dry_run or total == 0:
        return int(total or 0)

    column_sql = ', '.join(columns)
    placeholders = ', '.join('?' for _ in columns)
    conflict_sql = ' ON CONFLICT (id) DO NOTHING' if 'id' in columns else ''
    insert_sql = f'INSERT INTO {table} ({column_sql}) VALUES ({placeholders}){conflict_sql}'

    copied = 0
    cursor = source.execute(f'SELECT {column_sql} FROM {table} ORDER BY id' if 'id' in columns else f'SELECT {column_sql} FROM {table}')
    while True:
        rows = cursor.fetchmany(batch_size)
        if not rows:
            break
        values = [[postgres_safe_value(row[column]) for column in columns] for row in rows]
        if target.conn.is_postgres:
            with target.conn._conn.cursor() as pg_cursor:
                with pg_cursor.copy(f'COPY {table} ({column_sql}) FROM STDIN') as copy:
                    for value_row in values:
                        copy.write_row(value_row)
        else:
            for value_row in values:
                target.conn.execute(insert_sql, value_row)
        copied += len(rows)
        target.conn.commit()
        print(f'{table}: copied={copied}/{total_label}', flush=True)
    if 'id' in columns:
        pg_reset_identity(target, table)
    target.conn.commit()
    return copied


def truncate_target(db):
    table_sql = ', '.join(TABLES)
    db.conn.execute(f'TRUNCATE TABLE {table_sql} RESTART IDENTITY CASCADE')
    db.conn.commit()


def parse_args():
    parser = argparse.ArgumentParser(description='Offline migrate scanner observability SQLite DB to PostgreSQL.')
    parser.add_argument('--sqlite', required=True, help='Path to scanner_active.db or archived SQLite DB')
    parser.add_argument('--db-url', default=os.getenv('SCANNER_DB_URL') or os.getenv('DATABASE_URL'), help='PostgreSQL DSN')
    parser.add_argument('--batch-size', type=int, default=1000)
    parser.add_argument('--exact-counts', action='store_true', help='Use exact COUNT(*) per table; slow on multi-GB SQLite files')
    parser.add_argument('--truncate', action='store_true', help='Delete existing Postgres observability rows before import')
    parser.add_argument('--dry-run', action='store_true')
    return parser.parse_args()


def main():
    raise SystemExit(
        'migrate_observability_db.py is retired for PostgreSQL. Use migrate_runtime_safety.py '
        '--apply --sources-stopped with the bound cluster; perform legacy data import only with a reviewed, cluster-bound tool.'
    )


if __name__ == '__main__':
    sys.exit(main())