#!/bin/bash
#
# seed.sh - Run db:seed with elevated MySQL privileges
#
# This script temporarily overrides the database connection to use admin
# credentials for operations that require DROP/CREATE permissions.
#
# Usage:
#   ./seed.sh <password>                    # Uses 'root' as username
#   ./seed.sh <username> <password>         # Uses specified username
#
# IMPORTANT: If your password contains special characters like ( ) > < | & ! $
# you must wrap it in single quotes to prevent bash from interpreting them.
#
# Examples:
#   ./seed.sh 'mypassword'
#   ./seed.sh root 'mypassword'
#   ./seed.sh 'p@ss(word)!23'
#

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

printf "${GREEN}=== Database Seeder with Admin Privileges ===${NC}\n\n"

# Parse arguments
if [ $# -eq 0 ]; then
    printf "${YELLOW}Usage:${NC}\n"
    printf "  ./seed.sh '<password>'              # Uses 'root' as username\n"
    printf "  ./seed.sh <username> '<password>'   # Uses specified username\n\n"
    printf "${YELLOW}IMPORTANT:${NC} Wrap password in single quotes if it contains special characters.\n\n"
    printf "${YELLOW}Examples:${NC}\n"
    printf "  ./seed.sh 'mypassword'\n"
    printf "  ./seed.sh root 'mypassword'\n"
    printf "  ./seed.sh 'p@ss(word)!23'\n"
    exit 1
elif [ $# -eq 1 ]; then
    DB_USER="root"
    DB_PASS="$1"
elif [ $# -eq 2 ]; then
    DB_USER="$1"
    DB_PASS="$2"
else
    printf "${RED}Error: Too many arguments.${NC}\n"
    exit 1
fi

printf "${GREEN}Running db:seed...${NC}\n\n"

# Drop all tables directly via mysql CLI before seeding.
# This is necessary because MariaDB 10.11 denies bulk DROP via PDO even with
# correct database-level grants (works fine via the mysql CLI client).
DB_APP_USER=$(grep '^DB_USERNAME' .env | cut -d'=' -f2 | tr -d '"')
DB_APP_PASS=$(grep '^DB_PASSWORD' .env | cut -d'=' -f2 | tr -d '"')
DB_APP_HOST=$(grep '^DB_HOST' .env | cut -d'=' -f2 | tr -d '"')
DB_APP_PORT=$(grep '^DB_PORT' .env | cut -d'=' -f2 | tr -d '"')
DB_NAME=$(grep '^DB_DATABASE' .env | cut -d'=' -f2 | tr -d '"')

# Temporarily grant full privileges so migrations and seeder can run.
# Per-table immutability restrictions will be re-applied at the end of this script.
mysql -h "$DB_APP_HOST" -P "$DB_APP_PORT" -u "$DB_USER" -p"$DB_PASS" -e "
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON \`${DB_NAME}\`.* TO '${DB_APP_USER}'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON \`${DB_NAME}\`.* TO '${DB_APP_USER}'@'127.0.0.1';
FLUSH PRIVILEGES;
" 2>&1
if [ $? -ne 0 ]; then
    printf "${RED}Failed to grant temporary full access. Aborting.${NC}\n"
    exit 1
fi
printf "${GREEN}Temporary full access granted for seeding.${NC}\n"
TABLES=$(mysql -h "$DB_APP_HOST" -P "$DB_APP_PORT" -u "$DB_USER" -p"$DB_PASS" \
    -N -e "SELECT CONCAT('\`',table_name,'\`') FROM information_schema.tables WHERE table_schema='${DB_NAME}';" 2>/dev/null | tr '\n' ',')
if [ -n "$TABLES" ]; then
    TABLES="${TABLES%,}"  # remove trailing comma
    printf "${GREEN}Dropping all tables...${NC}\n"
    mysql -h "$DB_APP_HOST" -P "$DB_APP_PORT" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" \
        -e "SET FOREIGN_KEY_CHECKS=0; DROP TABLE IF EXISTS ${TABLES}; SET FOREIGN_KEY_CHECKS=1;" 2>&1
    if [ $? -ne 0 ]; then
        printf "${RED}Failed to drop tables.${NC}\n"
        exit 1
    fi
    printf "${GREEN}All tables dropped.${NC}\n"
fi

# Run migrations first (fresh DB has no tables)
printf "${GREEN}Running migrations...${NC}\n"
php artisan migrate --force
if [ $? -ne 0 ]; then
    printf "${RED}Migrations failed.${NC}\n"
    exit 1
fi
printf "\n"

# Remove profile photos
rm -rf storage/app/public/profile-photos/*
mkdir -p storage/app/public/profile-photos

# Prevent the profiles:mark-inactive cron from running during seeding by creating
# a maintenance mode flag. Laravel's schedule:run checks for maintenance mode.
printf "${GREEN}Enabling maintenance mode to prevent cron jobs during seeding...${NC}\n"
php artisan down --quiet 2>/dev/null || true

# Grant SELECT on Cyclos source database BEFORE db:seed so the seeder's
# migrate:cyclos prompts can access it. Must happen before db:seed.
printf "\n${GREEN}Cyclos source database...${NC}\n"
printf "${YELLOW}To import a Cyclos dump first, run:${NC}\n"
printf "  mysql -h 127.0.0.1 -u root -p -e \"CREATE DATABASE IF NOT EXISTS cyclos_db CHARACTER SET utf8mb3;\"\n"
printf "  mysql -h 127.0.0.1 -u root -p --force cyclos_db < cyclos_dump.sql\n"
printf "${YELLOW}Note: Use --force to skip non-critical errors (e.g. Cyclos statistics views).${NC}\n"
printf "${YELLOW}Note: Do NOT use GUI tools like Antares or TablePlus — they corrupt binary BLOB data.${NC}\n\n"
CYCLOS_DB=""
while true; do
    printf "Enter the Cyclos source database name (leave empty to skip): "
    read CYCLOS_DB
    if [ -z "$CYCLOS_DB" ]; then
        break
    fi
    # Check if the database exists
    DB_EXISTS=$(mysql -h "$DB_APP_HOST" -P "$DB_APP_PORT" -u "$DB_USER" -p"$DB_PASS" \
        -N -e "SELECT COUNT(*) FROM information_schema.schemata WHERE schema_name='${CYCLOS_DB}';" 2>/dev/null)
    if [ "$DB_EXISTS" = "1" ]; then
        mysql -h "$DB_APP_HOST" -P "$DB_APP_PORT" -u "$DB_USER" -p"$DB_PASS" -e "
GRANT SELECT ON \`${CYCLOS_DB}\`.* TO '${DB_APP_USER}'@'localhost';
GRANT SELECT ON \`${CYCLOS_DB}\`.* TO '${DB_APP_USER}'@'127.0.0.1';
FLUSH PRIVILEGES;
" 2>/dev/null
        printf "${GREEN}SELECT granted on ${CYCLOS_DB}.${NC}\n"
        break
    else
        printf "${RED}Database '${CYCLOS_DB}' not found. Available databases:${NC}\n"
        mysql -h "$DB_APP_HOST" -P "$DB_APP_PORT" -u "$DB_USER" -p"$DB_PASS" \
            -N -e "SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql','performance_schema','sys');" 2>/dev/null | sed 's/^/  - /'
    fi
done

# Run artisan db:seed. If CYCLOS_DB is set, pass it via env so the seeder
# skips the "migrate cyclos?" prompt and uses the already-known DB name.
if [ -n "$CYCLOS_DB" ]; then
    CYCLOS_DB="$CYCLOS_DB" php artisan db:seed
else
    php artisan db:seed
fi

# Register any unregistered models as love reactants
printf "\n${GREEN}Registering unregistered reactants...${NC}\n"
php artisan tinker --execute="
App\Models\Call::whereNull('love_reactant_id')->get()->each(fn(\$c) => \$c->registerAsLoveReactant());
App\Models\Post::whereNull('love_reactant_id')->get()->each(fn(\$p) => \$p->registerAsLoveReactant());
echo 'Done.';
"

# Fix profile-photos directory ownership so web server (www-data) can read files
printf "\n${GREEN}Fixing profile-photos ownership...${NC}\n"
chown -R r:www-data storage/app/public/profile-photos/

# Re-apply transaction immutability restrictions AFTER seeding.
# create-restricted-db-user-safe.sh drops and recreates the user with correct
# per-table grants (no UPDATE/DELETE on transactions/transaction_types).
# It must run after seeding (not before) so the seeder has full access.
# It must run before re-granting the Cyclos SELECT (which it would otherwise remove).
printf "\n${GREEN}Re-applying transaction immutability restrictions...${NC}\n"
printf "Enter the app DB password to recreate the restricted user (or press Enter to skip): "
read -s IMMUTABILITY_PASS
printf "\n"
if [ -n "$IMMUTABILITY_PASS" ]; then
    sudo ./scripts/create-restricted-db-user-safe.sh "$DB_APP_USER" "$IMMUTABILITY_PASS"
    if [ $? -ne 0 ]; then
        printf "${RED}Failed to re-apply immutability restrictions.${NC}\n"
    elif [ -n "$CYCLOS_DB" ]; then
        # Re-grant Cyclos SELECT since create-restricted-db-user-safe.sh recreated the user
        mysql -h "$DB_APP_HOST" -P "$DB_APP_PORT" -u "$DB_USER" -p"$DB_PASS" -e "
GRANT SELECT ON \`${CYCLOS_DB}\`.* TO '${DB_APP_USER}'@'localhost';
GRANT SELECT ON \`${CYCLOS_DB}\`.* TO '${DB_APP_USER}'@'127.0.0.1';
FLUSH PRIVILEGES;
" 2>/dev/null
        printf "${GREEN}SELECT re-granted on ${CYCLOS_DB} after immutability setup.${NC}\n"
    fi
else
    printf "${YELLOW}Skipped. Run manually: sudo ./scripts/create-restricted-db-user-safe.sh${NC}\n"
fi

# Bring application back online
printf "\n${GREEN}Disabling maintenance mode...${NC}\n"
php artisan up --quiet 2>/dev/null || true

printf "\n${GREEN}=== Seeding complete ===${NC}\n"

# If a Cyclos migration was run, offer to verify it
if [ -n "$CYCLOS_DB" ]; then
    printf "\n"
    printf "Run the Cyclos migration verification script? [Y/n]: "
    read RUN_VERIFY
    if [ -z "$RUN_VERIFY" ] || [ "$RUN_VERIFY" = "y" ] || [ "$RUN_VERIFY" = "Y" ]; then
        php artisan verify:cyclos-migration
    else
        printf "${YELLOW}Skipped. Run manually: php artisan verify:cyclos-migration${NC}\n"
    fi
fi