#!/bin/bash

# Database Helper - Secure MySQL Connection Functions
# Source this file in other scripts to get secure database access
#
# Usage:
#   source "$(dirname "$0")/_db-helper.sh"
#   db_query "SELECT COUNT(*) FROM jobs"
#   db_count "jobs"

# Get project root (scripts are in showprima/scripts/)
if [ -z "$PROJECT_ROOT" ]; then
    # If sourced, use BASH_SOURCE; if executed directly, use $0
    SCRIPT_PATH="${BASH_SOURCE[0]:-$0}"
    PROJECT_ROOT="$(cd "$(dirname "$SCRIPT_PATH")/.." && pwd)"
fi

# Load environment variables from .env
if [ -f "$PROJECT_ROOT/.env" ]; then
    # Export database credentials from .env
    export $(grep -E "^DB_(HOST|PORT|DATABASE|USERNAME|PASSWORD)=" "$PROJECT_ROOT/.env" | xargs)
else
    echo "Error: .env file not found at $PROJECT_ROOT/.env" >&2
    exit 1
fi

# Validate required variables
if [ -z "$DB_HOST" ] || [ -z "$DB_DATABASE" ] || [ -z "$DB_USERNAME" ] || [ -z "$DB_PASSWORD" ]; then
    echo "Error: Database credentials not found in .env" >&2
    echo "Required: DB_HOST, DB_DATABASE, DB_USERNAME, DB_PASSWORD" >&2
    exit 1
fi

# Function to execute MySQL query securely (simple output)
# Usage: db_query "SELECT COUNT(*) FROM jobs"
db_query() {
    local QUERY="$1"
    mysql --host="$DB_HOST" \
          --port="${DB_PORT:-3306}" \
          --user="$DB_USERNAME" \
          --password="$DB_PASSWORD" \
          --database="$DB_DATABASE" \
          -se "$QUERY" 2>/dev/null
}

# Function to execute MySQL query with table formatting
# Usage: db_table "SELECT * FROM seats LIMIT 5"
db_table() {
    local QUERY="$1"
    mysql --host="$DB_HOST" \
          --port="${DB_PORT:-3306}" \
          --user="$DB_USERNAME" \
          --password="$DB_PASSWORD" \
          --database="$DB_DATABASE" \
          --table \
          -e "$QUERY" 2>&1 | grep -v "Using a password"
}

# Function to test MySQL connection
# Usage: db_test_connection
db_test_connection() {
    mysql --host="$DB_HOST" \
          --port="${DB_PORT:-3306}" \
          --user="$DB_USERNAME" \
          --password="$DB_PASSWORD" \
          --database="$DB_DATABASE" \
          -e "SELECT 1" > /dev/null 2>&1
}

# Function to count rows in a table
# Usage: db_count "jobs"
db_count() {
    local TABLE="$1"
    db_query "SELECT COUNT(*) FROM $TABLE"
}

# Function to count rows with WHERE clause
# Usage: db_count_where "jobs" "queue='default'"
db_count_where() {
    local TABLE="$1"
    local WHERE="$2"
    db_query "SELECT COUNT(*) FROM $TABLE WHERE $WHERE"
}

# Function to show table structure
# Usage: db_describe "seats"
db_describe() {
    local TABLE="$1"
    db_table "SHOW COLUMNS FROM $TABLE"
}

# Function to show table row count and recent records
# Usage: db_peek "orders" 5
db_peek() {
    local TABLE="$1"
    local LIMIT="${2:-5}"
    echo "=== Table: $TABLE ==="
    echo "Total rows: $(db_count "$TABLE")"
    echo ""
    echo "Sample records:"
    db_table "SELECT * FROM $TABLE LIMIT $LIMIT"
}

# Function to list all tables
# Usage: db_tables
db_tables() {
    db_table "SHOW TABLES"
}

# Function to search for tables by pattern
# Usage: db_find_tables "admin"
db_find_tables() {
    local PATTERN="$1"
    db_table "SHOW TABLES LIKE '%${PATTERN}%'"
}

# Function to get table sizes
# Usage: db_table_sizes
db_table_sizes() {
    db_table "SELECT TABLE_NAME, TABLE_ROWS, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Size (MB)'
              FROM information_schema.TABLES
              WHERE TABLE_SCHEMA = DATABASE()
              ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC
              LIMIT 20"
}

# Export functions for use in other scripts
export -f db_query
export -f db_table
export -f db_test_connection
export -f db_count
export -f db_count_where
export -f db_describe
export -f db_peek
export -f db_tables
export -f db_find_tables
export -f db_table_sizes