#!/usr/bin/env python3
"""Static verification for the Brightforge build.
1. database.sql structural validation (tables, FKs, indexes, seed refs)
2. Table/column usage in PHP vs schema
3. Helper function calls vs definitions
4. URL/slug consistency with .htaccess rewrite rules
5. No lorem ipsum / forbidden patterns
6. File structure per spec
"""
import re, os, sys, glob

ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
failures = []
ok = []

def check(cond, msg):
    if cond:
        ok.append(msg)
    else:
        failures.append(msg)

# ---------- 1. SQL structure ----------
sql = open(os.path.join(ROOT, "database.sql"), encoding="utf-8").read()

tables = re.findall(r"CREATE TABLE\s+`?(\w+)`?\s*\(", sql)
check(len(tables) == 20, f"SQL: 20 tables found ({len(tables)})")
check("CREATE DATABASE IF NOT EXISTS brightforge" in sql, "SQL: database bootstrap present")

# FK references point to existing tables
fk_refs = set(re.findall(r"REFERENCES\s+`?(\w+)`?\s*\(", sql))
missing_refs = [t for t in fk_refs if t not in tables]
check(not missing_refs, f"SQL: all FK targets exist {missing_refs or ''}")

# every table has PRIMARY KEY
no_pk = []
for t in tables:
    m = re.search(r"CREATE TABLE\s+`?" + re.escape(t) + r"`?\s*\(.*?PRIMARY KEY", sql, re.S)
    if not m:
        no_pk.append(t)
check(not no_pk, f"SQL: all tables have PK {no_pk or ''}")

# index requirements per spec: slug, status, category_id, created_at
for idx_key in ["uq_", "idx_"]:
    pass
slug_cols = re.findall(r"UNIQUE KEY uq_\w+\s*\((\w+)\)", sql)
check(any("slug" in c for c in slug_cols), "SQL: unique slug indexes present")
check("KEY idx_services_status" in sql, "SQL: status indexes present")
check("KEY idx_products_category" in sql, "SQL: category_id indexes present")
check("KEY idx_activity_created" in sql, "SQL: created_at indexes present")

# balance of quotes/parens (crude)
check(sql.count("(") == sql.count(")"), f"SQL: balanced parens ({sql.count('(')} vs {sql.count(')')})")

# bcrypt hash present
check("$2y$12$XTNCr38kOeTLXv0DqqujQ.KIXV0Dhr7avmYhsBNBnAUda95wv94Kq" in sql, "SQL: admin bcrypt hash seeded")

# seed svg refs exist on disk
svg_refs = set(re.findall(r"'(/uploads/[^']+\.svg)'", sql))
missing_svg = [p for p in svg_refs if not os.path.exists(os.path.join(ROOT, p.lstrip("/")))]
check(not missing_svg, f"SQL: all {len(svg_refs)} referenced SVGs exist on disk {missing_svg or ''}")

# ---------- 2/3. PHP helper & table usage ----------
php_files = []
for pat in ["*.php", "admin/*.php", "includes/*.php", "config/*.php"]:
    php_files += glob.glob(os.path.join(ROOT, pat))
php_src = ""
for f in php_files:
    php_src += open(f, encoding="utf-8").read() + "\n"

# tables referenced in SQL queries exist in schema
query_tables = set()
for m in re.finditer(r"(?:FROM|INTO|UPDATE|JOIN)\s+`?(\w+)`?", php_src):
    query_tables.add(m.group(1))
unknown_tables = [t for t in query_tables if t not in tables and t not in ("DUAL", "the", "THE", "svalue")]
check(not unknown_tables, f"PHP: all queried tables exist in schema {unknown_tables or ''}")

# helper functions defined
defs = set(re.findall(r"^function\s+(\w+)\s*\(", open(os.path.join(ROOT, "includes/functions.php"), encoding="utf-8").read(), re.M))
called = set(re.findall(r"(?<!function\s)(?<!->)(?<!::)\b([a-z_]{3,})\s*\(", php_src))
# only consider helpers that look like ours
ours = {c for c in called if c in {"esc","esc_url","slugify","unique_slug","csrf_token","csrf_field","csrf_verify","require_csrf",
"settings_all","setting","site_url","redirect","flash_set","flash_get","current_user","is_logged_in","require_login",
"has_permission","require_permission","log_activity","excerpt","nl_to_list","tech_tags","format_date","time_ago",
"paginate","pagination_links","upload_file","clean_input","is_valid_email","asset","db"}}
undefined = [c for c in ours if c not in defs]
check(not undefined, f"PHP: helper calls all defined {undefined or ''}")

