#!/bin/bash
#
# Diagnostic script to check ALTER permission status
#

# Colors
GREEN='\033[0;32m'
BLUE='\033[0;34m'
YELLOW='\033[0;33m'
RED='\033[0;31m'
NC='\033[0m'

echo -e "${BLUE}===========================================================${NC}"
echo -e "${BLUE}   Checking ALTER Permission Status${NC}"
echo -e "${BLUE}===========================================================${NC}"
echo ""

# Get database configuration from Laravel
echo -e "${BLUE}1. Reading Laravel database configuration...${NC}"
DB_USER=$(php artisan tinker --execute="echo config('database.connections.mysql.username');" 2>/dev/null | grep -v ">>>" | grep -v "Psy" | tr -d '\n' | xargs)
DB_NAME=$(php artisan tinker --execute="echo config('database.connections.mysql.database');" 2>/dev/null | grep -v ">>>" | grep -v "Psy" | tr -d '\n' | xargs)
DB_HOST=$(php artisan tinker --execute="echo config('database.connections.mysql.host');" 2>/dev/null | grep -v ">>>" | grep -v "Psy" | tr -d '\n' | xargs)

echo -e "   Database: ${GREEN}$DB_NAME${NC}"
echo -e "   User: ${GREEN}$DB_USER${NC}"
echo -e "   Host: ${GREEN}$DB_HOST${NC}"
echo ""

# Prompt for MySQL credentials with GRANT privileges
echo -e "${BLUE}2. MySQL user with GRANT privileges needed to check grants${NC}"
echo -e "   ${YELLOW}This can be root or a dedicated deployment user${NC}"
read -p "   MySQL username [root]: " MYSQL_DEPLOY_USER
MYSQL_DEPLOY_USER="${MYSQL_DEPLOY_USER:-root}"
read -sp "   MySQL password: " MYSQL_DEPLOY_PASS
echo ""
echo ""

# Check grants for localhost
echo -e "${BLUE}3. Checking grants for '$DB_USER'@'localhost'...${NC}"
GRANTS_LOCALHOST=$(mysql -u "$MYSQL_DEPLOY_USER" -p"$MYSQL_DEPLOY_PASS" -e "SHOW GRANTS FOR '$DB_USER'@'localhost';" 2>/dev/null)
if [ $? -eq 0 ]; then
  echo "$GRANTS_LOCALHOST"
  if echo "$GRANTS_LOCALHOST" | grep -qi "ALTER"; then
    echo -e "   ${GREEN}✓ Has ALTER permission${NC}"
  else
    echo -e "   ${RED}✗ Does NOT have ALTER permission${NC}"
  fi
else
  echo -e "   ${RED}✗ User '$DB_USER'@'localhost' does not exist${NC}"
fi
echo ""

# Check grants for 127.0.0.1
echo -e "${BLUE}4. Checking grants for '$DB_USER'@'127.0.0.1'...${NC}"
GRANTS_127=$(mysql -u "$MYSQL_DEPLOY_USER" -p"$MYSQL_DEPLOY_PASS" -e "SHOW GRANTS FOR '$DB_USER'@'127.0.0.1';" 2>/dev/null)
if [ $? -eq 0 ]; then
  echo "$GRANTS_127"
  if echo "$GRANTS_127" | grep -qi "ALTER"; then
    echo -e "   ${GREEN}✓ Has ALTER permission${NC}"
  else
    echo -e "   ${RED}✗ Does NOT have ALTER permission${NC}"
  fi
else
  echo -e "   ${YELLOW}⚠ User '$DB_USER'@'127.0.0.1' does not exist${NC}"
fi
echo ""

# Check all users matching the username
echo -e "${BLUE}5. Searching for all MySQL users with username '$DB_USER'...${NC}"
ALL_USERS=$(mysql -u "$MYSQL_DEPLOY_USER" -p"$MYSQL_DEPLOY_PASS" -e "SELECT User, Host FROM mysql.user WHERE User='$DB_USER';" 2>/dev/null)
echo "$ALL_USERS"
echo ""

# Test ALTER from Laravel
echo -e "${BLUE}6. Testing ALTER command from Laravel...${NC}"
php artisan tinker --execute="
try {
    DB::statement('ALTER TABLE sessions ADD COLUMN test_check_column VARCHAR(10)');
    echo 'SUCCESS: ALTER command worked' . PHP_EOL;
    DB::statement('ALTER TABLE sessions DROP COLUMN test_check_column');
    echo 'Test column cleaned up' . PHP_EOL;
} catch (\Exception \$e) {
    if (strpos(\$e->getMessage(), 'ALTER command denied') !== false) {
        echo 'DENIED: ALTER command was denied' . PHP_EOL;
    } else {
        echo 'ERROR: ' . \$e->getMessage() . PHP_EOL;
    }
}
exit;
" 2>/dev/null | grep -E "SUCCESS|DENIED|ERROR"
echo ""

echo -e "${BLUE}===========================================================${NC}"
echo -e "${BLUE}   Diagnostic Complete${NC}"
echo -e "${BLUE}===========================================================${NC}"