#!/usr/bin/env bash
set -euo pipefail

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
NODE_ID="669427"
NODE_ROLE="ENGINE"
DB_NAME="engine"
DB_ADMIN_USER="root"
DB_HOST="localhost"
DB_PORT="3306"
REPORT_PORT="8301"
WG_MESH_IP="10.0.1.6"
LOG_FILE="/var/log/engine-db-setup.log"
SECRETS_FILE="/etc/engine/.dbsecrets"

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

DRY_RUN=0
SKIP_USERS=0
SKIP_SEED=0
PW_SOURCE="env"
REPORT_ONLY=0

while [[ $# -gt 0 ]]; do
    case "$1" in
        --passwords-from-env)    PW_SOURCE="env"; shift ;;
        --passwords-from-file)   PW_SOURCE="file"; shift ;;
        --dry-run)               DRY_RUN=1; shift ;;
        --skip-users)            SKIP_USERS=1; shift ;;
        --skip-seed)             SKIP_SEED=1; shift ;;
        --report-only)           REPORT_ONLY=1; shift ;;
        -h|--help)
            echo "Usage: $0 [options]"
            echo "  --passwords-from-env   Read passwords from env vars (default)"
            echo "  --passwords-from-file  Read passwords from ${SECRETS_FILE}"
            echo "  --dry-run              Show what would be done without executing"
            echo "  --skip-users           Skip user creation"
            echo "  --skip-seed            Skip seed data insertion"
            echo "  --report-only          Only send completion report"
            exit 0
            ;;
        *) echo "Unknown option: $1"; exit 1 ;;
    esac
done

log() {
    local level="$1"
    local message="$2"
    local timestamp
    timestamp="$(date -Iseconds)"
    echo -e "[${timestamp}] [${level}] ${message}" | tee -a "$LOG_FILE"
}

log_info()  { log "INFO"  "$1"; }
log_warn()  { log "WARN"  "${YELLOW}$1${NC}"; }
log_error() { log "ERROR" "${RED}$1${NC}"; }
log_ok()    { log "OK"    "${GREEN}$1${NC}"; }

load_passwords() {
    if [[ "$PW_SOURCE" == "env" ]]; then
        ENGINE_READ_PASSWORD="${ENGINE_READ_PASSWORD:-$(openssl rand -base64 32)}"
        ENGINE_WRITE_PASSWORD="${ENGINE_WRITE_PASSWORD:-$(openssl rand -base64 32)}"
        ENGINE_ADMIN_PASSWORD="${ENGINE_ADMIN_PASSWORD:-$(openssl rand -base64 32)}"
    elif [[ "$PW_SOURCE" == "file" ]]; then
        if [[ ! -f "$SECRETS_FILE" ]]; then
            log_error "Secrets file not found: $SECRETS_FILE"
            exit 1
        fi
        source "$SECRETS_FILE"
    fi
    export ENGINE_READ_PASSWORD
    export ENGINE_WRITE_PASSWORD
    export ENGINE_ADMIN_PASSWORD
}

preflight_checks() {
    log_info "Running pre-flight checks..."

    if ! systemctl is-active --quiet mariadb 2>/dev/null && ! systemctl is-active --quiet mysql 2>/dev/null; then
        log_error "MariaDB/MySQL is not running"
        exit 1
    fi
    log_ok "Database server is running"

    if ! command -v mysql &>/dev/null; then
        log_error "mysql client not found"
        exit 1
    fi
    log_ok "mysql client available"

    if ! mysql -u"$DB_ADMIN_USER" -e "SELECT 1" &>/dev/null; then
        log_error "Cannot connect to MariaDB as root"
        exit 1
    fi
    log_ok "Database root access verified"

    for file in "$SCRIPT_DIR/00-engine.sql" "$SCRIPT_DIR/01-store.sql" "$SCRIPT_DIR/02-additions.sql" "$SCRIPT_DIR/03-procedures.sql" "$SCRIPT_DIR/04-triggers.sql"; do
        if [[ ! -f "$file" ]]; then
            log_error "Required SQL file missing: $file"
            exit 1
        fi
    done
    log_ok "All required SQL files present"

    local engine_check
    engine_check=$(mysql -u"$DB_ADMIN_USER" -e "SHOW ENGINES WHERE Engine='InnoDB' AND Support IN ('YES','DEFAULT');" 2>/dev/null | wc -l)
    if [[ "$engine_check" -lt 2 ]]; then
        log_error "InnoDB storage engine not available"
        exit 1
    fi
    log_ok "InnoDB engine available"

    local log_dir
    log_dir="$(dirname "$LOG_FILE")"
    if [[ ! -d "$log_dir" ]]; then
        mkdir -p "$log_dir"
        log_info "Created log directory: $log_dir"
    fi
}

