#!/bin/bash
TABLE=$1
if [ -z "$TABLE" ]; then echo "Usage: db-push <table>"; exit 1; fi

# --- CONFIGURATION ---
SSH_HOST="dpkpnm.com"
SSH_PORT="17177"
SSH_USER="dpkpnmc1"
DB_USER="dpkpnmc1_1"
DB_NAME="dpkpnmc1_data"
DB_PASS='ACuRdkCV-k%&'

# 1. Auto-Create Table
SCHEMA_FILE="$HOME/web/api2/tables/$TABLE.php"
if [ -f "$SCHEMA_FILE" ]; then
    SCHEMA_SQL=$(php -r "echo include '$SCHEMA_FILE';")
    ssh -p "$SSH_PORT" "$SSH_USER@$SSH_HOST" "mysql -u $DB_USER -p'$DB_PASS' $DB_NAME -e \"CREATE TABLE IF NOT EXISTS $TABLE ($SCHEMA_SQL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;\""
fi

# 2. Push Data (Fixing column-value alignment)
# We use keys as a variable to ensure the order matches for both columns and values
jq -r '.[] | keys as $k | "INSERT IGNORE INTO '$TABLE' (" + ($k | map("`\(.)`") | join(", ")) + ") VALUES (" + ([$k[] as $key | .[$key] | if . == null then "NULL" elif type=="string" then "\"\(.)\"" else . end] | join(", ")) + ");"' | \
ssh -p "$SSH_PORT" "$SSH_USER@$SSH_HOST" "mysql -u $DB_USER -p'$DB_PASS' $DB_NAME --force"