#!/usr/bin/env bash
set -euo pipefail

SQLITE_DB="${1:-ideas.db}"
MYSQL_DB="${MYSQL_DB:-ideasnew}"
MYSQL_USER="${MYSQL_USER:-ideasuser}"
MYSQL_HOST="${MYSQL_HOST:-localhost}"
CSV_DIR="/tmp/ideas_import_csv"

if [ ! -f "$SQLITE_DB" ]; then
    echo "SQLite database not found: $SQLITE_DB"
    exit 1
fi

command -v sqlite3 >/dev/null || { echo "sqlite3 is not installed."; exit 1; }
command -v mysql >/dev/null || { echo "mysql/mariadb client is not installed."; exit 1; }

read -rsp "MariaDB password for ${MYSQL_USER}: " MYSQL_PWD
echo
export MYSQL_PWD

mkdir -p "$CSV_DIR"
rm -f "$CSV_DIR"/*.csv

TABLE_MAP=(
  "Acabados:Acabados"
  "BodegaOrder:BodegaOrder"
  "BodegaOrderDetalles:BodegaOrderDetalles"
  "Clientes:Clientes"
  "ComprasAdjuntos:ComprasAdjuntos"
  "ComprasDetail:ComprasDetail"
  "ComprasMaster:ComprasMaster"
  "CotDetAcabados:CotDetAcabados"
  "CotDetImpresion:CotDetImpresion"
  "Cotizacion:Cotizacion"
  "CotizacionDetalles:CotizacionDetalles"
  "Depositos:Depositos"
  "Disenos:Disenos"
  "Empleados:Empleados"
  "Entregas:Entregas"
  "EntregasDetalles:EntregasDetalles"
  "HerramientaReqDet:HerramientaReqDet"
  "HerramientaReqMain:HerramientaReqMain"
  "Herramientas:Herramientas"
  "Impresion:Impresion"
  "Impresiones:Impresiones"
  "Inventario:Inventario"
  "Pagos:Pagos"
  "Productos:Productos"
  "Vendor:Vendor"
  "anuladas:anuladas"
  "configuracion:configuracion"
  "orden:Orden"
  "trabajos:trabajos"
  "trabajosDetalles:trabajosDetalles"
)

echo "Testing MariaDB connection..."
mysql --local-infile=1 -h "$MYSQL_HOST" -u "$MYSQL_USER" "$MYSQL_DB" -e "SELECT DATABASE();"

echo "Clearing destination tables in ${MYSQL_DB}..."
mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" "$MYSQL_DB" <<SQL
SET FOREIGN_KEY_CHECKS = 0;

DELETE FROM \`ComprasAdjuntos\`;
DELETE FROM \`ComprasDetail\`;
DELETE FROM \`ComprasMaster\`;
DELETE FROM \`BodegaOrderDetalles\`;
DELETE FROM \`BodegaOrder\`;
DELETE FROM \`CotDetAcabados\`;
DELETE FROM \`CotDetImpresion\`;
DELETE FROM \`CotizacionDetalles\`;
DELETE FROM \`Cotizacion\`;
DELETE FROM \`Pagos\`;
DELETE FROM \`Orden\`;
DELETE FROM \`anuladas\`;
DELETE FROM \`Depositos\`;
DELETE FROM \`Disenos\`;
DELETE FROM \`EntregasDetalles\`;
DELETE FROM \`Entregas\`;
DELETE FROM \`trabajosDetalles\`;
DELETE FROM \`trabajos\`;
DELETE FROM \`HerramientaReqDet\`;
DELETE FROM \`HerramientaReqMain\`;
DELETE FROM \`Herramientas\`;
DELETE FROM \`Impresiones\`;
DELETE FROM \`Inventario\`;
DELETE FROM \`Vendor\`;
DELETE FROM \`Productos\`;
DELETE FROM \`Impresion\`;
DELETE FROM \`Acabados\`;
DELETE FROM \`Clientes\`;
DELETE FROM \`Empleados\`;
DELETE FROM \`configuracion\`;

SET FOREIGN_KEY_CHECKS = 1;
SQL

for pair in "${TABLE_MAP[@]}"; do
    SRC="${pair%%:*}"
    DST="${pair##*:}"
    CSV_FILE="${CSV_DIR}/${SRC}.csv"

    echo "Exporting SQLite table ${SRC}..."

    sqlite3 "$SQLITE_DB" <<SQL
.headers on
.mode csv
.once ${CSV_FILE}
SELECT * FROM "${SRC}";
SQL

    ROWS=$(sqlite3 "$SQLITE_DB" "SELECT COUNT(*) FROM \"${SRC}\";")

    if [ "$ROWS" -eq 0 ]; then
        echo "Skipping ${SRC}; 0 rows."
        continue
    fi

    COLS=$(sqlite3 "$SQLITE_DB" "PRAGMA table_info('${SRC}');" | awk -F'|' '{print $2}')

    VAR_LIST=""
    SET_LIST=""
    i=1

    while read -r COL; do
        [ -z "$COL" ] && continue

        if [ -z "$VAR_LIST" ]; then
            VAR_LIST="@c${i}"
            SET_LIST="\`${COL}\` = NULLIF(@c${i}, '')"
        else
            VAR_LIST="${VAR_LIST}, @c${i}"
            SET_LIST="${SET_LIST}, \`${COL}\` = NULLIF(@c${i}, '')"
        fi

        i=$((i + 1))
    done <<< "$COLS"

    echo "Importing ${ROWS} rows into MariaDB table ${DST}..."

    mysql --local-infile=1 -h "$MYSQL_HOST" -u "$MYSQL_USER" "$MYSQL_DB" <<SQL
SET FOREIGN_KEY_CHECKS = 0;

LOAD DATA LOCAL INFILE '${CSV_FILE}'
INTO TABLE \`${DST}\`
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(${VAR_LIST})
SET ${SET_LIST};

SET FOREIGN_KEY_CHECKS = 1;
SQL

done

echo
echo "Import complete. Row counts:"
mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" "$MYSQL_DB" <<SQL
SELECT 'Acabados' AS TableName, COUNT(*) AS RowsCount FROM Acabados
UNION ALL SELECT 'Clientes', COUNT(*) FROM Clientes
UNION ALL SELECT 'Cotizacion', COUNT(*) FROM Cotizacion
UNION ALL SELECT 'CotizacionDetalles', COUNT(*) FROM CotizacionDetalles
UNION ALL SELECT 'Orden', COUNT(*) FROM Orden
UNION ALL SELECT 'Pagos', COUNT(*) FROM Pagos
UNION ALL SELECT 'Productos', COUNT(*) FROM Productos
UNION ALL SELECT 'Inventario', COUNT(*) FROM Inventario
UNION ALL SELECT 'Vendor', COUNT(*) FROM Vendor
UNION ALL SELECT 'trabajos', COUNT(*) FROM trabajos
UNION ALL SELECT 'Entregas', COUNT(*) FROM Entregas;
SQL

unset MYSQL_PWD