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

# Loads DB creds from server/.env (or environment) and applies schema upgrades + seeds.

ROOT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
ENV_FILE="$ROOT_DIR/server/.env"

if [ -f "$ENV_FILE" ]; then
  # shellcheck disable=SC2046
  export $(grep -v '^#' "$ENV_FILE" | grep -E '^(DB_HOST|DB_PORT|DB_USER|DB_PASSWORD|DB_NAME|PORT)=' | xargs)
fi

: "${DB_HOST:?DB_HOST is required}"
: "${DB_PORT:=3306}"
: "${DB_USER:?DB_USER is required}"
: "${DB_PASSWORD:?DB_PASSWORD is required}"
: "${DB_NAME:?DB_NAME is required}"

MYSQL_CMD=(mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" "-p$DB_PASSWORD" "$DB_NAME")

echo "Applying DB changes on $DB_HOST:$DB_PORT/$DB_NAME ..."

# Ensure new branch columns exist (MySQL 8+ supports IF NOT EXISTS).
\"${MYSQL_CMD[@]}\" <<'SQL'
ALTER TABLE branches
  ADD COLUMN IF NOT EXISTS lat DECIMAL(10,7),
  ADD COLUMN IF NOT EXISTS lng DECIMAL(10,7),
  ADD COLUMN IF NOT EXISTS delivery_per_km DECIMAL(10,2) DEFAULT 0,
  ADD COLUMN IF NOT EXISTS min_delivery_charge DECIMAL(10,2) DEFAULT 0,
  ADD COLUMN IF NOT EXISTS max_delivery_km DECIMAL(10,2) DEFAULT 0;
SQL

# Add user_id and FK only if missing
HAS_USER_ID=$("${MYSQL_CMD[@]}" -N -e "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='${DB_NAME}' AND table_name='orders' AND column_name='user_id';")
if [ "$HAS_USER_ID" -eq 0 ]; then
  "${MYSQL_CMD[@]}" -e "ALTER TABLE orders ADD COLUMN user_id INT NULL;"
fi

HAS_FK=$("${MYSQL_CMD[@]}" -N -e "SELECT COUNT(*) FROM information_schema.table_constraints WHERE table_schema='${DB_NAME}' AND table_name='orders' AND constraint_name='fk_orders_user';")
if [ "$HAS_FK" -eq 0 ]; then
  "${MYSQL_CMD[@]}" -e "ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE;"
fi

"${MYSQL_CMD[@]}" <<'SQL'
CREATE TABLE IF NOT EXISTS admin_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(150) NOT NULL UNIQUE,
  name VARCHAR(150),
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS admin_sessions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  admin_id INT NOT NULL,
  token CHAR(64) NOT NULL,
  expires_at DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE KEY uniq_admin_token (token)
);
SQL

echo "Running schema.sql ..."
"${MYSQL_CMD[@]}" < "$ROOT_DIR/server/schema.sql"

echo "Running seed.sql ..."
"${MYSQL_CMD[@]}" < "$ROOT_DIR/server/seed.sql"

echo "Done."