#!/bin/bash

# Script to test transaction immutability on active database (from .env)
# This script safely tests database permissions without altering real data
#
# Tests:
# 1. Can we INSERT into transactions? (should be allowed)
# 2. Can we UPDATE transactions? (should be DENIED)
# 3. Can we DELETE transactions? (should be DENIED)
#
# Safety: Uses database transactions with ROLLBACK to prevent any data changes

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}Transaction Immutability Test${NC}"
echo -e "${BLUE}========================================${NC}"
echo ""

# Get database credentials 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_USERNAME=$(grep "^DB_USERNAME=" .env | cut -d '=' -f2 | sed 's/#.*//' | tr -d '"' | sed 's/^[[:space:]]*//;s/[[:space:]]*$//' | sed 's/\r$//')
DB_PASSWORD=$(grep "^DB_PASSWORD=" .env | cut -d '=' -f2- | sed 's/#.*//' | sed 's/^"//' | sed 's/"$//' | 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}User:${NC} $DB_USERNAME"
echo -e "${BLUE}Host:${NC} $DB_HOST"
echo ""

# Check if mysql command is available
if ! command -v mysql &> /dev/null; then
    echo -e "${RED}Error: mysql command not found${NC}"
    echo "Please install mysql-client"
    exit 1
fi

# Test database connection
echo -e "${YELLOW}Testing database connection...${NC}"
if ! MYSQL_PWD="$DB_PASSWORD" mysql -h"$DB_HOST" -u"$DB_USERNAME" "$DB_DATABASE" -e "SELECT 1;" 2>/dev/null; then
    echo -e "${RED}Error: Cannot connect to database${NC}"
    echo -e "${YELLOW}Debug info:${NC}"
    echo -e "  Database: $DB_DATABASE"
    echo -e "  Username: $DB_USERNAME"
    echo -e "  Host: $DB_HOST"
    exit 1
fi
echo -e "${GREEN}✓ Database connection successful${NC}"
echo ""

# Get sample account IDs for testing
echo -e "${YELLOW}Getting sample account IDs...${NC}"
ACCOUNT_IDS=$(MYSQL_PWD="$DB_PASSWORD" mysql -h"$DB_HOST" -u"$DB_USERNAME" "$DB_DATABASE" -N -e "SELECT id FROM accounts LIMIT 2;")
FROM_ACCOUNT=$(echo "$ACCOUNT_IDS" | head -n 1)
TO_ACCOUNT=$(echo "$ACCOUNT_IDS" | tail -n 1)

if [ -z "$FROM_ACCOUNT" ] || [ -z "$TO_ACCOUNT" ]; then
    echo -e "${RED}Error: Could not find sample accounts${NC}"
    exit 1
fi

echo -e "${GREEN}✓ Using account IDs: $FROM_ACCOUNT → $TO_ACCOUNT${NC}"
echo ""

# Create a temporary test transaction ID variable
TEST_TRANSACTION_ID=""

# ====================
# TEST 1: INSERT (should be ALLOWED)
# ====================
echo -e "${BLUE}========================================${NC}"
echo -e "${BLUE}TEST 1: INSERT Permission${NC}"
echo -e "${BLUE}========================================${NC}"

MYSQL_PWD="$DB_PASSWORD" mysql -h"$DB_HOST" -u"$DB_USERNAME" "$DB_DATABASE" <<EOF
START TRANSACTION;

-- Try to insert a test transaction
INSERT INTO transactions (
    from_account_id,
    to_account_id,
    amount,
    description,
    transaction_type_id,
    created_at,
    updated_at
) VALUES (
    $FROM_ACCOUNT,
    $TO_ACCOUNT,
    1,
    'IMMUTABILITY TEST - WILL BE ROLLED BACK',
    1,
    NOW(),
    NOW()
);

-- Get the ID of the inserted test transaction
SELECT @test_id := LAST_INSERT_ID();

-- Display the test transaction
SELECT id, from_account_id, to_account_id, amount, description
FROM transactions
WHERE id = @test_id;

-- ROLLBACK to prevent any data changes
ROLLBACK;

SELECT 'Transaction ROLLED BACK - no data was changed' AS status;
EOF

INSERT_RESULT=$?

if [ $INSERT_RESULT -eq 0 ]; then
    echo -e "${GREEN}✓ INSERT permission: ALLOWED${NC}"
    echo -e "${GREEN}  Database user CAN create new transactions${NC}"
else
    echo -e "${RED}✗ INSERT permission: DENIED${NC}"
    echo -e "${RED}  Database user CANNOT create transactions${NC}"
fi
echo ""

