#!/usr/bin/env python3
"""Re-dump capped tables without max_rows limit.
Tables: balance_transactions (9.6M), sessions (192K - VARCHAR PK),
user_claims (1K), user_sales (2.1M), sale_items (4.2M).
Uses keyset pagination with correct JSON filter.
"""
import subprocess, base64, json, csv, os, sys

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

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

def dump_table(table, max_rows=10000000):
    csv_path = f"{DUMP}/{table}.csv"
    
    # Skip rep_ tables
    if table.startswith("rep_"):
        return "SKIP_VIEW"
    
    # Get schema
    queries = [
        f'$c = DB::select("DESCRIBE `{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)
    
    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:
        return "NO_SCHEMA"
    
    # Check PK type
    id_col = "id" if "id" in columns else columns[0]
    pk_type = "int"
    for line in schema_raw.split("\n"):
        if f"{id_col}|" in line and "varchar" in line:
            pk_type = "varchar"
            break
    
    if pk_type == "varchar":
        # Use OFFSET pagination for VARCHAR PK
        total_rows = 0
        page = 0
        write_header = True
        while True:
            offset = page * 500
            col_list = ",".join(f"`{c}`" for c in columns)
            queries = [
                f'$r = DB::select("SELECT {col_list} FROM `{table}` ORDER BY `{id_col}` LIMIT 500 OFFSET {offset}");',
                'foreach($r as $row) { echo json_encode($row)."\\n"; }',
            ]
            raw = run_tinker(queries, timeout=300)
            page_rows = 0
            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("{") or line.startswith("{#"):
                        continue
                    try:
                        obj = json.loads(line)
                        if isinstance(obj, dict):
                            row = [str(obj.get(c,"")) if obj.get(c) is not None else "" for c in columns]
                            writer.writerow(row)
                            page_rows += 1
                    except:
                        pass
            total_rows += page_rows
            if page_rows < 500:
                break
            page += 1
            if total_rows >= max_rows:
                break
            if total_rows % 5000 == 0:
                print(f"  {table}: {total_rows} rows (OFFSET)...")
        return total_rows
    
    # Keyset pagination for numeric PK
    # Remove old file first
    if os.path.isfile(csv_path):
        os.remove(csv_path)
    
    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 `{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("{") or 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
        
        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 >= max_rows:
            break
        
        if total_rows % 5000 == 0:
            print(f"  {table}: {total_rows} rows...")
    
    return total_rows

# Tables to re-dump (remove old capped versions)
TABLES = [
    "balance_transactions",  # 9.6M rows, was 50K
    "user_claims",           # 1K rows, was 704
    "user_sales",            # 2.1M rows, was 50K
    "sale_items",            # 4.2M rows, was 50K
    "processed_balance_transactions",  # 3.5M rows, was 50K
    "user_notifications",    # 61M rows, was 28K — SKIP (too large, 31GB)
    "tb_notification",       # 40M rows, was 50K — SKIP (too large, 8.6GB)
    "user_activities",       # 87M rows, was 50K — SKIP (too large, 25GB)
    "sessions",              # 192K rows, VARCHAR PK — use OFFSET
]

# Remove capped versions
for t in TABLES:
    csv_path = f"{DUMP}/{t}.csv"
    if os.path.isfile(csv_path):
        os.remove(csv_path)
        print(f"Removed {t}.csv (capped)")

for table in TABLES:
    print(f"\n=== {table} ===")
    result = dump_table(table, max_rows=10000000)
    print(f"  -> {result} rows")