#!/usr/bin/env bash

set -Eeuo pipefail

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
ROOT_DIR="$(cd "${SCRIPT_DIR}/.." && pwd)"
MIGRATIONS_DIR="${ROOT_DIR}/migrations"

require_cmd() {
  if ! command -v "$1" >/dev/null 2>&1; then
    echo "[setup] ERRO: comando '$1' não encontrado. Instale-o e tente novamente." >&2
    exit 1
  fi
}

require_cmd psql
require_cmd php

if [[ -f "${ROOT_DIR}/.env" ]]; then
  set -a
  # shellcheck disable=SC1090
  source <(sed $'s/\r$//' "${ROOT_DIR}/.env")
  set +a
fi

for var in DB_HOST DB_PORT DB_NAME DB_USER DB_PASSWORD; do
  if [[ -z "${!var:-}" ]]; then
    echo "[setup] ERRO: variável ${var} não definida (.env)." >&2
    exit 1
  fi
done

DB_SUPERUSER="${DB_SUPERUSER:-${DB_USER}}"
DB_SUPERUSER_PASSWORD="${DB_SUPERUSER_PASSWORD:-${DB_PASSWORD}}"
DB_SUPERUSER_DB="${DB_SUPERUSER_DB:-postgres}"

run_psql() {
  local db="$1"
  shift
  PGPASSWORD="${DB_PASSWORD}" psql -h "${DB_HOST}" -p "${DB_PORT}" -U "${DB_USER}" -d "${db}" "$@"
}

run_psql_super() {
  local db="${1:-${DB_SUPERUSER_DB}}"
  shift
  PGPASSWORD="${DB_SUPERUSER_PASSWORD}" psql -h "${DB_HOST}" -p "${DB_PORT}" -U "${DB_SUPERUSER}" -d "${db}" "$@"
}

database_exists() {
  local exists
  exists="$(run_psql_super "${DB_SUPERUSER_DB}" -tAc "SELECT 1 FROM pg_database WHERE datname='${DB_NAME}'" | tr -d '[:space:]')"
  [[ "${exists}" == "1" ]]
}

