#!/bin/bash

set -euo pipefail

# Caminhos
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
ROOT_DIR="$(cd "${SCRIPT_DIR}/.." && pwd)"
MIGRATIONS_FILE="${ROOT_DIR}/migrations/migrations_v1.sql"

# Carrega variáveis do .env se existir
if [[ -f "${ROOT_DIR}/.env" ]]; then
  set -a
  # shellcheck disable=SC1090
  source "${ROOT_DIR}/.env"
  set +a
fi

# Configurações de conexão (PostgreSQL)
DB_HOST=${DB_HOST}
DB_PORT=${DB_PORT}
DB_NAME=${DB_NAME}
DB_USER=${DB_USER}
DB_PASSWORD=${DB_PASSWORD}

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

if [[ ! -f "${MIGRATIONS_FILE}" ]]; then
  echo "[setup] ERRO: Arquivo de migração não encontrado: ${MIGRATIONS_FILE}" >&2
  exit 1
fi

echo "[setup] Resetando banco '${DB_NAME}' (DROP/CREATE)..."
PGPASSWORD="${DB_PASSWORD}" psql -h "${DB_HOST}" -p "${DB_PORT}" -U "${DB_USER}" -d postgres -v ON_ERROR_STOP=1 -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='${DB_NAME}' AND pid <> pg_backend_pid();"
PGPASSWORD="${DB_PASSWORD}" psql -h "${DB_HOST}" -p "${DB_PORT}" -U "${DB_USER}" -d postgres -v ON_ERROR_STOP=1 -c "DROP DATABASE IF EXISTS \"${DB_NAME}\";"
PGPASSWORD="${DB_PASSWORD}" psql -h "${DB_HOST}" -p "${DB_PORT}" -U "${DB_USER}" -d postgres -v ON_ERROR_STOP=1 -c "CREATE DATABASE \"${DB_NAME}\";"

# 2) Executa migração (schema) somente se ainda não existir
echo "[setup] Verificando schema..."
if [[ -z "$(PGPASSWORD="${DB_PASSWORD}" psql -h "${DB_HOST}" -p "${DB_PORT}" -U "${DB_USER}" -d "${DB_NAME}" -tAc "SELECT to_regclass('public.company')")" ]]; then
  echo "[setup] Aplicando migrações do arquivo: ${MIGRATIONS_FILE}"
  PGPASSWORD="${DB_PASSWORD}" psql -h "${DB_HOST}" -p "${DB_PORT}" -U "${DB_USER}" -d "${DB_NAME}" -v ON_ERROR_STOP=1 -f "${MIGRATIONS_FILE}"
else
  echo "[setup] Schema já existente. Pulando migrações."
fi

# 3) Seed inicial (idempotente)
echo "[setup] Inserindo dados iniciais (seed)..."

PGPASSWORD="${DB_PASSWORD}" psql -h "${DB_HOST}" -p "${DB_PORT}" -U "${DB_USER}" -d "${DB_NAME}" -v ON_ERROR_STOP=1 -c "
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');
"

PGPASSWORD="${DB_PASSWORD}" psql -h "${DB_HOST}" -p "${DB_PORT}" -U "${DB_USER}" -d "${DB_NAME}" -v ON_ERROR_STOP=1 -c "
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
  );
"

PGPASSWORD="${DB_PASSWORD}" psql -h "${DB_HOST}" -p "${DB_PORT}" -U "${DB_USER}" -d "${DB_NAME}" -v ON_ERROR_STOP=1 -c "
INSERT INTO \"status\" (status_status)
SELECT 'PENDING'
WHERE NOT EXISTS (SELECT 1 FROM \"status\" WHERE status_status = 'PENDING');
"

PGPASSWORD="${DB_PASSWORD}" psql -h "${DB_HOST}" -p "${DB_PORT}" -U "${DB_USER}" -d "${DB_NAME}" -v ON_ERROR_STOP=1 -c "
INSERT INTO \"chain\" (chain_name)
SELECT 'polygon'
WHERE NOT EXISTS (SELECT 1 FROM \"chain\" WHERE chain_name = 'polygon');
"

PGPASSWORD="${DB_PASSWORD}" psql -h "${DB_HOST}" -p "${DB_PORT}" -U "${DB_USER}" -d "${DB_NAME}" -v ON_ERROR_STOP=1 -c "
INSERT INTO \"commodities\" (commodities_name, commodities_flag)
SELECT 'soja', 'a'
WHERE NOT EXISTS (SELECT 1 FROM \"commodities\" WHERE commodities_name = 'soja');
"

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

echo "[setup] Inserindo usuário padrão (${DEFAULT_USER_EMAIL})..."
PGPASSWORD="${DB_PASSWORD}" psql -h "${DB_HOST}" -p "${DB_PORT}" -U "${DB_USER}" -d "${DB_NAME}" -v ON_ERROR_STOP=1 -c "
INSERT INTO \"user\" (
  user_name, user_email, user_password, user_phone, user_address, user_city, user_state, user_zip, user_country,
  user_kyc, 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_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}');
"

# 4) Resumo
echo "[setup] Finalizado com sucesso!"
echo "[setup] Tabelas seed (amostra):"
PGPASSWORD="${DB_PASSWORD}" psql -h "${DB_HOST}" -p "${DB_PORT}" -U "${DB_USER}" -d "${DB_NAME}" -c "
TABLE \"company\";
TABLE \"role\";
TABLE \"status\";
TABLE \"chain\";
TABLE \"user\";
" | sed 's/\x1b\[[0-9;]*m//g'