# every public page includes header/footer consistently
for f in ["index.php","about.php","services.php","service-detail.php","products.php","product-detail.php",
          "portfolio.php","portfolio-detail.php","blog.php","article-detail.php","contact.php","sitemap.php"]:
    src = open(os.path.join(ROOT, f), encoding="utf-8").read()
    check("config/config.php" in src, f"{f}: boots config")
    if f != "sitemap.php":
        check("INCLUDES_PATH . '/header.php'" in src and "INCLUDES_PATH . '/footer.php'" in src, f"{f}: header+footer")

# admin pages guard (layout partials header/sidebar/footer are includes, not standalone pages)
for f in glob.glob(os.path.join(ROOT, "admin/*.php")):
    name = os.path.basename(f)
    if name in ("header.php", "sidebar.php", "footer.php"):
        continue
    src = open(f, encoding="utf-8").read()
    check("../config/config.php" in src, f"admin/{name}: boots config")
    if name not in ("login.php", "logout.php"):
        check("__DIR__ . '/header.php'" in src and "__DIR__ . '/footer.php'" in src, f"admin/{name}: layout includes")

# ---------- 4. URL / slug consistency ----------
ht = open(os.path.join(ROOT, ".htaccess"), encoding="utf-8").read()
rewrite_rules = re.findall(r"RewriteRule\s+\^([\w/\-\[\]\.]+)", ht)
url_uses = set(re.findall(r'["\'](/[a-z-]+/[a-z0-9-]+)["\']', php_src))
for u in url_uses:
    prefix = "/" + u.split("/")[1]
    rule_present = any(prefix + "/" in r for r in rewrite_rules)
    if prefix in ("/uploads", "/assets"):
        continue
    check(rule_present, f"URL {u} has matching rewrite rule")

# slugs referenced in links exist in seed data
seed_slugs = set(re.findall(r"'([a-z0-9-]+)'", sql))
link_slugs = set(re.findall(r'["\']/(?:products|services|portfolio|blog)/([a-z0-9-]+)["\']', php_src))
for s in link_slugs:
    check(s in seed_slugs or s.startswith("$"), f"linked slug '{s}' exists in seed or is dynamic")

# ---------- 5. forbidden content ----------
lorem = re.search(r"lorem ipsum", sql + php_src, re.I)
check(not lorem, "No lorem ipsum anywhere")
for bad in ["We are a leading company", "Ready to take your business to the next level",
            "Welcome to Brightforge", "generic blue gradient", "next level?"]:
    check(bad.lower() not in (sql + php_src).lower(), f"No template phrase: '{bad}'")

# fake statistics check: no fabricated "XX%" metrics in seeded copy
fake_metrics = re.findall(r"\d{2,3}\s*%", sql)
check(not fake_metrics, f"SQL: no fabricated percentage claims {fake_metrics or ''}")

# ---------- 6. file structure ----------
required = ["index.php","about.php","services.php","service-detail.php","products.php","product-detail.php",
"portfolio.php","portfolio-detail.php","blog.php","article-detail.php","contact.php",".htaccess","database.sql",
"config/config.php","includes/functions.php","includes/header.php","includes/footer.php","assets/css/main.css",
"assets/css/admin.css","assets/js/main.js","assets/js/admin.js","admin/index.php","admin/login.php","admin/logout.php",
"admin/services.php","admin/service-form.php","admin/products.php","admin/product-form.php","admin/portfolio.php",
"admin/portfolio-form.php","admin/articles.php","admin/article-form.php","admin/testimonials.php","admin/inquiries.php",
"admin/media.php","admin/users.php","admin/roles.php","admin/settings.php","admin/header.php","admin/sidebar.php","admin/footer.php"]
missing = [r for r in required if not os.path.exists(os.path.join(ROOT, r))]
check(not missing, f"Structure: all spec files present {missing or ''}")

# admin CRUD references each content table
for tbl in ["services","products","portfolio","articles","testimonials","inquiries","media","users","roles","settings"]:
    check(re.search(r"\b" + tbl + r"\b", open(os.path.join(ROOT, f"admin/{tbl}.php"), encoding="utf-8").read()),
          f"admin/{tbl}.php exists and operates on its table")

print("=" * 60)
print(f"PASS: {len(ok)}  FAIL: {len(failures)}")
for o in ok: print("  ✓", o)
if failures:
    print("-" * 60)
    for fl in failures: print("  ✗", fl)
    sys.exit(1)