#!/bin/bash
#
# Create Restricted Database User - Safe Script
#
# This script creates a database user with restricted permissions to enforce
# transaction immutability. The user can:
# - SELECT and INSERT on all tables
# - UPDATE and DELETE on all tables EXCEPT transactions and transaction_types
#
# This enforces financial transaction immutability at the database level.
#
# Usage:
#   ./scripts/create-restricted-db-user-safe.sh [username] [password]
#
# If username/password not provided, will prompt interactively.

set -e  # Exit on error

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

echo -e "${BLUE}========================================${NC}"
echo -e "${BLUE}Create Restricted Database User${NC}"
echo -e "${BLUE}========================================${NC}"
echo ""

# Load database name from .env
if [ ! -f .env ]; then
    echo -e "${RED}Error: .env file not found${NC}"
    exit 1
fi

DB_DATABASE=$(grep "^DB_DATABASE=" .env | cut -d '=' -f2 | sed 's/#.*//' | tr -d '"' | sed 's/^[[:space:]]*//;s/[[:space:]]*$//' | sed 's/\r$//')
DB_HOST=$(grep "^DB_HOST=" .env | cut -d '=' -f2 | sed 's/#.*//' | tr -d '"' | sed 's/^[[:space:]]*//;s/[[:space:]]*$//' | sed 's/\r$//')

# Default to localhost if not set
if [ -z "$DB_HOST" ]; then
    DB_HOST="localhost"
fi

echo -e "${BLUE}Database:${NC} $DB_DATABASE"
echo -e "${BLUE}Host:${NC} $DB_HOST"
echo ""

# Determine authentication method
USE_SUDO=false
ROOT_USER="root"
ROOT_PASSWORD=""

# If running as root, try socket authentication first
if [ "$EUID" -eq 0 ]; then
    echo -e "${YELLOW}Running as root user, testing socket authentication...${NC}"
    if mysql -e "SELECT 1;" &> /dev/null; then
        echo -e "${GREEN}✓ Socket authentication successful${NC}"
        USE_SUDO=true
    else
        echo -e "${YELLOW}Socket authentication failed, falling back to credential-based auth${NC}"
    fi
fi

# If not using socket auth, get credentials
if [ "$USE_SUDO" = false ]; then
    echo ""
    echo -e "${YELLOW}Please provide MySQL root credentials to create the restricted user:${NC}"
    read -p "Root username (default: root): " ROOT_USER_INPUT
    ROOT_USER=${ROOT_USER_INPUT:-root}

    read -s -p "Root password: " ROOT_PASSWORD
    echo ""

    # Test root connection
    echo -e "${YELLOW}Testing database connection...${NC}"
    if ! MYSQL_PWD="$ROOT_PASSWORD" mysql -h"$DB_HOST" -u"$ROOT_USER" -e "SELECT 1;" 2>/dev/null; then
        echo -e "${RED}Error: Cannot connect to database with provided credentials${NC}"
        echo ""
        echo -e "${YELLOW}Troubleshooting:${NC}"
        echo -e "  1. Check your MySQL root password"
        echo -e "  2. Verify MySQL is running: ${BLUE}systemctl status mysql${NC}"
        echo -e "  3. Check MySQL is listening on ${DB_HOST}: ${BLUE}netstat -tlnp | grep mysql${NC}"
        echo -e "  4. Verify user '${ROOT_USER}' can connect from '${DB_HOST}'"
        echo ""
        echo -e "${YELLOW}Debug: Attempting to connect as:${NC}"
        echo -e "  User: ${ROOT_USER}"
        echo -e "  Host: ${DB_HOST}"
        exit 1
    fi
    echo -e "${GREEN}✓ Database connection successful${NC}"
fi

echo ""

# Check if user has CREATE USER privilege
echo -e "${YELLOW}Checking for CREATE USER privilege...${NC}"
if [ "$USE_SUDO" = true ]; then
    HAS_CREATE_USER=$(mysql -N -e "SELECT COUNT(*) FROM information_schema.user_privileges WHERE PRIVILEGE_TYPE='CREATE USER' AND GRANTEE LIKE '%root%';" 2>/dev/null || echo "0")
