#!/usr/bin/env bash
# =============================================================================
# ci-sql-guard.sh — GB5 SQL Quality Gate
#
# Fails CI if any QueryBuilder (*QB.cs) file contains:
#   1. SELECT *  — every column in result set must be explicitly named
#   2. SQL with no tenant filter (DatabaseName/ClientId/TenantId) — prevents
#      cross-tenant data leaks
#
# Usage:
#   bash scripts/ci-sql-guard.sh              # from repo root
#   bash scripts/ci-sql-guard.sh GB5Solution  # limit to a sub-tree
#
# Exit codes:
#   0 — all checks passed
#   1 — one or more violations found
#
# Exclusions — patterns that are intentionally SELECT * free:
#   - Lines containing "#temp" or "[OUTPUT_"   (temp-table selects, safe)
#   - Lines containing "T.*"                   (subquery alias expansion)
#   - Comment lines (leading --)
#   - Stored-procedure EXEC results (EXEC sp_...)
# =============================================================================

set -euo pipefail

ROOT="${1:-$(git rev-parse --show-toplevel 2>/dev/null || pwd)}"
QB_PATTERN="*QB.cs"
VIOLATIONS=0

# ── Color helpers ─────────────────────────────────────────────────────────────
RED='\033[0;31m'
YELLOW='\033[1;33m'
GREEN='\033[0;32m'
NC='\033[0m'  # No Color

banner() { echo -e "${YELLOW}[sql-guard]${NC} $*"; }
pass()   { echo -e "  ${GREEN}✔${NC} $*"; }
fail()   { echo -e "  ${RED}✖${NC} $*"; }

# =============================================================================
# Check 1 — SELECT * violations
# =============================================================================
banner "Check 1 — SELECT * in QB files (${ROOT})"

# grep flags:
#   -r  recursive
#   -n  line numbers
#   -i  case-insensitive
#   -l  filenames only (for first pass)
#   --include — restrict to QB.cs files only
#
# Exclusions applied via grep -v pipeline:
#   - Lines with T.* (subquery alias)
#   - Lines with #temp or [OUTPUT_ (temp table selects)
#   - Comment-only lines (trimmed leading --)
#   - Lines with EXEC (stored procedures)

SELECT_STAR_VIOLATIONS=$(
    grep -rni --include="$QB_PATTERN" "SELECT \*" "$ROOT" \
    | grep -v "T\.\*" \
    | grep -v "#temp" \
    | grep -v "\[OUTPUT_" \
    | grep -v "^\s*--" \
    | grep -v "EXEC\s" \
    || true
)

if [[ -z "$SELECT_STAR_VIOLATIONS" ]]; then
    pass "No SELECT * violations found."
else
    fail "SELECT * violations (replace with explicit column lists):"
    echo ""
    while IFS= read -r line; do
        echo -e "    ${RED}${line}${NC}"
    done <<< "$SELECT_STAR_VIOLATIONS"
    echo ""
    VIOLATIONS=$((VIOLATIONS + 1))
fi

# =============================================================================
# Check 2 — SQL without tenant filter
#
# Logic: find QB files that contain SELECT/FROM/WHERE SQL blocks but do NOT
# reference DatabaseName, ClientId, or TenantId anywhere in the file.
#
# This is a file-level heuristic — a file with ANY SQL DML that lacks ANY
# tenant reference is flagged for manual review.
#
# Intentional exclusions (--exclude-dir):
#   - Framework QB files (FrameworkDAL) often use system-level queries
#   - Migration scripts under DB/
# =============================================================================
banner "Check 2 — QB files with DML but no tenant filter"

TENANT_FILTER_VIOLATIONS=()

while IFS= read -r -d '' qb_file; do
    # Skip framework and system QB files
    if echo "$qb_file" | grep -qE "(FrameworkDAL|DB/Migrations|scripts/)"; then
        continue
    fi

    # Check file has some DML (SELECT/INSERT/UPDATE/DELETE with FROM or INTO)
    has_dml=$(grep -ciE "^\s*(SELECT|INSERT\s+INTO|UPDATE\s+\w|DELETE\s+FROM)" "$qb_file" || true)
    if [[ "$has_dml" -eq 0 ]]; then
        continue
    fi

    # Check for any tenant filter reference
    has_tenant=$(grep -ciE "(DatabaseName|ClientId|TenantId|DATABASENAME|CLIENTID|TENANTID)" "$qb_file" || true)
    if [[ "$has_tenant" -eq 0 ]]; then
        TENANT_FILTER_VIOLATIONS+=("$qb_file")
    fi
done < <(find "$ROOT" -name "$QB_PATTERN" -print0)

if [[ ${#TENANT_FILTER_VIOLATIONS[@]} -eq 0 ]]; then
    pass "All QB files with DML reference a tenant filter."
else
    fail "QB files with DML but no tenant filter (review required):"
    echo ""
    for f in "${TENANT_FILTER_VIOLATIONS[@]}"; do
        # Show relative path
        rel="${f#$ROOT/}"
        echo -e "    ${RED}${rel}${NC}"
    done
    echo ""
    echo -e "  ${YELLOW}→ Every query must include DatabaseName, ClientId, or TenantId.${NC}"
    echo -e "  ${YELLOW}  If intentional (e.g. system-level queries), add a comment:${NC}"
    echo -e "  ${YELLOW}  -- ci-sql-guard: tenant-filter-exempt${NC}"
    echo ""
    VIOLATIONS=$((VIOLATIONS + 1))
fi

# =============================================================================
# Summary
# =============================================================================
echo ""
if [[ "$VIOLATIONS" -eq 0 ]]; then
    echo -e "${GREEN}✔ All SQL quality checks passed.${NC}"
    exit 0
else
    echo -e "${RED}✖ ${VIOLATIONS} check(s) failed. Fix violations before merging.${NC}"
    exit 1
fi