#!/bin/bash
# Test Database Setup Script
# Purpose: Create test database and user on 192.168.1.104 for isolated test execution
# Usage: ./scripts/setup-test-db.sh

set -euo pipefail

# Configuration
DB_HOST="192.168.1.104"
SSH_HOST="four"
TEST_DB_NAME="codex_test_db"
TEST_DB_USER="codex_test_user" 
TEST_DB_PASS="TestPass_2024_Secure"

echo "🚀 Setting up test database infrastructure on ${DB_HOST}..."

# Check SSH connectivity
if ! ssh -q -o ConnectTimeout=5 ${SSH_HOST} exit; then
    echo "❌ ERROR: Cannot connect to ${SSH_HOST}. Ensure SSH access is configured."
    echo "   Try: ssh-copy-id ${SSH_HOST}"
    exit 1
fi

echo "✅ SSH connectivity verified"

# Create test database and user
ssh ${SSH_HOST} "sudo -u postgres psql" << EOF
-- Create test user if not exists
DO \$\$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_catalog.pg_user WHERE usename = '${TEST_DB_USER}') THEN
        CREATE USER ${TEST_DB_USER} WITH PASSWORD '${TEST_DB_PASS}';
        RAISE NOTICE 'Created test user: ${TEST_DB_USER}';
    ELSE
        RAISE NOTICE 'Test user already exists: ${TEST_DB_USER}';
    END IF;
END
\$\$;

-- Grant necessary permissions
ALTER USER ${TEST_DB_USER} CREATEDB;
ALTER USER ${TEST_DB_USER} CREATEROLE;
GRANT USAGE ON SCHEMA public TO ${TEST_DB_USER};
GRANT CREATE ON SCHEMA public TO ${TEST_DB_USER};

-- Create template test database if not exists
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '${TEST_DB_NAME}' AND pid <> pg_backend_pid();

DROP DATABASE IF EXISTS ${TEST_DB_NAME};
CREATE DATABASE ${TEST_DB_NAME} 
    WITH OWNER = ${TEST_DB_USER}
         ENCODING = 'UTF8'
         LC_COLLATE = 'en_US.UTF-8'
         LC_CTYPE = 'en_US.UTF-8'
         TEMPLATE = template0;

-- Grant all privileges on test database
GRANT ALL PRIVILEGES ON DATABASE ${TEST_DB_NAME} TO ${TEST_DB_USER};

EOF

echo "✅ Test database and user created successfully"

# Verify test database connectivity
echo "🔍 Verifying test database connectivity..."

if ssh ${SSH_HOST} "PGPASSWORD='${TEST_DB_PASS}' psql -h localhost -U ${TEST_DB_USER} -d ${TEST_DB_NAME} -c 'SELECT version();'" > /dev/null 2>&1; then
    echo "✅ Test database connectivity verified"
else
    echo "❌ ERROR: Cannot connect to test database"
    exit 1
fi

# Setup required extensions
ssh ${SSH_HOST} "PGPASSWORD='${TEST_DB_PASS}' psql -h localhost -U ${TEST_DB_USER} -d ${TEST_DB_NAME}" << EOF
-- Create required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgvector";
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";

-- Verify extensions
SELECT extname, extversion FROM pg_extension WHERE extname IN ('uuid-ossp', 'vector', 'pg_stat_statements');

EOF

echo "✅ Required extensions installed"

# Display success summary
echo ""
echo "🎉 Test Database Setup Complete!"
echo "=================================="
echo "Database Host: ${DB_HOST}"
echo "Database Name: ${TEST_DB_NAME}"
echo "Database User: ${TEST_DB_USER}"
echo "Connection: TEST_DATABASE_URL in .env"
echo ""
echo "Test with: cargo test"
echo "Manual access: ssh ${SSH_HOST} \"PGPASSWORD='${TEST_DB_PASS}' psql -h localhost -U ${TEST_DB_USER} -d ${TEST_DB_NAME}\""
echo ""
echo "⚠️  Note: Test databases will be created/destroyed automatically during test runs"
echo "   This template database provides the foundation for isolated test execution"