#!/bin/bash
# Grant Database Permissions via SSH to Proxmox Host

set -e

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

echo "=========================================="
echo "Granting Database Permissions (Remote)"
echo "=========================================="
echo ""
echo "Proxmox Host: $PROXMOX_HOST"
echo "VMID: $VMID"
echo "Database: $DB_NAME"
echo "User: $DB_USER"
echo ""

# Check if we can SSH to Proxmox host
if ! ssh -o ConnectTimeout=5 -o BatchMode=yes root@"$PROXMOX_HOST" exit 2>/dev/null; then
    echo "⚠️  Cannot SSH to Proxmox host ($PROXMOX_HOST)"
    echo "   Please ensure:"
    echo "   1. SSH key is set up for root@$PROXMOX_HOST"
    echo "   2. Host is reachable"
    echo ""
    echo "   Or run manually on Proxmox host:"
    echo "   pct exec $VMID -- bash -c \"su - postgres -c \\\"psql -d $DB_NAME << 'EOF'"
    echo "   GRANT CONNECT ON DATABASE $DB_NAME TO $DB_USER;"
    echo "   GRANT ALL PRIVILEGES ON DATABASE $DB_NAME TO $DB_USER;"
    echo "   ALTER USER $DB_USER CREATEDB;"
    echo "   \\\\c $DB_NAME"
    echo "   GRANT ALL ON SCHEMA public TO $DB_USER;"
    echo "   GRANT CREATE ON SCHEMA public TO $DB_USER;"
    echo "   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO $DB_USER;"
    echo "   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO $DB_USER;"
    echo "   EOF\\\"\""
    exit 1
fi

echo "✅ Connected to Proxmox host"
echo ""

echo "Step 1: Granting database-level permissions..."
ssh root@"$PROXMOX_HOST" "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..."
ssh root@"$PROXMOX_HOST" "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..."
ssh root@"$PROXMOX_HOST" "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"
else
    echo "⚠️  Verification had issues, but permissions may still be granted"
fi

echo ""
echo "=========================================="
echo "✅ Database permissions granted!"
echo "=========================================="