#!/usr/bin/env python3
"""Dump analytics DB tables."""
import subprocess, base64, json, csv, os

HOST = "root@46.101.74.150"
KEY = "L3/ssh_keys/jenkins-host-id_rsa.pem"
CONTAINER = "yajny-api"
DUMP = "db_dump/analytics"

def run_tinker(queries, timeout=300):
    code = "\n".join(queries)
    b64 = base64.b64encode(code.encode()).decode()
    cmd = ["ssh", "-i", KEY, "-o", "BatchMode=yes", "-o", "StrictHostKeyChecking=accept-new",
           HOST, f'echo {b64} | base64 -d | docker exec -i {CONTAINER} php /var/www/html/artisan tinker 2>&1']
    r = subprocess.run(cmd, capture_output=True, text=True, timeout=timeout)
    return r.stdout

tables = ["analytics_events", "analytics_sessions", "applications", "logs", "migrations", "platforms"]

for table in tables:
    csv_path = f"{DUMP}/{table}.csv"
    if os.path.isfile(csv_path) and os.path.getsize(csv_path) > 0:
        print(f"{table}: SKIP (exists)")
        continue
    
    # Get schema
    queries = [
        f"$c = DB::select('DESCRIBE analytics.{table}'); foreach($c as $col) {{ echo $col->Field.'|'.$col->Type.'|'.$col->Null.'|'.$col->Key.'|'.$col->Default.'|'.$col->Extra.'\\n'; }}",
        'exit;',
    ]
    schema_raw = run_tinker(queries, timeout=60)
    
    # Parse columns
    columns = []
    for line in schema_raw.split("\n"):
        line = line.strip()
        if "|" in line and not line.startswith(">") and "Field" not in line:
            parts = line.split("|")
            if len(parts) >= 2 and parts[0].replace("_","").isalnum():
                columns.append(parts[0])
    
    if not columns:
        print(f"{table}: NO SCHEMA")
        continue
    
    # Save schema
    with open(f"{DUMP}/{table}_schema.txt", "w") as f:
        f.write(schema_raw)
    
    # Determine PK for keyset pagination
    id_col = "id" if "id" in columns else columns[0]
    
    # Dump with keyset pagination
    total_rows = 0
    last_id = None
    write_header = True
    
    while True:
        if last_id is not None:
            where_clause = f"WHERE `{id_col}` > {last_id}"
        else:
            where_clause = ""
        col_list = ",".join(f"`{c}`" for c in columns)
        queries = [
            f"$r = DB::select('SELECT {col_list} FROM analytics.{table} {where_clause} ORDER BY `{id_col}` LIMIT 500');",
            'foreach($r as $row) { echo json_encode($row)."\\n"; }',
        ]
        raw = run_tinker(queries, timeout=300)
        
        page_rows = 0
        last_line = None
        with open(csv_path, "a", newline="") as f:
            writer = csv.writer(f)
            if write_header:
                writer.writerow(columns)
                write_header = False
            for line in raw.split("\n"):
                line = line.strip()
                if len(line) < 5 or not line.startswith("{"):
                    continue
                if line.startswith("{#"):
                    continue
                try:
                    obj = json.loads(line)
                    if isinstance(obj, dict):
                        last_line = obj
                        row = []
                        for c in columns:
                            val = obj.get(c, "")
                            if isinstance(val, (dict, list)):
                                val = json.dumps(val, ensure_ascii=False)
                            row.append(str(val) if val is not None else "")
                        writer.writerow(row)
                        page_rows += 1
                except:
                    pass
        
        total_rows += page_rows
        if page_rows < 500:
            break
        
        # Update last_id
        if last_line and id_col in last_line:
            val = last_line[id_col]
            if isinstance(val, (int, str)) and str(val).isdigit():
                last_id = int(val)
            else:
                break
        else:
            break
        
        if total_rows % 5000 == 0:
            print(f"  {table}: {total_rows} rows...")
    
    print(f"{table}: {total_rows} rows dumped")