#!/usr/bin/env bash
# Builds the importable starting database for cPanel / phpMyAdmin:
#   deploy/database/zues-database.sql          tables + 10 expense categories + one admin
#   deploy/database/zues-triggers-optional.sql the extra double-booking triggers
#
# Needs a local MySQL that root can use without a password (as in development).
# Usage: ./deploy/make-database-sql.sh [admin-email] [admin-password]
set -euo pipefail

cd "$(dirname "$0")/.."
EMAIL="${1:-admin@example.com}"
PASSWORD="${2:-ChangeMe-2026}"
TMP_DB="zues_sql_export_$$"
OUT=deploy/database

cleanup() { mysql -uroot -e "DROP DATABASE IF EXISTS \`$TMP_DB\`" 2>/dev/null || true; }
trap cleanup EXIT

mysql -uroot -e "CREATE DATABASE \`$TMP_DB\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"

# Production settings: the seeder then adds only the expense categories.
export APP_ENV=production DB_DATABASE="$TMP_DB" DB_USERNAME=root DB_PASSWORD=
php artisan migrate --force --quiet
php artisan db:seed --force --quiet
php artisan app:create-owner --name="Admin" --email="$EMAIL" --password="$PASSWORD" >/dev/null

{
    cat <<HEADER
-- Zues starting database, generated $(date '+%Y-%m-%d %H:%M').
--
-- Contains: every table, the migrations list (so "php artisan migrate" knows
-- the database is up to date), the 10 expense categories, and one admin:
--     email:    $EMAIL
--     password: $PASSWORD   (you must choose a new one at first sign-in)
-- No sample properties, rooms, bookings or expenses.
--
-- Import into an EMPTY database: phpMyAdmin -> select the database -> Import.
-- Double-booking triggers are in zues-triggers-optional.sql (see DEPLOY.md).

SET FOREIGN_KEY_CHECKS = 0;
SET NAMES utf8mb4;

HEADER
    mysqldump -uroot --single-transaction --skip-triggers --no-tablespaces \
        --set-gtid-purged=OFF --skip-comments --skip-add-locks --default-character-set=utf8mb4 \
        "$TMP_DB" \
      | grep -v -E '^SET @@|SQL_LOG_BIN|GTID_PURGED' \
      | sed -E 's/ DEFINER=`[^`]+`@`[^`]+`//g'
    printf '\nSET FOREIGN_KEY_CHECKS = 1;\n'
} > "$OUT/zues-database.sql"

php -r '
require "vendor/autoload.php";
$app = require "bootstrap/app.php";
$app->make(Illuminate\Contracts\Console\Kernel::class)->bootstrap();
echo "-- Optional extra protection: database triggers that refuse double-bookings.\n";
echo "-- Import AFTER zues-database.sql. If it fails with \"#1419 ... SUPER privilege\",\n";
echo "-- your host does not allow triggers; the app still refuses double-bookings itself.\n\n";
echo "DROP TRIGGER IF EXISTS room_blocks_before_update;\nDROP TRIGGER IF EXISTS room_blocks_before_insert;\n";
echo "DROP TRIGGER IF EXISTS bookings_before_update;\nDROP TRIGGER IF EXISTS bookings_before_insert;\n";
echo "DROP PROCEDURE IF EXISTS assert_room_bookable;\n\nDELIMITER $$\n\n";
foreach (App\Database\AvailabilityTriggers::sql() as $statement) { echo trim($statement), "\$\$\n\n"; }
echo "DELIMITER ;\n";
' > "$OUT/zues-triggers-optional.sql"

echo "✓ $OUT/zues-database.sql ($(du -h "$OUT/zues-database.sql" | cut -f1)) — admin: $EMAIL / $PASSWORD"
echo "✓ $OUT/zues-triggers-optional.sql"