#!/usr/bin/env bash

set -euo pipefail
shopt -s nullglob

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

# Carrega as variáveis de ambiente do arquivo .env se ele existir
if [[ -f "$ENV_FILE" ]]; then
    set -a
    # shellcheck disable=SC1090
    source "$ENV_FILE"
    set +a
fi

# Fallbacks caso não estejam definidas no .env
DB_HOST="${DB_HOST:-127.0.0.1}"
DB_PORT="${DB_PORT:-5432}"
DB_NAME="${DB_NAME:-}"
DB_USER="${DB_USER:-}"
DB_PASSWORD="${DB_PASSWORD:-${DB_PASS:-}}"

# Validações básicas
if [[ -z "$DB_NAME" ]]; then
    echo "Erro: DB_NAME não está configurado no .env."
    exit 1
fi

if [[ -z "$DB_USER" ]]; then
    echo "Erro: DB_USER não está configurado no .env."
    exit 1
fi

# Exporta a senha temporariamente para o psql não pedir interativamente
export PGPASSWORD="$DB_PASSWORD"

MIGRATION_FILES=("$MIGRATIONS_DIR"/*.sql)

if [[ ${#MIGRATION_FILES[@]} -eq 0 ]]; then
    echo "Erro: nenhuma migration .sql encontrada em $MIGRATIONS_DIR."
    exit 1
fi

echo "Verificando se o banco '$DB_NAME' existe..."

DB_EXISTS="$(psql "host=$DB_HOST port=$DB_PORT user=$DB_USER dbname=postgres" -tAc "SELECT 1 FROM pg_database WHERE datname = '$DB_NAME'")"

if [[ "$DB_EXISTS" != "1" ]]; then
    echo "Banco '$DB_NAME' não existe. Criando..."
    psql "host=$DB_HOST port=$DB_PORT user=$DB_USER dbname=postgres" -v ON_ERROR_STOP=1 -c "CREATE DATABASE \"$DB_NAME\" OWNER \"$DB_USER\""
else
    echo "Banco '$DB_NAME' já existe. Preservando dados existentes."
fi

echo "Garantindo tabela de controle de migrações..."

psql "host=$DB_HOST port=$DB_PORT user=$DB_USER dbname=$DB_NAME" -v ON_ERROR_STOP=1 <<SQL
CREATE TABLE IF NOT EXISTS schema_migrations (
    migration_name TEXT PRIMARY KEY,
    applied_at TIMESTAMP NOT NULL DEFAULT NOW()
);

INSERT INTO schema_migrations (migration_name)
SELECT 'migrations_v1.sql'
WHERE EXISTS (
    SELECT 1
    FROM information_schema.tables
    WHERE table_schema = 'public'
      AND table_name = 'company'
)
AND EXISTS (
    SELECT 1
    FROM information_schema.tables
    WHERE table_schema = 'public'
      AND table_name = 'integration'
)
AND NOT EXISTS (
    SELECT 1
    FROM schema_migrations
    WHERE migration_name = 'migrations_v1.sql'
);
SQL

for migration_file in "${MIGRATION_FILES[@]}"; do
    migration_name="$(basename "$migration_file")"
    already_applied="$(psql "host=$DB_HOST port=$DB_PORT user=$DB_USER dbname=$DB_NAME" -tAc "SELECT 1 FROM schema_migrations WHERE migration_name = '$migration_name'")"

    if [[ "$already_applied" == "1" ]]; then
        echo "Pulando $migration_name (já aplicada)."
        continue
    fi

    echo "Aplicando migração: $migration_name..."
    psql "host=$DB_HOST port=$DB_PORT user=$DB_USER dbname=$DB_NAME" -v ON_ERROR_STOP=1 -f "$migration_file"
    psql "host=$DB_HOST port=$DB_PORT user=$DB_USER dbname=$DB_NAME" -v ON_ERROR_STOP=1 -c "INSERT INTO schema_migrations (migration_name) VALUES ('$migration_name')"
done

echo "--------------------------------------------------------"
echo "Banco de dados '$DB_NAME' preservado e migrações aplicadas com sucesso!"
echo "--------------------------------------------------------"