#!/bin/bash
# Test execute_sql "limited" flag behavior
# Usage: ./test_sql_limit.sh [db_path]
# Default: ../db/test.duckdb (ALWAYS USE TEST DATABASE!)
#
# This tests the fix for the bug where "limited" was always false
# even when results were truncated.

source "$(dirname "$0")/test_lib.sh"

# Get absolute paths before changing directory
SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
PROJECT_DIR="$(cd "$SCRIPT_DIR/.." && pwd)"

# Convert DB_PATH to absolute path (before we cd later)
DB_PATH_ARG="${1:-$PROJECT_DIR/db/test.duckdb}"
if [[ "$DB_PATH_ARG" = /* ]]; then
    DB_PATH="$DB_PATH_ARG"
else
    DB_PATH="$(cd "$(dirname "$DB_PATH_ARG")" && pwd)/$(basename "$DB_PATH_ARG")"
fi

if [ ! -f "$DB_PATH" ]; then
    echo -e "${RED}Error: Database not found at $DB_PATH${NC}"
    exit 1
fi

echo "=== Testing execute_sql 'limited' Flag ==="
echo ""
echo "Database: $DB_PATH"
echo ""

check_binary

# Navigate to the project directory where skraak binary is located
cd "$PROJECT_DIR" || exit 1

# Helper to run CLI command and capture JSON output (stderr discarded)
run_cli() {
    "$PROJECT_DIR/skraak" "$@" 2>/dev/null || true
}

# Count total files in database for test planning
FILE_COUNT=$(run_cli sql --db "$DB_PATH" "SELECT COUNT(*) as cnt FROM file WHERE active = true" | jq -r '.rows[0].cnt // 0')
LOCATION_COUNT=$(run_cli sql --db "$DB_PATH" "SELECT COUNT(*) as cnt FROM location WHERE active = true" | jq -r '.rows[0].cnt // 0')
DATASET_COUNT=$(run_cli sql --db "$DB_PATH" "SELECT COUNT(*) as cnt FROM dataset WHERE active = true" | jq -r '.rows[0].cnt // 0')

echo "Database stats:"
echo "  Files: $FILE_COUNT"
echo "  Locations: $LOCATION_COUNT"
echo "  Datasets: $DATASET_COUNT"
echo ""

# We need at least some files to test truncation
if [ "$FILE_COUNT" -lt 100 ]; then
    echo -e "${YELLOW}Warning: Need at least 100 files to test truncation. Have $FILE_COUNT.${NC}"
    echo "Some tests may be skipped."
    echo ""
fi

TESTS_RUN=0
TESTS_PASSED=0
TESTS_FAILED=0

# Test helper: check limited flag and row count
test_limit_flag() {
    local name="$1"
    local expected_limited="$2"
    local expected_row_count="$3"
    local result="$4"

    ((TESTS_RUN++)) || true

    # Note: jq '//' operator treats false as empty, so check for boolean explicitly
    local actual_limited=$(echo "$result" | jq -r 'if has("limited") then (.limited | tostring) else "missing" end')
    local actual_row_count=$(echo "$result" | jq -r '.row_count // -1')

    if [ "$actual_limited" = "$expected_limited" ] && [ "$actual_row_count" -eq "$expected_row_count" ]; then
        echo -e "${GREEN}✓${NC} $name"
        echo "    row_count=$actual_row_count, limited=$actual_limited"
        ((TESTS_PASSED++)) || true
        return 0
    else
        echo -e "${RED}✗${NC} $name"
        echo "    Expected: row_count=$expected_row_count, limited=$expected_limited"
        echo "    Actual:   row_count=$actual_row_count, limited=$actual_limited"
        ((TESTS_FAILED++)) || true
        return 1
    fi
}

# Test helper: check query_executed field
test_query_reported() {
    local name="$1"
    local expected_query_fragment="$2"
    local result="$3"

    ((TESTS_RUN++)) || true

    local query=$(echo "$result" | jq -r '.query_executed // ""')

    if echo "$query" | grep -q "$expected_query_fragment"; then
        echo -e "${GREEN}✓${NC} $name"
        echo "    query: $query"
        ((TESTS_PASSED++)) || true
        return 0
    else
        echo -e "${RED}✗${NC} $name"
        echo "    Expected fragment: $expected_query_fragment"
        echo "    Actual query: $query"
        ((TESTS_FAILED++)) || true
        return 1
    fi
}

echo "=== Test 1: Auto-limit with truncation ==="
echo "Query without LIMIT on large table should trigger truncation"
if [ "$FILE_COUNT" -ge 100 ]; then
    result=$(run_cli sql --db "$DB_PATH" "SELECT * FROM file WHERE active = true")
    test_limit_flag "Auto-limit truncates results" "true" "1000" "$result"
    test_query_reported "Query shows effective limit 1000" "LIMIT 1000" "$result"
else
    echo -e "${YELLOW}⊘${NC} Skipped (need >= 100 files)"
fi
echo ""

echo "=== Test 2: Auto-limit without truncation ==="
echo "Query without LIMIT on small table should not truncate"
result=$(run_cli sql --db "$DB_PATH" "SELECT * FROM dataset WHERE active = true")
EXPECTED_ROWS=$DATASET_COUNT
test_limit_flag "Auto-limit no truncation" "false" "$EXPECTED_ROWS" "$result"
echo ""

echo "=== Test 3: User-provided LIMIT preserved ==="
echo "User's own LIMIT clause should be preserved"
result=$(run_cli sql --db "$DB_PATH" "SELECT * FROM file WHERE active = true LIMIT 5")
test_limit_flag "User LIMIT: limited=false" "false" "5" "$result"
test_query_reported "User LIMIT preserved in query" "LIMIT 5$" "$result"
echo ""

echo "=== Test 4: User LIMIT equal to default ==="
echo "User LIMIT 1000 should work (not double-limited)"
result=$(run_cli sql --db "$DB_PATH" "SELECT * FROM file WHERE active = true LIMIT 1000")
test_limit_flag "User LIMIT 1000: limited=false" "false" "1000" "$result"
test_query_reported "User LIMIT 1000 preserved" "LIMIT 1000$" "$result"
echo ""

echo "=== Test 5: Explicit --limit parameter with truncation ==="
echo "Using --limit 100 should truncate if table has > 100 rows"
if [ "$FILE_COUNT" -ge 100 ]; then
    result=$(run_cli sql --db "$DB_PATH" --limit 100 "SELECT * FROM file WHERE active = true")
    test_limit_flag "--limit 100 truncates" "true" "100" "$result"
    test_query_reported "Query shows LIMIT 100" "LIMIT 100" "$result"
else
    echo -e "${YELLOW}⊘${NC} Skipped (need >= 100 files)"
fi
echo ""

echo "=== Test 6: Explicit --limit parameter without truncation ==="
echo "Using --limit larger than table should not truncate"
result=$(run_cli sql --db "$DB_PATH" --limit 100 "SELECT * FROM dataset WHERE active = true")
EXPECTED_ROWS=$DATASET_COUNT
test_limit_flag "--limit > table size: no truncation" "false" "$EXPECTED_ROWS" "$result"
echo ""

echo "=== Test 7: Empty result set ==="
echo "Query returning no rows should have limited=false"
result=$(run_cli sql --db "$DB_PATH" "SELECT * FROM dataset WHERE id = 'NONEXISTENT_ID_12345'")
test_limit_flag "Empty result: limited=false" "false" "0" "$result"
echo ""

echo "=== Test 8: Small --limit with small table ==="
echo "--limit 1 on datasets should work correctly"
result=$(run_cli sql --db "$DB_PATH" --limit 1 "SELECT * FROM dataset WHERE active = true")
if [ "$DATASET_COUNT" -gt 1 ]; then
    test_limit_flag "--limit 1 truncates (table has $DATASET_COUNT)" "true" "1" "$result"
else
    test_limit_flag "--limit 1 no truncation (table has $DATASET_COUNT)" "false" "$DATASET_COUNT" "$result"
fi
echo ""

echo "=== Summary ==="
echo "Tests run: $TESTS_RUN"
echo -e "Passed: ${GREEN}$TESTS_PASSED${NC}"
if [ "$TESTS_FAILED" -gt 0 ]; then
    echo -e "Failed: ${RED}$TESTS_FAILED${NC}"
    exit 1
else
    echo -e "Failed: $TESTS_FAILED"
fi