#!/usr/bin/env bash
# One-shot Pinakle migrate: MySQL import + Apache site + upload perms.
# Run: sudo bash /srv/www/systems/pinakle/deploy/setup.sh
set -euo pipefail

ROOT="/srv/www/systems/pinakle"
SITE="$ROOT/site"
DEPLOY="$ROOT/deploy"
SQL_ZIP="/srv/www/systems/old databases/pinakle_orders202610241728.zip"
SQL_TMP="/tmp/pinakle_orders_import.sql"
APACHE_SITE="/etc/apache2/sites-available/pinakle.conf"

if [[ "$(id -u)" -ne 0 ]]; then
  echo "Run as root: sudo bash $0" >&2
  exit 1
fi

echo "==> MySQL: create database + pinakle_app user"
mysql < "$DEPLOY/setup-mysql.sql"

echo "==> Extract and import dump (MySQL 5.7 → 8.4)"
unzip -p "$SQL_ZIP" > "$SQL_TMP"
# Soften sql_mode for legacy dump quirks during import
mysql --default-character-set=utf8mb4 pinakle_orders -e "SET SESSION sql_mode='NO_ENGINE_SUBSTITUTION';"
mysql --default-character-set=utf8mb4 --force pinakle_orders < "$SQL_TMP"
rm -f "$SQL_TMP"

echo "==> Point app configs at host MySQL (127.0.0.1:3306)"
# Migration testing used LXD on 3307; production uses host mysqld on 3306.
for f in \
  "$SITE/config_pinakle.php" \
  "$SITE/Admin/inc/config.php" \
  "$SITE/customers/inc/config.php"
do
  sed -i 's/\$dbport\s*=\s*3307/\$dbport     = 3306/' "$f"
done

echo "==> Verify tables"
TABLES=$(mysql -N -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='pinakle_orders'")
echo "    tables in pinakle_orders: $TABLES"
mysql -N -e "SELECT COUNT(*) AS admin_users FROM pinakle_orders.admin_users; SELECT COUNT(*) AS customers FROM pinakle_orders.customers; SELECT COUNT(*) AS kitchen_orders FROM pinakle_orders.kitchen_orders;"

echo "==> Permissions for PHP-FPM (www-data) write dirs"
WRITE_DIRS=(
  "$SITE/uploaded_files"
  "$SITE/pdf_submissions"
  "$SITE/quote_attachments"
  "$SITE/invoice_attachments"
  "$SITE/project_details_uploads"
  "$SITE/order_section_images"
  "$SITE/Admin/logs"
  "$SITE/Admin/data"
  "$SITE/Admin/backups"
  "$SITE/kilwee/uploads"
  "$SITE/kilwee/data"
)
for d in "${WRITE_DIRS[@]}"; do
  mkdir -p "$d"
  chown -R owner:www-data "$d"
  chmod -R ug+rwX "$d"
done
# Ensure tree is readable by www-data
chown -R owner:www-data "$ROOT"
chmod -R u+rwX,g+rX,o+rX "$ROOT"
# Re-apply write on upload dirs after recursive chmod
for d in "${WRITE_DIRS[@]}"; do
  chmod -R ug+rwX "$d"
done

echo "==> Apache vhost"
cp "$DEPLOY/pinakle.conf" "$APACHE_SITE"
a2ensite pinakle.conf >/dev/null
# php-fpm already enabled via conf; ensure modules
a2enmod proxy_fcgi setenvif rewrite headers >/dev/null 2>&1 || true
apache2ctl configtest
systemctl reload apache2

echo "==> Quick PHP/DB smoke test as www-data"
sudo -u www-data php -r '
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$c = new mysqli("localhost", "pinakle_app", "Pinakle26@", "pinakle_orders");
$c->set_charset("utf8mb4");
$n = $c->query("SELECT COUNT(*) c FROM admin_users")->fetch_assoc()["c"];
echo "DB OK — admin_users=$n\n";
'

echo ""
echo "Done."
echo "  Public site:  http://192.168.1.177/  (Host: pinakle.local) or add Host header"
echo "  Admin login:  http://192.168.1.177/Admin/"
echo "  Customers:    http://192.168.1.177/customers/"
echo ""
echo "Tip: add to /etc/hosts on your PC:  192.168.1.177  pinakle.local"
echo "Or test with:  curl -H 'Host: pinakle.local' http://192.168.1.177/"