# ====================
# TEST 2: UPDATE (should be DENIED)
# ====================
echo -e "${BLUE}========================================${NC}"
echo -e "${BLUE}TEST 2: UPDATE Permission${NC}"
echo -e "${BLUE}========================================${NC}"

# Get an existing transaction ID
EXISTING_ID=$(MYSQL_PWD="$DB_PASSWORD" mysql -h"$DB_HOST" -u"$DB_USERNAME" "$DB_DATABASE" -N -e "SELECT id FROM transactions ORDER BY id DESC LIMIT 1;")

if [ -z "$EXISTING_ID" ]; then
    echo -e "${YELLOW}No existing transactions found, skipping UPDATE test${NC}"
    UPDATE_RESULT=1
else
    echo -e "Testing UPDATE on transaction ID: $EXISTING_ID"

    # Get original amount
    ORIGINAL_AMOUNT=$(MYSQL_PWD="$DB_PASSWORD" mysql -h"$DB_HOST" -u"$DB_USERNAME" "$DB_DATABASE" -N -e "SELECT amount FROM transactions WHERE id = $EXISTING_ID;")
    echo -e "Original amount: $ORIGINAL_AMOUNT"

    # Try to update (wrapped in transaction for safety)
    MYSQL_PWD="$DB_PASSWORD" mysql -h"$DB_HOST" -u"$DB_USERNAME" "$DB_DATABASE" <<EOF 2>&1 | tee /tmp/update_test.log
START TRANSACTION;

-- Try to update an existing transaction
UPDATE transactions
SET amount = 99999, description = 'IMMUTABILITY TEST - SHOULD BE BLOCKED'
WHERE id = $EXISTING_ID;

-- Check if update succeeded
SELECT amount, description
FROM transactions
WHERE id = $EXISTING_ID;

-- ROLLBACK for safety
ROLLBACK;
EOF

    UPDATE_RESULT=${PIPESTATUS[0]}

    # Verify the transaction was not modified
    CURRENT_AMOUNT=$(MYSQL_PWD="$DB_PASSWORD" mysql -h"$DB_HOST" -u"$DB_USERNAME" "$DB_DATABASE" -N -e "SELECT amount FROM transactions WHERE id = $EXISTING_ID;")

    if [ "$CURRENT_AMOUNT" != "$ORIGINAL_AMOUNT" ]; then
        echo -e "${RED}✗ UPDATE permission: ALLOWED (CRITICAL SECURITY ISSUE)${NC}"
        echo -e "${RED}  Transaction amount was changed from $ORIGINAL_AMOUNT to $CURRENT_AMOUNT${NC}"
        echo -e "${RED}  ⚠️  TRANSACTIONS ARE MUTABLE - THIS IS A CRITICAL FINANCIAL SECURITY ISSUE${NC}"
    elif grep -q "denied" /tmp/update_test.log 2>/dev/null; then
        echo -e "${GREEN}✓ UPDATE permission: DENIED${NC}"
        echo -e "${GREEN}  Database user CANNOT modify transactions (secure)${NC}"
    else
        # Update command succeeded but amount unchanged (transaction rolled back)
        echo -e "${YELLOW}⚠ UPDATE permission: ALLOWED but transaction rolled back${NC}"
        echo -e "${YELLOW}  Database user HAS UPDATE permission (potential security issue)${NC}"
        echo -e "${YELLOW}  Data was not changed due to ROLLBACK${NC}"
    fi
fi
echo ""

# ====================
# TEST 3: DELETE (should be DENIED)
# ====================
echo -e "${BLUE}========================================${NC}"
echo -e "${BLUE}TEST 3: DELETE Permission${NC}"
echo -e "${BLUE}========================================${NC}"

if [ -z "$EXISTING_ID" ]; then
    echo -e "${YELLOW}No existing transactions found, skipping DELETE test${NC}"
    DELETE_RESULT=1
else
    echo -e "Testing DELETE on transaction ID: $EXISTING_ID"

    # Verify transaction exists
    EXISTS_BEFORE=$(MYSQL_PWD="$DB_PASSWORD" mysql -h"$DB_HOST" -u"$DB_USERNAME" "$DB_DATABASE" -N -e "SELECT COUNT(*) FROM transactions WHERE id = $EXISTING_ID;")
    echo -e "Transaction exists: $EXISTS_BEFORE"

    # Try to delete (wrapped in transaction for safety)
    MYSQL_PWD="$DB_PASSWORD" mysql -h"$DB_HOST" -u"$DB_USERNAME" "$DB_DATABASE" <<EOF 2>&1 | tee /tmp/delete_test.log
START TRANSACTION;

-- Try to delete an existing transaction
DELETE FROM transactions WHERE id = $EXISTING_ID;