else
    HAS_CREATE_USER=$(MYSQL_PWD="$ROOT_PASSWORD" mysql -h"$DB_HOST" -u"$ROOT_USER" -N -e "SELECT COUNT(*) FROM information_schema.user_privileges WHERE PRIVILEGE_TYPE='CREATE USER' AND GRANTEE LIKE '%${ROOT_USER}%';" 2>/dev/null || echo "0")
fi

if [ "$HAS_CREATE_USER" = "0" ]; then
    echo -e "${RED}Error: User '${ROOT_USER}' does not have CREATE USER privilege${NC}"
    echo ""
    echo -e "${YELLOW}Solutions:${NC}"
    echo ""
    echo -e "  ${BLUE}Option 1: Use sudo mysql (recommended for servers)${NC}"
    echo -e "  If your server uses socket authentication for root, run:"
    echo -e "  ${GREEN}sudo ./scripts/create-restricted-db-user-safe.sh${NC}"
    echo ""
    echo -e "  ${BLUE}Option 2: Grant CREATE USER privilege${NC}"
    echo -e "  Connect as a user with GRANT privilege and run:"
    echo -e "  ${GREEN}GRANT CREATE USER ON *.* TO '${ROOT_USER}'@'${DB_HOST}';${NC}"
    echo -e "  ${GREEN}FLUSH PRIVILEGES;${NC}"
    echo ""
    echo -e "  ${BLUE}Option 3: Use MySQL root user${NC}"
    echo -e "  On Ubuntu/Debian servers, try running without password:"
    echo -e "  ${GREEN}sudo mysql -u root${NC}"
    echo -e "  Then manually run the SQL commands from:"
    echo -e "  ${GREEN}scripts/create-restricted-db-user.sql${NC}"
    echo ""
    exit 1
fi
echo -e "${GREEN}✓ CREATE USER privilege confirmed${NC}"
echo ""

# Get new user details
if [ -z "$1" ]; then
    read -p "Enter new restricted username (default: ${DB_DATABASE}_dev): " NEW_USER
    NEW_USER=${NEW_USER:-${DB_DATABASE}_dev}
else
    NEW_USER="$1"
fi

if [ -z "$2" ]; then
    echo -e "${YELLOW}Generate strong password for ${NEW_USER}? (y/n)${NC}"
    read -p "> " GENERATE_PASSWORD

    if [[ "$GENERATE_PASSWORD" =~ ^[Yy]$ ]]; then
        # Generate strong random password
        NEW_PASSWORD=$(openssl rand -base64 32 | tr -d "=+/" | cut -c1-32)
        echo -e "${GREEN}Generated password: ${NEW_PASSWORD}${NC}"
    else
        read -s -p "Enter password for ${NEW_USER}: " NEW_PASSWORD
        echo ""
        read -s -p "Confirm password: " NEW_PASSWORD_CONFIRM
        echo ""

        if [ "$NEW_PASSWORD" != "$NEW_PASSWORD_CONFIRM" ]; then
            echo -e "${RED}Error: Passwords do not match${NC}"
            exit 1
        fi
    fi
else
    NEW_PASSWORD="$2"
fi

echo ""
echo -e "${BLUE}Creating user:${NC} ${NEW_USER}@${DB_HOST}"
echo -e "${BLUE}For database:${NC} ${DB_DATABASE}"
echo ""

# Check if user already exists
if [ "$USE_SUDO" = true ]; then
    USER_EXISTS=$(mysql -N -e "SELECT COUNT(*) FROM mysql.user WHERE user='$NEW_USER' AND host='$DB_HOST';" 2>/dev/null || echo "0")
else
    USER_EXISTS=$(MYSQL_PWD="$ROOT_PASSWORD" mysql -h"$DB_HOST" -u"$ROOT_USER" -N -e "SELECT COUNT(*) FROM mysql.user WHERE user='$NEW_USER' AND host='$DB_HOST';" 2>/dev/null || echo "0")
fi

if [ "$USER_EXISTS" != "0" ]; then
    echo -e "${YELLOW}Warning: User '${NEW_USER}'@'${DB_HOST}' already exists${NC}"
    read -p "Drop and recreate user? (y/n): " RECREATE

    if [[ "$RECREATE" =~ ^[Yy]$ ]]; then
        echo -e "${YELLOW}Dropping existing user...${NC}"
        if [ "$USE_SUDO" = true ]; then
            mysql <<EOF