ensure_migrations_table() {
  run_psql "${DB_NAME}" -v ON_ERROR_STOP=1 <<'SQL'
CREATE TABLE IF NOT EXISTS migrations_run (
  filename TEXT PRIMARY KEY,
  applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
SQL
}

migration_already_applied() {
  local name="$1"
  local result
  result="$(run_psql "${DB_NAME}" -tAc "SELECT 1 FROM migrations_run WHERE filename='${name//\'/''}'" | tr -d '[:space:]')"
  [[ "${result}" == "1" ]]
}

record_migration_applied() {
  local name="$1"
  run_psql "${DB_NAME}" -v ON_ERROR_STOP=1 -c "INSERT INTO migrations_run (filename) VALUES ('${name//\'/''}') ON CONFLICT (filename) DO NOTHING;"
}

bootstrap_migrations_state() {
  local count
  count="$(run_psql "${DB_NAME}" -tAc "SELECT COUNT(*) FROM migrations_run" | tr -d '[:space:]')"
  if [[ "${count}" -gt 0 ]]; then
    return
  fi

  local chain_reg
  chain_reg="$(run_psql "${DB_NAME}" -tAc "SELECT to_regclass('public.chain')" | tr -d '[:space:]')"
  if [[ -n "${chain_reg}" ]]; then
    record_migration_applied "migrations_v1.sql"
  fi

  local cpr_col
  cpr_col="$(run_psql "${DB_NAME}" -tAc "SELECT 1 FROM information_schema.columns WHERE table_name='cpr' AND column_name='user_id'" | tr -d '[:space:]')"
  if [[ "${cpr_col}" == "1" ]]; then
    record_migration_applied "migration_user_id.sql"
  fi
}

if [[ ! -d "${MIGRATIONS_DIR}" ]]; then
  echo "[setup] ERRO: diretório de migrações inexistente (${MIGRATIONS_DIR})." >&2
  exit 1
fi

collect_migrations() {
  local -a ordered=()
  local primary="${MIGRATIONS_DIR}/migrations_v1.sql"
  if [[ -f "${primary}" ]]; then
    ordered+=("${primary}")
  fi

  local -a extras=()
  local file
  shopt -s nullglob
  for file in "${MIGRATIONS_DIR}"/*.sql; do
    [[ "${file}" == "${primary}" ]] && continue
    extras+=("${file}")
  done
  shopt -u nullglob

  if (( ${#extras[@]} )); then
    # shellcheck disable=SC2207
    extras=($(printf '%s\n' "${extras[@]}" | sort))
    ordered+=("${extras[@]}")
  fi

  if (( ${#ordered[@]} == 0 )); then
    echo "[setup] ERRO: nenhum arquivo .sql encontrado em ${MIGRATIONS_DIR}." >&2
    exit 1
  fi

  MIGRATION_FILES=("${ordered[@]}")
}

collect_migrations

echo "[setup] Host=${DB_HOST} Port=${DB_PORT} DB=${DB_NAME} User=${DB_USER}"

echo "[setup] Verificando existência do banco '${DB_NAME}'..."
if database_exists; then
  echo "[setup] Banco já existe. Pulando recriação."
else
  echo "[setup] Banco não encontrado. Criando..."
  run_psql_super "${DB_SUPERUSER_DB}" -v ON_ERROR_STOP=1 -c "CREATE DATABASE \"${DB_NAME}\";"
fi

ensure_migrations_table

bootstrap_migrations_state

echo "[setup] Aplicando migrações..."
for migration in "${MIGRATION_FILES[@]}"; do
  name="$(basename "${migration}")"
  if migration_already_applied "${name}"; then
    echo "  -> ${name} (já aplicada, pulando)"
    continue
  fi

  echo "  -> ${name}"
  run_psql "${DB_NAME}" -v ON_ERROR_STOP=1 -f "${migration}"
  record_migration_applied "${name}"
done

echo "[setup] Inserindo dados iniciais (seed)..."
run_psql "${DB_NAME}" -v ON_ERROR_STOP=1 <<SQL
INSERT INTO "company" (company_name, company_flag, company_cnpj)
SELECT 'LumyonTech', 'a', '${COMPANY_CNPJ:-00000000000000}'
WHERE NOT EXISTS (SELECT 1 FROM "company" WHERE company_name = 'LumyonTech');
SQL

run_psql "${DB_NAME}" -v ON_ERROR_STOP=1 <<SQL
INSERT INTO "role" (company_id, role_name, role_permission, role_flag)
SELECT c.company_id, 'Admin', '{}'::jsonb, 'a'
FROM "company" c
WHERE c.company_name = 'LumyonTech'
  AND NOT EXISTS (
    SELECT 1 FROM "role" r WHERE r.role_name = 'Admin' AND r.company_id = c.company_id
  );
SQL

run_psql "${DB_NAME}" -v ON_ERROR_STOP=1 <<SQL
INSERT INTO "status" (status_id, status_status) VALUES
    (0, 'PENDING'),
    (1, 'CONFIRMED'),
    (2, 'EXPIRED')
ON CONFLICT (status_id) DO UPDATE
    SET status_status = EXCLUDED.status_status;
SQL

run_psql "${DB_NAME}" -v ON_ERROR_STOP=1 <<SQL
SELECT setval(
    pg_get_serial_sequence('"status"', 'status_id'),
    GREATEST((SELECT COALESCE(MAX(status_id), 0) FROM "status"), 1)
);
SQL

run_psql "${DB_NAME}" -v ON_ERROR_STOP=1 <<SQL
INSERT INTO "chain" (chain_name)
SELECT 'polygon'
WHERE NOT EXISTS (SELECT 1 FROM "chain" WHERE chain_name = 'polygon');
SQL

run_psql "${DB_NAME}" -v ON_ERROR_STOP=1 <<SQL
INSERT INTO "commodities" (commodities_name, commodities_flag)
SELECT 'soja', 'a'
WHERE NOT EXISTS (SELECT 1 FROM "commodities" WHERE commodities_name = 'soja');
SQL

run_psql "${DB_NAME}" -v ON_ERROR_STOP=1 <<SQL
INSERT INTO "wallet" (
  company_id,
  wallet_public_key,
  wallet_address,
  wallet_private_key,
  wallet_flag,
  chain_id
)
SELECT c.company_id,
       '0x04b6095043c25c42caa6bd6e9bd0245a106171cbe4dfc1014f4a710a0ef8d4f3e6911a21c5ae01e98b222b197e29ae808e127309a4d3c8ccbb99140f9949ff16b6',
       '0x0166117cE1C182D0Cf5cf605A2C07BB87C278F85',
       '0xffb107583d6829311e884f660d038be30ba4cb40263ba439fb9e082ff325c8da',
       'a',
       ch.chain_id
FROM "company" c
JOIN "chain" ch ON ch.chain_name = 'polygon'
WHERE c.company_name = 'LumyonTech'
  AND NOT EXISTS (
    SELECT 1 FROM "wallet" w
    WHERE w.wallet_address = '0x0166117cE1C182D0Cf5cf605A2C07BB87C278F85'
  );
SQL

DEFAULT_USER_NAME="${DEFAULT_USER_NAME:-Admin}"
DEFAULT_USER_EMAIL="${DEFAULT_USER_EMAIL:-admin@lumyon.tech}"
DEFAULT_USER_PASSWORD_HASH="$(php -r 'echo password_hash(getenv("DEFAULT_USER_PASSWORD") ?: "admin123", PASSWORD_DEFAULT);')"
DEFAULT_USER_PHONE="${DEFAULT_USER_PHONE:-5511999999999}"
DEFAULT_USER_ADDRESS="${DEFAULT_USER_ADDRESS:-Default Address}"
DEFAULT_USER_CITY="${DEFAULT_USER_CITY:-Sao Paulo}"
DEFAULT_USER_STATE="${DEFAULT_USER_STATE:-SP}"
DEFAULT_USER_ZIP="${DEFAULT_USER_ZIP:-00000000}"
DEFAULT_USER_COUNTRY="${DEFAULT_USER_COUNTRY:-BR}"
DEFAULT_USER_KYC="${DEFAULT_USER_KYC:-0}"
DEFAULT_USER_BIRTHDATE="${DEFAULT_USER_BIRTHDATE:-0}"
DEFAULT_USER_CPF="${DEFAULT_USER_CPF:-00000000000}"
DEFAULT_USER_KYC_EXTERNAL_ID="${DEFAULT_USER_KYC_EXTERNAL_ID:-default-kyc-id}"

echo "[setup] Inserindo usuário padrão (${DEFAULT_USER_EMAIL})..."
run_psql "${DB_NAME}" -v ON_ERROR_STOP=1 <<SQL
INSERT INTO "user" (
  user_name, user_email, user_password, user_phone, user_address, user_city, user_state, user_zip, user_country,
  user_kyc, kyc_external_id, user_birthdate, user_cpf, company_id, role_id, user_flag
)
SELECT
  '${DEFAULT_USER_NAME}',
  '${DEFAULT_USER_EMAIL}',
  '${DEFAULT_USER_PASSWORD_HASH}',
  '${DEFAULT_USER_PHONE}',
  '${DEFAULT_USER_ADDRESS}',
  '${DEFAULT_USER_CITY}',
  '${DEFAULT_USER_STATE}',
  '${DEFAULT_USER_ZIP}',
  '${DEFAULT_USER_COUNTRY}',
  ${DEFAULT_USER_KYC}::int,
  '${DEFAULT_USER_KYC_EXTERNAL_ID}',
  ${DEFAULT_USER_BIRTHDATE}::int,
  '${DEFAULT_USER_CPF}',
  c.company_id,
  r.role_id,
  'a'
FROM "company" c
JOIN "role" r ON r.company_id = c.company_id AND r.role_name = 'Admin'
WHERE c.company_name = 'LumyonTech'
  AND NOT EXISTS (SELECT 1 FROM "user" u WHERE u.user_email = '${DEFAULT_USER_EMAIL}');
SQL

echo "[setup] Finalizado com sucesso!"
run_psql "${DB_NAME}" <<'SQL'
\dt+ public.*
SQL