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

# Loads DB creds from server/.env (or environment) and applies incremental schema changes only.

ROOT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
source "$ROOT_DIR/server/load-env.sh"
load_server_env "$ROOT_DIR"

: "${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")

mysql_query() {
  "${MYSQL_CMD[@]}" -N -e "$1"
}

table_exists() {
  mysql_query "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='${DB_NAME}' AND table_name='$1';"
}

column_exists() {
  mysql_query "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='${DB_NAME}' AND table_name='$1' AND column_name='$2';"
}

column_data_type() {
  mysql_query "SELECT COALESCE(DATA_TYPE, '') FROM information_schema.columns WHERE table_schema='${DB_NAME}' AND table_name='$1' AND column_name='$2' LIMIT 1;"
}

constraint_exists() {
  mysql_query "SELECT COUNT(*) FROM information_schema.table_constraints WHERE table_schema='${DB_NAME}' AND table_name='$1' AND constraint_name='$2';"
}

require_table() {
  local table="$1"
  if [ "$(table_exists "$table")" -eq 0 ]; then
    echo "Missing base table '$table'. Run server/schema.sql for initial setup." >&2
    exit 1
  fi
}

ensure_table() {
  local table="$1"
  local ddl="$2"
  if [ "$(table_exists "$table")" -eq 0 ]; then
    "${MYSQL_CMD[@]}" -e "$ddl"
  fi
}

ensure_column() {
  local table="$1"
  local column="$2"
  local definition="$3"
  if [ "$(column_exists "$table" "$column")" -eq 0 ]; then
    "${MYSQL_CMD[@]}" -e "ALTER TABLE ${table} ADD COLUMN ${definition};"
  fi
}

ensure_fk() {
  local table="$1"
  local name="$2"
  local definition="$3"
  if [ "$(constraint_exists "$table" "$name")" -eq 0 ]; then
    "${MYSQL_CMD[@]}" -e "ALTER TABLE ${table} ADD CONSTRAINT ${name} ${definition};"
  fi
}

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

for base_table in branches categories menu_items orders order_items users; do
  require_table "$base_table"
done

# Branch updates
ensure_column branches lat "lat DECIMAL(10,7)"
ensure_column branches lng "lng DECIMAL(10,7)"
ensure_column branches tracking_prefix "tracking_prefix VARCHAR(12)"
ensure_column branches tracking_year "tracking_year INT"
ensure_column branches admin_whatsapp "admin_whatsapp VARCHAR(50)"
ensure_column branches delivery_per_km "delivery_per_km DECIMAL(10,2) DEFAULT 0"
ensure_column branches min_delivery_charge "min_delivery_charge DECIMAL(10,2) DEFAULT 0"
ensure_column branches max_delivery_km "max_delivery_km DECIMAL(10,2) DEFAULT 0"
ensure_column branches is_maintenance "is_maintenance TINYINT(1) DEFAULT 0"
ensure_column branches service_charge_pct "service_charge_pct DECIMAL(5,2) DEFAULT 0"
ensure_column branches open_time "open_time TIME"
ensure_column branches close_time "close_time TIME"
ensure_column branches friday_open_time "friday_open_time TIME"
ensure_column branches friday_close_time "friday_close_time TIME"
ensure_column branches is_temp_closed "is_temp_closed TINYINT(1) DEFAULT 0"
ensure_column branches temp_closed_until "temp_closed_until DATETIME"
ensure_column branches temp_closed_message "temp_closed_message TEXT"
ensure_column branches temp_closed_image_url "temp_closed_image_url VARCHAR(512)"
ensure_column branches tables_two "tables_two INT DEFAULT 0"
ensure_column branches tables_four "tables_four INT DEFAULT 0"
ensure_column branches tables_eight "tables_eight INT DEFAULT 0"
ensure_column branches admin_phone "admin_phone VARCHAR(50)"
ensure_column branches dine_in_info "dine_in_info TEXT"
ensure_column branches takeaway_info "takeaway_info TEXT"
ensure_column branches specials_info "specials_info TEXT"
ensure_column branches payment_cash "payment_cash TINYINT(1) DEFAULT 1"
ensure_column branches payment_payhere "payment_payhere TINYINT(1) DEFAULT 1"
ensure_column branches payment_card_on_delivery "payment_card_on_delivery TINYINT(1) DEFAULT 1"
ensure_column branches order_delivery "order_delivery TINYINT(1) DEFAULT 1"
ensure_column branches order_pickup "order_pickup TINYINT(1) DEFAULT 1"
ensure_column branches order_dine_in "order_dine_in TINYINT(1) DEFAULT 1"