DROP USER IF EXISTS '${NEW_USER}'@'${DB_HOST}';
DROP USER IF EXISTS '${NEW_USER}'@'localhost';
DROP USER IF EXISTS '${NEW_USER}'@'127.0.0.1';
FLUSH PRIVILEGES;
EOF
        else
            MYSQL_PWD="$ROOT_PASSWORD" mysql -h"$DB_HOST" -u"$ROOT_USER" <<EOF
DROP USER IF EXISTS '${NEW_USER}'@'${DB_HOST}';
DROP USER IF EXISTS '${NEW_USER}'@'localhost';
DROP USER IF EXISTS '${NEW_USER}'@'127.0.0.1';
FLUSH PRIVILEGES;
EOF
        fi
        echo -e "${GREEN}✓ Existing user dropped${NC}"
    else
        echo "Exiting without changes"
        exit 0
    fi
fi

echo ""
echo -e "${YELLOW}Creating restricted database user...${NC}"
echo ""

# Get all tables
if [ "$USE_SUDO" = true ]; then
    ALL_TABLES=$(mysql -N -e "SHOW TABLES FROM ${DB_DATABASE};" | tr '\n' ' ')
else
    ALL_TABLES=$(MYSQL_PWD="$ROOT_PASSWORD" mysql -h"$DB_HOST" -u"$ROOT_USER" -N -e "SHOW TABLES FROM ${DB_DATABASE};" | tr '\n' ' ')
fi

# Count tables
TABLE_COUNT=$(echo "$ALL_TABLES" | wc -w)

# Immutable tables (cannot UPDATE or DELETE)
IMMUTABLE_TABLES="transactions transaction_types"

# Count immutable tables
IMMUTABLE_COUNT=$(echo "$IMMUTABLE_TABLES" | wc -w)

# Calculate mutable tables count
MUTABLE_COUNT=$((TABLE_COUNT - IMMUTABLE_COUNT))

echo -e "${BLUE}Total tables:${NC} $TABLE_COUNT"
echo -e "${BLUE}Immutable tables:${NC} $IMMUTABLE_COUNT (transactions, transaction_types)"
echo -e "${BLUE}Mutable tables:${NC} $MUTABLE_COUNT"
echo ""

# Create SQL script
SQL_SCRIPT=$(cat <<EOF
-- Create restricted database user for ${DB_DATABASE}
-- This user enforces transaction immutability at the database level

-- Create user for all common connection types
CREATE USER IF NOT EXISTS '${NEW_USER}'@'${DB_HOST}' IDENTIFIED BY '${NEW_PASSWORD}';
CREATE USER IF NOT EXISTS '${NEW_USER}'@'localhost' IDENTIFIED BY '${NEW_PASSWORD}';
CREATE USER IF NOT EXISTS '${NEW_USER}'@'127.0.0.1' IDENTIFIED BY '${NEW_PASSWORD}';

-- Grant SELECT and INSERT on all tables (basic read/write access)
GRANT SELECT, INSERT ON ${DB_DATABASE}.* TO '${NEW_USER}'@'${DB_HOST}';
GRANT SELECT, INSERT ON ${DB_DATABASE}.* TO '${NEW_USER}'@'localhost';
GRANT SELECT, INSERT ON ${DB_DATABASE}.* TO '${NEW_USER}'@'127.0.0.1';

-- Grant UPDATE and DELETE on each mutable table
EOF
)

# Add UPDATE/DELETE grants for each table except immutable ones
for table in $ALL_TABLES; do
    # Skip immutable tables
    if [[ " $IMMUTABLE_TABLES " =~ " $table " ]]; then
        continue
    fi

    SQL_SCRIPT+=$(cat <<EOF

GRANT UPDATE, DELETE ON ${DB_DATABASE}.${table} TO '${NEW_USER}'@'${DB_HOST}';
GRANT UPDATE, DELETE ON ${DB_DATABASE}.${table} TO '${NEW_USER}'@'localhost';
GRANT UPDATE, DELETE ON ${DB_DATABASE}.${table} TO '${NEW_USER}'@'127.0.0.1';
EOF
)
done

