#!/usr/bin/env python3
"""
Exports pos_db to a clean SQL file ready for cPanel phpMyAdmin import.
Avoids common import errors: no CREATE DATABASE, no USE, no DEFINER,
proper charset headers, chunked INSERTs for large tables.

Usage:
    python3 export_for_cpanel.py

Output:
    pos_db_cpanel_export.sql  (in the same folder)
"""

import mysql.connector
import os
import sys
from datetime import datetime

# ── Config ────────────────────────────────────────────────────────────────────
OUTPUT_FILE = os.path.join(os.path.dirname(__file__), 'pos_db_cpanel_export.sql')
CHUNK_SIZE  = 500   # rows per INSERT statement (keeps file manageable)

DB_CONFIG = {
    'unix_socket': '/tmp/mysql.sock',
    'user':        'root',
    'password':    '',              # leave empty if no password
    'database':    'pos_db',
}

# Tables to export IN THIS ORDER (respects foreign keys)
TABLES = ['products', 'store_items', 'orders', 'order_items']

# ── Connect ───────────────────────────────────────────────────────────────────
try:
    conn   = mysql.connector.connect(**DB_CONFIG)
    cursor = conn.cursor()
    print('✅ Connected to local MySQL')
except mysql.connector.Error as e:
    print(f'❌ Connection failed: {e}')
    sys.exit(1)

# ── Helper ────────────────────────────────────────────────────────────────────
def escape(val):
    """Safely escape a value for SQL INSERT."""
    if val is None:
        return 'NULL'
    if isinstance(val, (int, float)):
        return str(val)
    # Escape backslashes first, then quotes
    val = str(val).replace('\\', '\\\\').replace("'", "\\'")
    return f"'{val}'"

def get_create_table(table):
    cursor.execute(f'SHOW CREATE TABLE `{table}`')
    row = cursor.fetchone()
    ddl = row[1]
    # Strip ENGINE charset extras for compatibility; keep it clean
    return ddl

# ── Write SQL ─────────────────────────────────────────────────────────────────
with open(OUTPUT_FILE, 'w', encoding='utf-8') as f:

    # Header
    f.write('-- ================================================================\n')
    f.write(f'-- cPanel-ready export of pos_db\n')
    f.write(f'-- Generated: {datetime.now().strftime("%Y-%m-%d %H:%M:%S")}\n')
    f.write('--\n')
    f.write('-- HOW TO IMPORT:\n')
    f.write('--   1. cPanel > MySQL Databases > create database + user\n')
    f.write('--   2. phpMyAdmin > click your database on the left sidebar\n')
    f.write('--   3. Import tab > choose this file > Go\n')
    f.write('-- ================================================================\n\n')

    f.write('SET FOREIGN_KEY_CHECKS = 0;\n')
    f.write('SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";\n')
    f.write('SET time_zone = "+00:00";\n\n')

    for table in TABLES:
        # Check table exists
        cursor.execute("SHOW TABLES LIKE %s", (table,))
        if not cursor.fetchone():
            print(f'  ⚠️  Table "{table}" not found — skipping')
            continue

        print(f'  📦 Exporting {table}…', end=' ', flush=True)

        # ── DROP + CREATE ──────────────────────────────────────────────────
        f.write(f'-- ------------------------------------------------------------\n')
        f.write(f'-- Table: {table}\n')
        f.write(f'-- ------------------------------------------------------------\n')
        f.write(f'DROP TABLE IF EXISTS `{table}`;\n')

        ddl = get_create_table(table)
        f.write(f'{ddl};\n\n')

        # ── Data ──────────────────────────────────────────────────────────
        cursor.execute(f'SELECT * FROM `{table}`')
        all_rows = cursor.fetchall()
        col_names = [d[0] for d in cursor.description]

        if not all_rows:
            f.write(f'-- (no data in {table})\n\n')
            print('0 rows')
            continue

        col_list = ', '.join(f'`{c}`' for c in col_names)

        # Write in chunks so phpMyAdmin doesn't time out on large tables
        for i in range(0, len(all_rows), CHUNK_SIZE):
            chunk = all_rows[i:i + CHUNK_SIZE]
            values_sql = ',\n  '.join(
                '(' + ', '.join(escape(cell) for cell in row) + ')'
                for row in chunk
            )
            f.write(f'INSERT INTO `{table}` ({col_list}) VALUES\n  {values_sql};\n')

        f.write('\n')
        print(f'{len(all_rows):,} rows')

    f.write('SET FOREIGN_KEY_CHECKS = 1;\n')

cursor.close()
conn.close()

size_kb = os.path.getsize(OUTPUT_FILE) / 1024
print(f'\n✅ Export complete: pos_db_cpanel_export.sql ({size_kb:.1f} KB)')
print(f'   Import this file via phpMyAdmin.')