execute_sql_file() {
    local file="$1"
    local description="$2"

    if [[ "$DRY_RUN" -eq 1 ]]; then
        log_info "[DRY-RUN] Would execute: $description (${file})"
        return 0
    fi

    log_info "Executing: $description..."
    if mysql -u"$DB_ADMIN_USER" < "$file" 2>&1 | tee -a "$LOG_FILE"; then
        log_ok "$description completed"
    else
        log_error "$description failed"
        exit 1
    fi
}

execute_sql_substituted() {
    local file="$1"
    local description="$2"

    if [[ "$DRY_RUN" -eq 1 ]]; then
        log_info "[DRY-RUN] Would execute with substitution: $description"
        return 0
    fi

    log_info "Executing: $description (with variable substitution)..."
    sed -e "s/{{ENGINE_READ_PASSWORD}}/${ENGINE_READ_PASSWORD}/g" \
        -e "s/{{ENGINE_WRITE_PASSWORD}}/${ENGINE_WRITE_PASSWORD}/g" \
        -e "s/{{ENGINE_ADMIN_PASSWORD}}/${ENGINE_ADMIN_PASSWORD}/g" \
        "$file" | mysql -u"$DB_ADMIN_USER" 2>&1 | tee -a "$LOG_FILE"

    if [[ ${PIPESTATUS[1]} -eq 0 ]]; then
        log_ok "$description completed"
    else
        log_error "$description failed"
        exit 1
    fi
}

setup_partitions() {
    log_info "Setting up log partitions..."

    if [[ "$DRY_RUN" -eq 1 ]]; then
        log_info "[DRY-RUN] Would create future monthly partitions"
        return 0
    fi

    local partition_sql="ALTER TABLE engine.logs PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) ("
    local first=1
    local year month next_year next_month

    for i in $(seq 0 12); do
        year=$(date -d "+${i} months" +%Y 2>/dev/null || date -v+"${i}"m +%Y)
        month=$(date -d "+${i} months" +%m 2>/dev/null || date -v+"${i}"m +%m)
        next_year=$(date -d "+${i} months +1 month" +%Y 2>/dev/null || date -v+"${i}"m -v+1m +%Y)
        next_month=$(date -d "+${i} months +1 month" +%m 2>/dev/null || date -v+"${i}"m -v+1m +%m)

        local pname="p${year}${month}"
        local less_than="${next_year}-${next_month}-01 00:00:00"

        if [[ "$first" -eq 1 ]]; then
            partition_sql="${partition_sql}\n  PARTITION ${pname} VALUES LESS THAN (UNIX_TIMESTAMP('${less_than}'))"
            first=0
        else
            partition_sql="${partition_sql},\n  PARTITION ${pname} VALUES LESS THAN (UNIX_TIMESTAMP('${less_than}'))"
        fi
    done

    partition_sql="${partition_sql},\n  PARTITION pfuture VALUES LESS THAN MAXVALUE\n);"

    log_info "Creating monthly log partitions..."
    echo -e "$partition_sql" | mysql -u"$DB_ADMIN_USER" 2>&1 | tee -a "$LOG_FILE"
    log_ok "Log partitions configured"

    mysql -u"$DB_ADMIN_USER" <<'PARTITION_EVENT'
DELIMITER //
CREATE EVENT IF NOT EXISTS ev_add_log_partition
ON SCHEDULE EVERY 1 MONTH
STARTS DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-01 00:00:00')
DO
BEGIN
    DECLARE v_next_month VARCHAR(7);
    DECLARE v_less_than VARCHAR(20);
    SET v_next_month = DATE_FORMAT(CURDATE() + INTERVAL 2 MONTH, '%Y%m');
    SET v_less_than = DATE_FORMAT(CURDATE() + INTERVAL 2 MONTH, '%Y-%m-01 00:00:00');
    SET @sql = CONCAT('ALTER TABLE engine.logs ADD PARTITION (PARTITION p', v_next_month,
        ' VALUES LESS THAN (UNIX_TIMESTAMP("', v_less_than, '")))');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
PARTITION_EVENT

    log_ok "Monthly partition maintenance event created"
}

