#!/usr/bin/env python3
"""
Exporta todas las tablas de iapsonora_grados a un CSV por tabla,
luego los empaqueta en un solo ZIP en la carpeta raíz del proyecto.

Uso (desde la raíz del proyecto en el servidor):
    python3 exportar_bd.py
"""

import csv
import os
import sys
import zipfile
from datetime import datetime

import pymysql

DB = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "iapsonora_grados",
    "password": "B]si}_~PcJ~7urF",
    "db": "iapsonora_grados",
    "charset": "utf8mb4",
}

SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
TIMESTAMP = datetime.now().strftime("%Y%m%d_%H%M%S")
ZIP_PATH = os.path.join(SCRIPT_DIR, f"export_bd_{TIMESTAMP}.zip")


def get_tables(cursor):
    cursor.execute("SHOW TABLES")
    return [row[0] for row in cursor.fetchall()]


def export_table(cursor, table_name, csv_path):
    cursor.execute(f"SELECT * FROM `{table_name}`")
    rows = cursor.fetchall()
    columns = [d[0] for d in cursor.description]
    with open(csv_path, "w", newline="", encoding="utf-8-sig") as f:
        writer = csv.writer(f)
        writer.writerow(columns)
        writer.writerows(rows)
    return len(rows)


def main():
    print(f"Conectando a {DB['host']}:{DB['port']} / {DB['db']} …")
    try:
        conn = pymysql.connect(**DB)
    except Exception as e:
        print(f"ERROR al conectar: {e}")
        sys.exit(1)

    cursor = conn.cursor()
    tables = get_tables(cursor)
    print(f"Tablas encontradas: {len(tables)}")

    tmp_dir = os.path.join(SCRIPT_DIR, f"_tmp_export_{TIMESTAMP}")
    os.makedirs(tmp_dir, exist_ok=True)

    try:
        with zipfile.ZipFile(ZIP_PATH, "w", zipfile.ZIP_DEFLATED) as zf:
            for table in tables:
                csv_path = os.path.join(tmp_dir, f"{table}.csv")
                try:
                    n = export_table(cursor, table, csv_path)
                    zf.write(csv_path, arcname=f"{table}.csv")
                    print(f"  ✓ {table}: {n} filas")
                except Exception as e:
                    print(f"  ✗ {table}: {e}")
    finally:
        cursor.close()
        conn.close()
        # Limpiar archivos temporales
        import shutil
        shutil.rmtree(tmp_dir, ignore_errors=True)

    print(f"\nArchivo generado: {ZIP_PATH}")


if __name__ == "__main__":
    main()