#!/bin/sh
# Writes database/sql/cleanmate.sql: every CleanMate table, empty, for cPanel →
# phpMyAdmin → Import. Made from a fresh database built by the migrations, so
# it always matches the code. Needs a local MySQL / MariaDB (root, no password):
#
#   sh laravel_server/tools/make_sql.sh [port]     (default port 3306)
set -e
cd "$(dirname "$0")/.."
PORT=${1:-3306}
DB=cleanmate_sql_export
SQL="mariadb -uroot -h127.0.0.1 -P$PORT --skip-ssl"
DUMP="mariadb-dump -uroot -h127.0.0.1 -P$PORT --skip-ssl"
$SQL -e "DROP DATABASE IF EXISTS $DB; CREATE DATABASE $DB CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;"
DB_DATABASE=$DB DB_PORT=$PORT DB_HOST=127.0.0.1 DB_USERNAME=root DB_PASSWORD= php artisan migrate --force >/dev/null
V=$(python3 -c "import json;print(json.load(open('../shared/schema.json')).get('version','?'))")
mkdir -p database/sql
{
  echo "-- CleanMate (Laravel + MySQL) database: every table, empty."
  echo "-- Import in cPanel → phpMyAdmin → your database → Import. See DEPLOY_CPANEL.md."
  echo "-- Made from schema $V ($(date +%Y-%m-%d))."
  echo "SET NAMES utf8mb4;"
  echo "SET FOREIGN_KEY_CHECKS=0;"
  $DUMP --no-data --skip-comments --skip-add-locks --skip-dump-date --compact $DB | sed -E 's/ AUTO_INCREMENT=[0-9]+//' | grep -v '^/\*' | grep -v '^$'
  $DUMP --no-create-info --skip-comments --compact --skip-extended-insert $DB migrations
  echo "SET FOREIGN_KEY_CHECKS=1;"
} | sed 's/^CREATE TABLE `/CREATE TABLE IF NOT EXISTS `/; s/^INSERT INTO `migrations`/INSERT IGNORE INTO `migrations`/' > database/sql/cleanmate.sql
$SQL -e "DROP DATABASE $DB;"
echo "database/sql/cleanmate.sql: $(grep -c 'CREATE TABLE' database/sql/cleanmate.sql) tables"