# Menu + categories updates
ensure_column menu_items is_veg "is_veg TINYINT(1) NOT NULL DEFAULT 0"
ensure_column categories additional_type "additional_type TINYINT(1) NOT NULL DEFAULT 0"
ensure_column options is_pizza "is_pizza TINYINT(1) NOT NULL DEFAULT 0"

# New tables
ensure_table currency_rates "CREATE TABLE currency_rates (
  code VARCHAR(8) PRIMARY KEY,
  symbol VARCHAR(8) NOT NULL DEFAULT '',
  rate DECIMAL(12,6) NOT NULL DEFAULT 1.0,
  name VARCHAR(50) DEFAULT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);"

ensure_table delivery_riders "CREATE TABLE delivery_riders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  phone VARCHAR(50) NOT NULL,
  branch_id VARCHAR(64) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE ON UPDATE CASCADE
);"

ensure_table sizes "CREATE TABLE sizes (
  id VARCHAR(64) PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  price DECIMAL(10,2) NOT NULL DEFAULT 0,
  is_pizza TINYINT(1) NOT NULL DEFAULT 0
);"
ensure_column sizes is_pizza "is_pizza TINYINT(1) NOT NULL DEFAULT 0"

ensure_table menu_item_sizes "CREATE TABLE menu_item_sizes (
  menu_item_id VARCHAR(64) NOT NULL,
  size_id VARCHAR(64) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (menu_item_id, size_id),
  CONSTRAINT fk_mis_item FOREIGN KEY (menu_item_id) REFERENCES menu_items(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_mis_size FOREIGN KEY (size_id) REFERENCES sizes(id)
    ON DELETE CASCADE ON UPDATE CASCADE
);"

ensure_table branch_seasons "CREATE TABLE branch_seasons (
  id INT AUTO_INCREMENT PRIMARY KEY,
  branch_id VARCHAR(64) NOT NULL,
  name VARCHAR(150),
  start_month TINYINT NOT NULL,
  end_month TINYINT NOT NULL,
  open_time TIME NOT NULL,
  close_time TIME NOT NULL,
  friday_open_time TIME,
  friday_close_time TIME,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE ON UPDATE CASCADE
);"

ensure_table settings "CREATE TABLE settings (
  key_name VARCHAR(64) PRIMARY KEY,
  value TEXT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);"

ensure_table combos "CREATE TABLE combos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  branch_id VARCHAR(64) NOT NULL,
  name VARCHAR(150) NOT NULL,
  description TEXT NULL,
  image_url VARCHAR(512) NULL,
  price DECIMAL(10,2) NOT NULL DEFAULT 0,
  expires_at DATETIME NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE ON UPDATE CASCADE
);"
ensure_column combos expires_at "expires_at DATETIME NULL"

ensure_table combo_items "CREATE TABLE combo_items (
  combo_id INT NOT NULL,
  menu_item_id VARCHAR(64) NOT NULL,
  quantity INT NOT NULL DEFAULT 1,
  size_id VARCHAR(64) NULL,
  option_ids VARCHAR(255) NULL,
  PRIMARY KEY (combo_id, menu_item_id),
  FOREIGN KEY (combo_id) REFERENCES combos(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (size_id) REFERENCES sizes(id) ON DELETE SET NULL ON UPDATE CASCADE
);"

