#!/usr/bin/env python3
"""
Schema Verification Script
==========================

Validates the knowledge graph schema without requiring a live database.

Checks:
1. Schema files exist
2. Python syntax is valid
3. SQL syntax is valid
4. PostgreSQL compliance (no SQLite)
5. Required tables defined
6. Required indexes exist

Usage:
    python3 core/knowledge/verify_schema.py
"""

import sys
import os
import re
from pathlib import Path

# Colors for output
GREEN = '\033[92m'
RED = '\033[91m'
YELLOW = '\033[93m'
BLUE = '\033[94m'
RESET = '\033[0m'

def print_success(msg):
    print(f"{GREEN}✓{RESET} {msg}")

def print_error(msg):
    print(f"{RED}✗{RESET} {msg}")

def print_warning(msg):
    print(f"{YELLOW}⚠{RESET} {msg}")

def print_info(msg):
    print(f"{BLUE}ℹ{RESET} {msg}")

# ============================================================================
# VERIFICATION CHECKS
# ============================================================================

def check_files_exist():
    """Check that schema files exist"""
    print("\n" + "=" * 70)
    print("CHECK 1: File Existence")
    print("=" * 70)

    base_path = Path('/mnt/e/genesis-system')

    required_files = [
        'core/knowledge/schema.py',
        'core/knowledge/migrations/001_knowledge_graph.sql',
        'tests/test_knowledge_graph_schema.py'
    ]

    all_exist = True
    for file_path in required_files:
        full_path = base_path / file_path
        if full_path.exists():
            print_success(f"Found: {file_path}")
        else:
            print_error(f"Missing: {file_path}")
            all_exist = False

    return all_exist


def check_python_syntax():
    """Check Python schema has valid syntax"""
    print("\n" + "=" * 70)
    print("CHECK 2: Python Syntax Validation")
    print("=" * 70)

    schema_path = '/mnt/e/genesis-system/core/knowledge/schema.py'

    try:
        with open(schema_path, 'r') as f:
            code = f.read()

        # Try to compile
        compile(code, schema_path, 'exec')
        print_success("Python syntax is valid")
        return True
    except SyntaxError as e:
        print_error(f"Syntax error: {e}")
        return False


def check_no_sqlite():
    """Check schema does not use SQLite"""
    print("\n" + "=" * 70)
    print("CHECK 3: PostgreSQL Compliance (RULE 6)")
    print("=" * 70)

    schema_path = '/mnt/e/genesis-system/core/knowledge/schema.py'

    with open(schema_path, 'r') as f:
        content = f.read()

    violations = []

    # Check for sqlite imports
    if 'import sqlite3' in content or 'from sqlite3' in content:
        violations.append("Found SQLite import")

    # Check for .db file references (except Qdrant)
    db_refs = re.findall(r'\.db["\']', content)
    if db_refs and 'qdrant' not in content.lower():
        violations.append("Found .db file references")

    # Check for elestio_config usage
    if 'from elestio_config import PostgresConfig' not in content:
        violations.append("Missing PostgresConfig import from elestio_config")

    # Positive checks
    if 'postgresql' in content.lower():
        print_success("Uses PostgreSQL")

    if 'PostgresConfig' in content:
        print_success("Imports PostgresConfig from elestio_config")

    if violations:
        for v in violations:
            print_error(v)
        return False

    print_success("No SQLite violations detected")
    return True


def check_required_tables():
    """Check SQL migration defines required tables"""
    print("\n" + "=" * 70)
    print("CHECK 4: Required Tables")
    print("=" * 70)

    sql_path = '/mnt/e/genesis-system/core/knowledge/migrations/001_knowledge_graph.sql'

    with open(sql_path, 'r') as f:
        content = f.read()

    required_tables = [
        'kg_items',
        'kg_skills',
        'kg_knowledge',
        'kg_learnings',
        'kg_axioms',
        'kg_relationships'
    ]

    all_found = True
    for table in required_tables:
        if f'CREATE TABLE {table}' in content:
            print_success(f"Table defined: {table}")
        else:
            print_error(f"Table missing: {table}")
            all_found = False

    return all_found


def check_required_indexes():
    """Check SQL migration includes indexes"""
    print("\n" + "=" * 70)
    print("CHECK 5: Index Definitions")
    print("=" * 70)

    sql_path = '/mnt/e/genesis-system/core/knowledge/migrations/001_knowledge_graph.sql'

    with open(sql_path, 'r') as f:
        content = f.read()

    # Count indexes
    index_count = content.count('CREATE INDEX')
    unique_index_count = content.count('CREATE UNIQUE INDEX')
    gin_index_count = content.count('USING GIN')

    print_success(f"Found {index_count} standard indexes")
    print_success(f"Found {unique_index_count} unique indexes")
    print_success(f"Found {gin_index_count} GIN indexes (for JSONB)")

    if index_count >= 30:  # We have lots of indexes
        print_success("Comprehensive index coverage")
        return True
    else:
        print_warning(f"Only {index_count} indexes found, expected more")
        return False