-- Check if delete succeeded
SELECT COUNT(*) as remaining
FROM transactions
WHERE id = $EXISTING_ID;

-- ROLLBACK for safety
ROLLBACK;
EOF

    DELETE_RESULT=${PIPESTATUS[0]}

    # Verify the transaction still exists
    EXISTS_AFTER=$(MYSQL_PWD="$DB_PASSWORD" mysql -h"$DB_HOST" -u"$DB_USERNAME" "$DB_DATABASE" -N -e "SELECT COUNT(*) FROM transactions WHERE id = $EXISTING_ID;")

    if [ "$EXISTS_AFTER" != "$EXISTS_BEFORE" ]; then
        echo -e "${RED}✗ DELETE permission: ALLOWED (CRITICAL SECURITY ISSUE)${NC}"
        echo -e "${RED}  Transaction was deleted${NC}"
        echo -e "${RED}  ⚠️  TRANSACTIONS CAN BE DELETED - THIS IS A CRITICAL FINANCIAL SECURITY ISSUE${NC}"
    elif grep -q "denied" /tmp/delete_test.log 2>/dev/null; then
        echo -e "${GREEN}✓ DELETE permission: DENIED${NC}"
        echo -e "${GREEN}  Database user CANNOT delete transactions (secure)${NC}"
    else
        # Delete command succeeded but transaction still exists (rolled back)
        echo -e "${YELLOW}⚠ DELETE permission: ALLOWED but transaction rolled back${NC}"
        echo -e "${YELLOW}  Database user HAS DELETE permission (potential security issue)${NC}"
        echo -e "${YELLOW}  Data was not changed due to ROLLBACK${NC}"
    fi
fi
echo ""

# ====================
# FINAL SUMMARY
# ====================
echo -e "${BLUE}========================================${NC}"
echo -e "${BLUE}SUMMARY${NC}"
echo -e "${BLUE}========================================${NC}"
echo ""

ISSUES_FOUND=0

if [ $INSERT_RESULT -eq 0 ]; then
    echo -e "${GREEN}✓ INSERT: ALLOWED (expected)${NC}"
else
    echo -e "${RED}✗ INSERT: DENIED (unexpected - should be allowed)${NC}"
    ISSUES_FOUND=$((ISSUES_FOUND + 1))
fi

# Check UPDATE test results
if grep -q "denied" /tmp/update_test.log 2>/dev/null; then
    echo -e "${GREEN}✓ UPDATE: DENIED (expected - secure)${NC}"
elif [ $UPDATE_RESULT -ne 0 ]; then
    echo -e "${GREEN}✓ UPDATE: DENIED (expected - secure)${NC}"
else
    echo -e "${RED}✗ UPDATE: ALLOWED (security issue)${NC}"
    ISSUES_FOUND=$((ISSUES_FOUND + 1))
fi

# Check DELETE test results
if grep -q "denied" /tmp/delete_test.log 2>/dev/null; then
    echo -e "${GREEN}✓ DELETE: DENIED (expected - secure)${NC}"
elif [ $DELETE_RESULT -ne 0 ]; then
    echo -e "${GREEN}✓ DELETE: DENIED (expected - secure)${NC}"
else
    echo -e "${RED}✗ DELETE: ALLOWED (security issue)${NC}"
    ISSUES_FOUND=$((ISSUES_FOUND + 1))
fi

echo ""

if [ $ISSUES_FOUND -eq 0 ]; then
    echo -e "${GREEN}========================================${NC}"
    echo -e "${GREEN}✓ ALL TESTS PASSED${NC}"
    echo -e "${GREEN}Transaction immutability is properly enforced${NC}"
    echo -e "${GREEN}========================================${NC}"
else
    echo -e "${RED}========================================${NC}"
    echo -e "${RED}✗ $ISSUES_FOUND SECURITY ISSUE(S) FOUND${NC}"
    echo -e "${RED}Transaction immutability is NOT properly enforced${NC}"
    echo -e "${RED}========================================${NC}"
    echo ""
    echo -e "${YELLOW}Recommended Fix:${NC}"
    echo -e "${YELLOW}REVOKE UPDATE, DELETE ON $DB_DATABASE.transactions FROM '$DB_USERNAME'@'$DB_HOST';${NC}"
    echo -e "${YELLOW}FLUSH PRIVILEGES;${NC}"
fi

echo ""
echo -e "${BLUE}Note: All tests used database transactions with ROLLBACK${NC}"
echo -e "${BLUE}No actual data was modified in the database${NC}"

# Cleanup
rm -f /tmp/update_test.log /tmp/delete_test.log

exit $ISSUES_FOUND