#!/bin/bash
# Grant Database Permissions for dbis user
# Run this on the Proxmox host to grant permissions in the database container

set -e

VMID="${VMID:-10100}"
DB_NAME="${DB_NAME:-dbis_core}"
DB_USER="${DB_USER:-dbis}"

echo "=========================================="
echo "Granting Database Permissions"
echo "=========================================="
echo ""
echo "VMID: $VMID"
echo "Database: $DB_NAME"
echo "User: $DB_USER"
echo ""

# Check if pct command exists (must run on Proxmox host)
if ! command -v pct &> /dev/null; then
    echo "❌ Error: This script must be run on the Proxmox host (pct command not found)"
    echo ""
    echo "Alternative: Run these commands manually:"
    echo "  ssh root@192.168.11.10"
    echo "  pct exec $VMID -- bash"
    echo "  su - postgres -c \"psql -d $DB_NAME\""
    echo ""
    exit 1
fi

# Check if container exists
if ! pct list | grep -q "^\s*$VMID\s"; then
    echo "❌ Error: Container $VMID not found"
    exit 1
fi

echo "Step 1: Granting database-level permissions..."
pct exec "$VMID" -- bash -c "su - postgres -c \"psql -d postgres << 'EOF'
GRANT CONNECT ON DATABASE $DB_NAME TO $DB_USER;
GRANT ALL PRIVILEGES ON DATABASE $DB_NAME TO $DB_USER;
ALTER USER $DB_USER CREATEDB;
EOF\""

if [ $? -ne 0 ]; then
    echo "❌ Failed to grant database-level permissions"
    exit 1
fi

echo "✅ Database-level permissions granted"
echo ""

echo "Step 2: Granting schema-level permissions..."
pct exec "$VMID" -- bash -c "su - postgres -c \"psql -d $DB_NAME << 'EOF'
GRANT ALL ON SCHEMA public TO $DB_USER;
GRANT CREATE ON SCHEMA public TO $DB_USER;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO $DB_USER;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO $DB_USER;
EOF\""

if [ $? -ne 0 ]; then
    echo "❌ Failed to grant schema-level permissions"
    exit 1
fi

echo "✅ Schema-level permissions granted"
echo ""

echo "Step 3: Verifying permissions..."
pct exec "$VMID" -- bash -c "su - postgres -c \"psql -d $DB_NAME -c 'SELECT current_user, current_database();'\"" > /dev/null 2>&1

if [ $? -eq 0 ]; then
    echo "✅ Permissions verified - user $DB_USER can connect to $DB_NAME"
else
    echo "⚠️  Verification had issues, but permissions may still be granted"
fi

echo ""
echo "=========================================="
echo "✅ Database permissions granted!"
echo "=========================================="
echo ""
echo "Next step: Run the migration from your local machine:"
echo "  cd /home/intlc/projects/proxmox/dbis_core"
echo "  ./scripts/run-chart-of-accounts-migration.sh"