if [ "$(table_exists combo_items)" -eq 1 ] && [ "$(column_exists combo_items size_id)" -eq 1 ] && [ "$(column_data_type combo_items size_id)" != "varchar" ]; then
  "${MYSQL_CMD[@]}" -e "ALTER TABLE combo_items MODIFY COLUMN size_id VARCHAR(64) NULL;"
fi

ensure_table order_combo_items "CREATE TABLE order_combo_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  combo_id INT NULL,
  combo_name VARCHAR(150) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  quantity INT NOT NULL DEFAULT 1,
  included_items_json LONGTEXT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (combo_id) REFERENCES combos(id) ON DELETE SET NULL ON UPDATE CASCADE
);"

ensure_column order_combo_items included_items_json "included_items_json LONGTEXT NULL"

ensure_table competitions "CREATE TABLE competitions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(160) NOT NULL UNIQUE,
  title VARCHAR(180) NOT NULL,
  description TEXT,
  rules TEXT,
  regulations TEXT,
  banner_url TEXT,
  start_at DATETIME NULL,
  end_at DATETIME NULL,
  entry_fee_mode ENUM('free', 'paid') NOT NULL DEFAULT 'free',
  entry_fee_amount DECIMAL(10,2) NULL,
  entry_fee_currency VARCHAR(16) DEFAULT 'LKR',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

ensure_table competition_prizes "CREATE TABLE competition_prizes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  competition_id INT NOT NULL,
  label VARCHAR(120) NOT NULL,
  value TEXT,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (competition_id) REFERENCES competitions(id) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

ensure_table competition_form_fields "CREATE TABLE competition_form_fields (
  id INT AUTO_INCREMENT PRIMARY KEY,
  competition_id INT NOT NULL,
  field_key VARCHAR(160) NOT NULL,
  label VARCHAR(160) NOT NULL,
  type VARCHAR(40) NOT NULL,
  placeholder VARCHAR(255) NULL,
  help_text TEXT NULL,
  options_json LONGTEXT NULL,
  is_required TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (competition_id) REFERENCES competitions(id) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

ensure_table competition_applications "CREATE TABLE competition_applications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  competition_id INT NOT NULL,
  applicant_name VARCHAR(255) NULL,
  applicant_email VARCHAR(255) NULL,
  applicant_phone VARCHAR(80) NULL,
  answers_json LONGTEXT NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'new',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (competition_id) REFERENCES competitions(id) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

ensure_table admin_users "CREATE TABLE admin_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(150) NOT NULL UNIQUE,
  name VARCHAR(150),
  phone VARCHAR(50),
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(20) NOT NULL DEFAULT 'super',
  branch_id VARCHAR(64),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);"

ensure_column admin_users role "role VARCHAR(20) NOT NULL DEFAULT 'super'"
ensure_column admin_users branch_id "branch_id VARCHAR(64) NULL"
ensure_column admin_users phone "phone VARCHAR(50) NULL"

ensure_table admin_sessions "CREATE TABLE 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)
);"

# Orders updates
ensure_column orders reservation_time "reservation_time DATETIME NULL"
ensure_column orders table_count "table_count INT NULL"
ensure_column orders table_size "table_size INT NULL"
ensure_column orders reservation_status "reservation_status VARCHAR(32) DEFAULT 'pending'"
ensure_column orders rider_id "rider_id INT NULL"
ensure_column orders serial_code "serial_code VARCHAR(50) NULL"
ensure_column orders user_id "user_id INT NULL"

ensure_fk orders fk_orders_user "FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE"
ensure_fk orders fk_orders_rider "FOREIGN KEY (rider_id) REFERENCES delivery_riders(id) ON DELETE SET NULL ON UPDATE CASCADE"

# Order item updates
ensure_column order_items size_id "size_id VARCHAR(64) NULL"
ensure_fk order_items fk_order_items_size "FOREIGN KEY (size_id) REFERENCES sizes(id) ON DELETE SET NULL ON UPDATE CASCADE"

echo "Schema upgrades complete."