#!/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)"
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")

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';"
}

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 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"

# 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 admin_users "CREATE TABLE admin_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(150) NOT NULL UNIQUE,
  name VARCHAR(150),
  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_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)
);"

ensure_table pos_tickets "CREATE TABLE pos_tickets (
  id VARCHAR(64) PRIMARY KEY,
  code VARCHAR(32) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'open',
  branch_id VARCHAR(64) NOT NULL,
  order_type VARCHAR(16) NOT NULL DEFAULT 'dine-in',
  payment_method VARCHAR(32) NOT NULL DEFAULT 'cash',
  customer_query TEXT,
  selected_user_id INT NULL,
  show_new_customer_form TINYINT(1) NOT NULL DEFAULT 0,
  customer_name VARCHAR(150),
  customer_phone VARCHAR(50),
  customer_whatsapp VARCHAR(50),
  customer_email VARCHAR(150),
  customer_address TEXT,
  hotel_name VARCHAR(150),
  room_number VARCHAR(50),
  note TEXT,
  table_number VARCHAR(64),
  table_count INT NOT NULL DEFAULT 1,
  pickup_ready_mode VARCHAR(20) NOT NULL DEFAULT '30',
  pickup_custom_time VARCHAR(20),
  coupon_code_input VARCHAR(50),
  coupon_info_json LONGTEXT,
  cart_json LONGTEXT,
  kot_printed_quantities_json LONGTEXT,
  last_kot_at DATETIME NULL,
  created_by_admin_id INT NULL,
  updated_by_admin_id INT NULL,
  charged_order_id INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_pos_tickets_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_pos_tickets_user FOREIGN KEY (selected_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_pos_tickets_created_by FOREIGN KEY (created_by_admin_id) REFERENCES admin_users(id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_pos_tickets_updated_by FOREIGN KEY (updated_by_admin_id) REFERENCES admin_users(id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_pos_tickets_order FOREIGN KEY (charged_order_id) REFERENCES orders(id) ON DELETE SET NULL ON UPDATE CASCADE
);"

ensure_table pos_ticket_kots "CREATE TABLE pos_ticket_kots (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id VARCHAR(64) NOT NULL,
  kot_sequence INT NOT NULL DEFAULT 1,
  items_json LONGTEXT NOT NULL,
  note TEXT,
  created_by_admin_id INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_pos_ticket_kots_ticket FOREIGN KEY (ticket_id) REFERENCES pos_tickets(id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_pos_ticket_kots_admin FOREIGN KEY (created_by_admin_id) REFERENCES admin_users(id) ON DELETE SET NULL ON UPDATE CASCADE
);"

# 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."