SQL_SCRIPT+=$(cat <<EOF


-- Apply permission changes
FLUSH PRIVILEGES;

-- Display created user
SELECT
    'User created successfully' as status,
    '${NEW_USER}' as username,
    GROUP_CONCAT(DISTINCT host) as hosts
FROM mysql.user
WHERE user = '${NEW_USER}';
EOF
)

# Execute SQL script
echo -e "${YELLOW}Executing SQL commands...${NC}"
if [ "$USE_SUDO" = true ]; then
    echo "$SQL_SCRIPT" | mysql
else
    echo "$SQL_SCRIPT" | MYSQL_PWD="$ROOT_PASSWORD" mysql -h"$DB_HOST" -u"$ROOT_USER"
fi

if [ $? -eq 0 ]; then
    echo ""
    echo -e "${GREEN}========================================${NC}"
    echo -e "${GREEN}✓ User created successfully!${NC}"
    echo -e "${GREEN}========================================${NC}"
    echo ""
    echo -e "${BLUE}User Details:${NC}"
    echo -e "  Username: ${GREEN}${NEW_USER}${NC}"
    echo -e "  Password: ${GREEN}${NEW_PASSWORD}${NC}"
    echo -e "  Database: ${GREEN}${DB_DATABASE}${NC}"
    echo -e "  Hosts: ${GREEN}${DB_HOST}, localhost, 127.0.0.1${NC}"
    echo ""
    echo -e "${BLUE}Permissions:${NC}"
    echo -e "  ${GREEN}✓${NC} SELECT, INSERT on ${TABLE_COUNT} tables"
    echo -e "  ${GREEN}✓${NC} UPDATE, DELETE on ${MUTABLE_COUNT} mutable tables"
    echo -e "  ${RED}✗${NC} UPDATE, DELETE on ${IMMUTABLE_COUNT} immutable tables (transactions, transaction_types)"
    echo ""
    echo -e "${YELLOW}Next Steps:${NC}"
    echo ""
    echo -e "1. ${BLUE}Backup your current .env file:${NC}"
    echo -e "   cp .env .env.backup-\$(date +'%Y%m%d-%H%M%S')"
    echo ""
    echo -e "2. ${BLUE}Update your .env file:${NC}"
    echo -e "   DB_USERNAME=${NEW_USER}"
    echo -e "   DB_PASSWORD=${NEW_PASSWORD}"
    echo ""
    echo -e "3. ${BLUE}Clear Laravel configuration cache:${NC}"
    echo -e "   php artisan config:clear"
    echo ""
    echo -e "4. ${BLUE}Test the connection:${NC}"
    echo -e "   php artisan tinker --execute=\"DB::connection()->getPdo();\""
    echo ""
    echo -e "5. ${BLUE}Verify transaction immutability:${NC}"
    echo -e "   ./scripts/test-transaction-immutability.sh"
    echo ""
    echo -e "${YELLOW}IMPORTANT:${NC}"
    echo -e "  • For database migrations, use root credentials:"
    echo -e "    DB_USERNAME=root php artisan migrate"
    echo -e "  • Keep root credentials secure and separate"
    echo -e "  • Save the new user credentials securely"
    echo ""

    # Save credentials to a secure file
    CREDENTIALS_FILE=".credentials-${NEW_USER}"
    cat > "$CREDENTIALS_FILE" <<EOC
# Database credentials for ${NEW_USER}
# Created: $(date)
# Database: ${DB_DATABASE}

DB_USERNAME=${NEW_USER}
DB_PASSWORD=${NEW_PASSWORD}
EOC

    chmod 600 "$CREDENTIALS_FILE"
    echo -e "${GREEN}✓ Credentials saved to: ${CREDENTIALS_FILE}${NC}"
    echo -e "${YELLOW}  (This file has been created with restricted permissions: 600)${NC}"
    echo ""
else
    echo ""
    echo -e "${RED}========================================${NC}"
    echo -e "${RED}✗ Error creating user${NC}"
    echo -e "${RED}========================================${NC}"
    echo ""
    exit 1
fi