validate_setup() {
    log_info "Validating setup..."

    if [[ "$DRY_RUN" -eq 1 ]]; then
        log_info "[DRY-RUN] Skipping validation"
        return 0
    fi

    local errors=0

    log_info "Checking engine tables..."
    local engine_expected=(
        "artifacts" "payments" "payment_methods" "email_queue" "fulfillment"
        "canada_post_tracking" "sessions" "strikes" "ip_reputation" "node_health"
        "logs" "security_events" "waf_rules" "ai_inferences" "file_integrity"
        "unified_orders" "unified_customers" "unified_inventory" "unified_transactions"
        "unified_audit_log" "node_store_map" "sync_queue" "unified_notifications"
    )

    for table in "${engine_expected[@]}"; do
        local exists
        exists=$(mysql -u"$DB_ADMIN_USER" -e "SELECT 1 FROM information_schema.tables WHERE table_schema='engine' AND table_name='${table}';" 2>/dev/null | wc -l)
        if [[ "$exists" -lt 2 ]]; then
            log_error "Missing engine.${table}"
            ((errors++))
        fi
    done

    log_info "Checking store tables..."
    local store_tables_count
    store_tables_count=$(mysql -u"$DB_ADMIN_USER" -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='store';" 2>/dev/null | tail -1)
    if [[ "$store_tables_count" -lt 50 ]]; then
        log_warn "Store tables count is ${store_tables_count}, expected 70+"
    else
        log_ok "Store database has ${store_tables_count} tables"
    fi

    log_info "Checking procedures..."
    local proc_expected=(
        "sp_process_payment" "sp_send_email" "sp_start_fulfillment"
        "sp_update_trust_score" "sp_add_strike" "sp_check_session"
        "sp_process_canada_post_webhook"
    )

    for proc in "${proc_expected[@]}"; do
        local exists
        exists=$(mysql -u"$DB_ADMIN_USER" -e "SELECT 1 FROM information_schema.routines WHERE routine_schema='engine' AND routine_name='${proc}';" 2>/dev/null | wc -l)
        if [[ "$exists" -lt 2 ]]; then
            log_error "Missing procedure: ${proc}"
            ((errors++))
        fi
    done

    log_info "Checking triggers..."
    local trig_expected=(
        "trg_payments_before_insert" "trg_payments_after_insert"
        "trg_fulfillment_after_update" "trg_sessions_before_insert"
        "trg_strikes_after_insert" "trg_security_events_after_insert"
    )

    for trig in "${trig_expected[@]}"; do
        local exists
        exists=$(mysql -u"$DB_ADMIN_USER" -e "SELECT 1 FROM information_schema.triggers WHERE trigger_schema='engine' AND trigger_name='${trig}';" 2>/dev/null | wc -l)
        if [[ "$exists" -lt 2 ]]; then
            log_error "Missing trigger: ${trig}"
            ((errors++))
        fi
    done

    log_info "Checking charset..."
    local charset_check
    charset_check=$(mysql -u"$DB_ADMIN_USER" -e "SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='engine';" 2>/dev/null | tail -1)
    if [[ "$charset_check" != "utf8mb4" ]]; then
        log_warn "Engine charset is '${charset_check}', expected 'utf8mb4'"
    else
        log_ok "Engine charset: utf8mb4"
    fi

    charset_check=$(mysql -u"$DB_ADMIN_USER" -e "SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='store';" 2>/dev/null | tail -1)
    if [[ "$charset_check" != "utf8mb4" ]]; then
        log_warn "Store charset is '${charset_check}', expected 'utf8mb4'"
    else
        log_ok "Store charset: utf8mb4"
    fi

    if [[ "$SKIP_USERS" -eq 0 ]]; then
        log_info "Checking database users..."
        for user in "engine_read" "engine_write" "engine_admin"; do
            local exists
            exists=$(mysql -u"$DB_ADMIN_USER" -e "SELECT 1 FROM mysql.user WHERE user='${user}';" 2>/dev/null | wc -l)
            if [[ "$exists" -lt 2 ]]; then
                log_error "Missing user: ${user}"
                ((errors++))
            fi
        done
    fi

    if [[ "$errors" -gt 0 ]]; then
        log_error "Validation completed with ${errors} error(s)"
        return 1
    fi

    log_ok "All validations passed"
}

report_completion() {
    if [[ "$DRY_RUN" -eq 1 ]]; then
        log_info "[DRY-RUN] Would report completion to ENGINE:${REPORT_PORT}"
        return 0
    fi

    log_info "Reporting completion..."

    local report_payload
    report_payload=$(cat <<EOF
{
    "node_id": "${NODE_ID}",
    "node_role": "${NODE_ROLE}",
    "event": "database_setup_complete",
    "timestamp": "$(date -Iseconds)",
    "databases": ["engine", "store"],
    "engine_tables": 23,
    "store_tables": 77,
    "procedures": 7,
    "triggers": 6,
    "users_created": $([[ "$SKIP_USERS" -eq 1 ]] && echo 0 || echo 3),
    "partitions_configured": 1,
    "status": "success"
}
EOF
)

    if command -v curl &>/dev/null; then
        if curl -s -X POST -H "Content-Type: application/json" -d "$report_payload" "http://${WG_MESH_IP}:${REPORT_PORT}/api/setup-report" --connect-timeout 5 2>/dev/null; then
            log_ok "Completion report sent to ENGINE:${REPORT_PORT}"
        else
            log_warn "Could not reach ENGINE:${REPORT_PORT}"
            echo "$report_payload" > "/tmp/engine-${NODE_ID}-db-setup-report.json"
            log_info "Report saved to /tmp/engine-${NODE_ID}-db-setup-report.json"
        fi
    else
        log_warn "curl not available, saving report locally"
        echo "$report_payload" > "/tmp/engine-${NODE_ID}-db-setup-report.json"
    fi

    log_info "=========================================="
    log_ok  " ENGINE Node ${NODE_ID} Database Setup Complete"
    log_info "=========================================="
    log_info " Databases:   engine, store"
    log_info " Engine:      23 tables"
    log_info " Store:       77 tables"
    log_info " Procedures:  7"
    log_info " Triggers:    6"
    log_info " Users:       3 (if created)"
    log_info " Partitions:  Monthly (logs)"
    log_info " Log file:    ${LOG_FILE}"
    log_info "=========================================="
}

main() {
    if [[ "$REPORT_ONLY" -eq 1 ]]; then
        report_completion
        exit 0
    fi

    log_info "=========================================="
    log_info " ENGINE Node ${NODE_ID} Database Setup"
    log_info "=========================================="

    load_passwords
    preflight_checks
    execute_sql_file "$SCRIPT_DIR/00-engine.sql" "Phase 1: Engine schema"
    execute_sql_file "$SCRIPT_DIR/01-store.sql" "Phase 2: Store schema"
    execute_sql_file "$SCRIPT_DIR/02-additions.sql" "Phase 3: Unified additions"
    execute_sql_file "$SCRIPT_DIR/03-procedures.sql" "Phase 4: Stored procedures"
    execute_sql_file "$SCRIPT_DIR/04-triggers.sql" "Phase 5: Triggers"
    setup_partitions

    if [[ "$SKIP_USERS" -eq 0 ]]; then
        execute_sql_substituted "$SCRIPT_DIR/05-users.sql" "Phase 6: Users and privileges"

        if [[ "$PW_SOURCE" == "env" ]] && [[ ! -f "$SECRETS_FILE" ]]; then
            local secrets_dir
            secrets_dir="$(dirname "$SECRETS_FILE")"
            mkdir -p "$secrets_dir"
            cat > "$SECRETS_FILE" <<EOF
ENGINE_READ_PASSWORD='${ENGINE_READ_PASSWORD}'
ENGINE_WRITE_PASSWORD='${ENGINE_WRITE_PASSWORD}'
ENGINE_ADMIN_PASSWORD='${ENGINE_ADMIN_PASSWORD}'
EOF
            chmod 600 "$SECRETS_FILE"
            log_info "Saved generated passwords to ${SECRETS_FILE} (chmod 600)"
        fi
    fi

    validate_setup
    report_completion

    exit 0
}

main "$@"
