#!/bin/bash

# Initialize paths and configuration
CP_HOME="/home/clp"
DB_PATH="${CP_HOME}/htdocs/app/data/db.sq3"
BACKUP_DIR="${CP_HOME}/backups/databases"
LOG_DIR="${CP_HOME}/logs/database"

# Ensure our directories exist
mkdir -p "$BACKUP_DIR" "$LOG_DIR"

# Function to read database configurations from CloudPanel
get_database_config() {
    sqlite3 "$DB_PATH" "
        SELECT
            d.id,
            d.name as db_name,
            d.site_id,
            s.domain_name,
            ds.engine,
            ds.host,
            ds.port,
            du.user_name,
            du.permissions
        FROM database d
        JOIN database_server ds ON d.database_server_id = ds.id
        JOIN site s ON d.site_id = s.id
        JOIN database_user du ON d.id = du.database_id
        WHERE ds.is_active = 1;"
}

# Function to implement database security measures
secure_databases() {
    echo "Implementing database security measures..."

    while IFS='|' read -r db_id db_name site_id domain engine host port user perms; do
        echo "Securing database: $db_name for $domain"

        # Create secure configuration for each database
        case "$engine" in
            "mysql")
                secure_mysql_database "$db_name" "$user" "$perms"
                ;;
            "postgresql")
                secure_postgresql_database "$db_name" "$user" "$perms"
                ;;
            *)
                echo "Unsupported database engine: $engine"
                continue
                ;;
        esac

        # Setup automated backups for this database
        setup_database_backup "$db_name" "$domain" "$engine"
    done < <(get_database_config)
}

# Function to secure MySQL databases
secure_mysql_database() {
    local db_name="$1"
    local user="$2"
    local perms="$3"

    mysql -e "
        -- Remove any excessive privileges
        REVOKE ALL PRIVILEGES ON *.* FROM '$user'@'localhost';

        -- Grant only necessary permissions based on CloudPanel settings
        GRANT $perms ON $db_name.* TO '$user'@'localhost';

        -- Ensure proper security settings
        ALTER USER '$user'@'localhost'
            REQUIRE SSL
            PASSWORD EXPIRE INTERVAL 90 DAY
            FAILED_LOGIN_ATTEMPTS 3
            PASSWORD_LOCK_TIME 2;

        -- Enable binary logging for this database
        SET GLOBAL binlog_do_db = CONCAT(@@binlog_do_db, ',$db_name');

        FLUSH PRIVILEGES;"
}

# Function to setup database backups
setup_database_backup() {
    local db_name="$1"
    local domain="$2"
    local engine="$3"

    # Create backup script for this database
    local backup_script="${BACKUP_DIR}/${domain}-backup.sh"

    cat > "$backup_script" <<EOF
#!/bin/bash

# Backup script for $domain database ($db_name)
TIMESTAMP=\$(date +%Y%m%d_%H%M%S)
BACKUP_PATH="${BACKUP_DIR}/${domain}/\${TIMESTAMP}"
mkdir -p "\$BACKUP_PATH"

# Perform backup based on engine type
case "$engine" in
    "mysql")
        mysqldump --single-transaction \\
                 --quick \\
                 --lock-tables=false \\
                 --routines \\
                 --triggers \\
                 --events \\
                 "$db_name" | gzip > "\$BACKUP_PATH/${db_name}.sql.gz"
        ;;
    "postgresql")
        PGPASSWORD="\$PGPASSWORD" pg_dump \\
            -h localhost \\
            -U "$user" \\
            --clean \\
            --if-exists \\
            "$db_name" | gzip > "\$BACKUP_PATH/${db_name}.sql.gz"
        ;;
esac

# Verify backup integrity
if gunzip -t "\$BACKUP_PATH/${db_name}.sql.gz"; then
    echo "Backup completed successfully for $db_name"
else
    echo "Backup verification failed for $db_name"
    exit 1
fi

# Implement backup rotation (keep last 7 days)
find "${BACKUP_DIR}/${domain}" -type f -mtime +7 -delete
EOF

    # Make backup script executable
    chmod +x "$backup_script"

    # Add to crontab if not already present
    if ! crontab -l | grep -q "$backup_script"; then
        (crontab -l 2>/dev/null; echo "0 2 * * * $backup_script") | crontab -
    fi
}

# Function to monitor database security
monitor_database_security() {
    echo "Setting up database security monitoring..."

    # Create monitoring queries based on engine type
    cat > "${LOG_DIR}/monitor-queries.sql" <<EOF
-- Check for users with excessive privileges
SELECT user, host, privilege_type
FROM information_schema.user_privileges
WHERE privilege_type IN ('SUPER', 'FILE', 'PROCESS');

-- Check for non-local connections
SELECT user, host, db, command, time
FROM information_schema.processlist
WHERE host NOT LIKE 'localhost%';

-- Check for failed login attempts
SELECT * FROM mysql.general_log
WHERE argument LIKE '%Access denied%'
AND event_time > NOW() - INTERVAL 1 HOUR;
EOF

    # Setup periodic monitoring
    cat > "/etc/cron.hourly/db-security-monitor" <<EOF
#!/bin/bash
mysql -N < "${LOG_DIR}/monitor-queries.sql" > "${LOG_DIR}/security-audit-\$(date +%Y%m%d_%H).log"
EOF

    chmod +x "/etc/cron.hourly/db-security-monitor"
}

# Main execution
main() {
    echo "Starting database security configuration..."

    # Create backup of current configurations
    backup_date=$(date +%Y%m%d_%H%M%S)
    mkdir -p "${CP_HOME}/backups/db_security_${backup_date}"

    # Implement security measures
    secure_databases

    # Setup monitoring
    monitor_database_security

    echo "Database security configuration completed successfully"
}

main "$@"