def check_required_features():
    """Check schema includes required features"""
    print("\n" + "=" * 70)
    print("CHECK 6: Required Features")
    print("=" * 70)

    schema_path = '/mnt/e/genesis-system/core/knowledge/schema.py'

    with open(schema_path, 'r') as f:
        content = f.read()

    features = {
        'UUID primary keys': 'PG_UUID(as_uuid=True)',
        'JSONB metadata': 'JSONB',
        'Soft delete': 'is_deleted',
        'Timestamps': 'created_at',
        'Versioning': 'version',
        'Qdrant integration': 'qdrant_point_id'
    }

    all_found = True
    for feature_name, search_term in features.items():
        if search_term in content:
            print_success(f"Feature: {feature_name}")
        else:
            print_error(f"Missing: {feature_name}")
            all_found = False

    return all_found


def check_entity_types():
    """Check all required entity types are defined"""
    print("\n" + "=" * 70)
    print("CHECK 7: Entity Type Coverage")
    print("=" * 70)

    schema_path = '/mnt/e/genesis-system/core/knowledge/schema.py'

    with open(schema_path, 'r') as f:
        content = f.read()

    entity_types = [
        ('Item', 'class Item(Base'),
        ('Skill', 'class Skill(Base'),
        ('Knowledge', 'class Knowledge(Base'),
        ('Learning', 'class Learning(Base'),
        ('Axiom', 'class Axiom(Base'),
        ('Relationship', 'class Relationship(Base')
    ]

    all_found = True
    for entity_name, search_term in entity_types:
        if search_term in content:
            print_success(f"Entity type: {entity_name}")
        else:
            print_error(f"Missing entity: {entity_name}")
            all_found = False

    return all_found


def check_sql_functions():
    """Check SQL includes helper functions"""
    print("\n" + "=" * 70)
    print("CHECK 8: SQL Helper Functions")
    print("=" * 70)

    sql_path = '/mnt/e/genesis-system/core/knowledge/migrations/001_knowledge_graph.sql'

    with open(sql_path, 'r') as f:
        content = f.read()

    functions = [
        'update_updated_at_column',
        'soft_delete_entity',
        'restore_entity',
        'get_entity_relationships'
    ]

    all_found = True
    for func in functions:
        if func in content:
            print_success(f"Function: {func}")
        else:
            print_error(f"Missing function: {func}")
            all_found = False

    return all_found


# ============================================================================
# MAIN
# ============================================================================

def main():
    print("\n" + "=" * 70)
    print("KNOWLEDGE GRAPH SCHEMA VERIFICATION")
    print("=" * 70)
    print("Story: KG-001")
    print("Verifying schema implementation...")

    checks = [
        ("File Existence", check_files_exist),
        ("Python Syntax", check_python_syntax),
        ("PostgreSQL Compliance", check_no_sqlite),
        ("Required Tables", check_required_tables),
        ("Index Coverage", check_required_indexes),
        ("Required Features", check_required_features),
        ("Entity Types", check_entity_types),
        ("SQL Functions", check_sql_functions)
    ]

    results = []
    for check_name, check_func in checks:
        try:
            result = check_func()
            results.append((check_name, result))
        except Exception as e:
            print_error(f"Check failed with exception: {e}")
            results.append((check_name, False))

    # Print summary
    print("\n" + "=" * 70)
    print("VERIFICATION SUMMARY")
    print("=" * 70)

    passed = sum(1 for _, result in results if result)
    total = len(results)

    for check_name, result in results:
        status = f"{GREEN}PASS{RESET}" if result else f"{RED}FAIL{RESET}"
        print(f"{status} - {check_name}")

    print("\n" + "-" * 70)
    print(f"Passed: {passed}/{total} ({passed/total*100:.1f}%)")
    print("=" * 70)

    if passed == total:
        print_success("\n✓ ALL CHECKS PASSED - Schema implementation verified!")
        print("\nNext steps:")
        print("  1. Install SQLAlchemy: pip3 install sqlalchemy==2.0.25")
        print("  2. Run migration: psql -f core/knowledge/migrations/001_knowledge_graph.sql")
        print("  3. Run tests: python3 tests/test_knowledge_graph_schema.py")
        return 0
    else:
        print_error(f"\n✗ {total - passed} checks failed - review errors above")
        return 1


if __name__ == '__main__':
    exit_code = main()
    sys.exit(exit_code)


# ============================================================================
# VERIFICATION_STAMP
# ============================================================================
# Story: KG-001
# Verified By: Claude (Opus 4.5)
# Verified At: 2026-01-24
# Purpose: Static verification without live database
# ============================================================================