#!/bin/bash

# Setup script for local PostgreSQL database
# This script creates the database, user, and applies migrations for local development

set -e  # Exit on error

echo "🔧 Setting up local PostgreSQL database for Codex Memory..."

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color

# Database configuration (matching .env file)
DB_NAME="codex_db"
DB_USER="codex_user"
DB_PASSWORD="MZSfXiLr5uR3QYbRwv2vTzi22SvFkj4a"
DB_HOST="localhost"
DB_PORT="5432"

# Check if PostgreSQL is running
if ! pg_isready -h $DB_HOST -p $DB_PORT > /dev/null 2>&1; then
    echo -e "${RED}❌ PostgreSQL is not running on $DB_HOST:$DB_PORT${NC}"
    echo "Please start PostgreSQL first:"
    echo "  macOS: brew services start postgresql"
    echo "  Linux: sudo systemctl start postgresql"
    exit 1
fi

echo "✅ PostgreSQL is running"

# Function to run SQL as postgres superuser
run_sql() {
    psql -h $DB_HOST -p $DB_PORT -U postgres -c "$1" 2>/dev/null || return 1
}

# Create user if it doesn't exist
echo -n "Creating user '$DB_USER'... "
if run_sql "SELECT 1 FROM pg_user WHERE usename = '$DB_USER'" | grep -q 1; then
    echo -e "${YELLOW}already exists${NC}"
else
    if run_sql "CREATE USER $DB_USER WITH PASSWORD '$DB_PASSWORD'"; then
        echo -e "${GREEN}created${NC}"
    else
        echo -e "${RED}failed${NC}"
        exit 1
    fi
fi

# Create database if it doesn't exist
echo -n "Creating database '$DB_NAME'... "
if run_sql "SELECT 1 FROM pg_database WHERE datname = '$DB_NAME'" | grep -q 1; then
    echo -e "${YELLOW}already exists${NC}"
else
    if run_sql "CREATE DATABASE $DB_NAME OWNER $DB_USER"; then
        echo -e "${GREEN}created${NC}"
    else
        echo -e "${RED}failed${NC}"
        exit 1
    fi
fi

# Grant all privileges
echo -n "Granting privileges... "
if run_sql "GRANT ALL PRIVILEGES ON DATABASE $DB_NAME TO $DB_USER"; then
    echo -e "${GREEN}done${NC}"
else
    echo -e "${RED}failed${NC}"
    exit 1
fi

# Create pgvector extension
echo -n "Creating pgvector extension... "
if psql -h $DB_HOST -p $DB_PORT -U postgres -d $DB_NAME -c "CREATE EXTENSION IF NOT EXISTS vector" 2>/dev/null; then
    echo -e "${GREEN}done${NC}"
else
    echo -e "${YELLOW}may already exist or not available${NC}"
fi

# Apply migrations
echo ""
echo "📝 Applying database migrations..."

# Check if migrations directory exists
MIGRATIONS_DIR="$(dirname "$0")/migrations"
if [ ! -d "$MIGRATIONS_DIR" ]; then
    echo -e "${YELLOW}No migrations directory found at $MIGRATIONS_DIR${NC}"
    echo "Migrations will be applied when the application starts."
else
    # Apply each migration in order
    for migration in $(ls $MIGRATIONS_DIR/*.sql 2>/dev/null | sort); do
        filename=$(basename "$migration")
        echo -n "  Applying $filename... "
        
        if PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -f "$migration" > /dev/null 2>&1; then
            echo -e "${GREEN}done${NC}"
        else
            echo -e "${YELLOW}skipped (may already be applied)${NC}"
        fi
    done
fi

echo ""
echo "✨ Database setup complete!"
echo ""
echo "Connection details:"
echo "  Host:     $DB_HOST"
echo "  Port:     $DB_PORT"
echo "  Database: $DB_NAME"
echo "  User:     $DB_USER"
echo ""
echo "Connection string:"
echo "  postgresql://$DB_USER:$DB_PASSWORD@$DB_HOST:$DB_PORT/$DB_NAME"
echo ""
echo "This matches the DATABASE_URL in your .env file."