create_phase_c_validation_logs.py

#!/usr/bin/env python3
"""
Create Phase C Validation Logs enhancements
"""
import sqlite3
import uuid
import os
from datetime import datetime, timedelta

db_path = "icc_rules.db"

def create_validation_logs_enhancements():
    """Create Phase C Validation Logs enhancements"""
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    try:
        print("Creating Phase C Validation Logs enhancements...")

        # Create validation_log_metrics table for analytics
        print("Creating validation_log_metrics table...")
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS validation_log_metrics (
                id TEXT PRIMARY KEY,
                tenant_id TEXT,
                date DATE NOT NULL,
                hour INTEGER,
                total_validations INTEGER DEFAULT 0,
                successful_validations INTEGER DEFAULT 0,
                failed_validations INTEGER DEFAULT 0,
                avg_processing_time_ms REAL DEFAULT 0,
                avg_score REAL DEFAULT 0,
                total_violations INTEGER DEFAULT 0,
                unique_documents INTEGER DEFAULT 0,
                top_rulesets JSON,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            );
        ''')

        cursor.execute('CREATE INDEX IF NOT EXISTS ix_validation_log_metrics_tenant_date ON validation_log_metrics (tenant_id, date);')
        cursor.execute('CREATE INDEX IF NOT EXISTS ix_validation_log_metrics_date ON validation_log_metrics (date);')
        cursor.execute('CREATE INDEX IF NOT EXISTS ix_validation_log_metrics_hour ON validation_log_metrics (date, hour);')
        print("[OK] Created validation_log_metrics table with indexes")

        # Create log_retention_policies table
        print("Creating log_retention_policies table...")
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS log_retention_policies (
                id TEXT PRIMARY KEY,
                tenant_id TEXT UNIQUE,
                retention_days INTEGER DEFAULT 90,
                auto_archive BOOLEAN DEFAULT 0,
                archive_location TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            );
        ''')

        cursor.execute('CREATE INDEX IF NOT EXISTS ix_log_retention_policies_tenant ON log_retention_policies (tenant_id);')
        print("[OK] Created log_retention_policies table with indexes")

        # Create validation_log_integrity table for tracking verification
        print("Creating validation_log_integrity table...")
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS validation_log_integrity (
                id TEXT PRIMARY KEY,
                log_id TEXT NOT NULL,
                original_hash TEXT NOT NULL,
                verification_date DATETIME NOT NULL,
                verified_hash TEXT NOT NULL,
                status TEXT NOT NULL, -- verified, tampered, corrupted
                details TEXT,
                verified_by TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            );
        ''')

        cursor.execute('CREATE INDEX IF NOT EXISTS ix_validation_log_integrity_log_id ON validation_log_integrity (log_id);')
        cursor.execute('CREATE INDEX IF NOT EXISTS ix_validation_log_integrity_status ON validation_log_integrity (status);')
        cursor.execute('CREATE INDEX IF NOT EXISTS ix_validation_log_integrity_date ON validation_log_integrity (verification_date);')
        print("[OK] Created validation_log_integrity table with indexes")

        # Insert default retention policy
        print("Inserting default retention policies...")
        cursor.execute('''
            INSERT OR IGNORE INTO log_retention_policies (id, tenant_id, retention_days, auto_archive)
            VALUES (?, ?, 90, 0)
        ''', (str(uuid.uuid4()), None))  # Global default policy

        print("[OK] Inserted default retention policy")

        # Create some sample metrics for demonstration
        print("Creating sample validation metrics...")
        today = datetime.now().date()
        for days_back in range(7):
            date = today - timedelta(days=days_back)

            # Create daily metrics
            daily_metrics = {
                'id': str(uuid.uuid4()),
                'tenant_id': None,  # Global metrics
                'date': date.isoformat(),
                'hour': None,  # Daily aggregation
                'total_validations': 100 - (days_back * 5),
                'successful_validations': 85 - (days_back * 3),
                'failed_validations': 15 - (days_back * 2),
                'avg_processing_time_ms': 250.5 + (days_back * 10),
                'avg_score': 92.5 - (days_back * 1.5),
                'total_violations': 25 - (days_back * 2),
                'unique_documents': 95 - (days_back * 4),
                'top_rulesets': '["UCP600", "ISBP745", "URC522"]'
            }

            cursor.execute('''
                INSERT OR IGNORE INTO validation_log_metrics
                (id, tenant_id, date, hour, total_validations, successful_validations,
                 failed_validations, avg_processing_time_ms, avg_score, total_violations,
                 unique_documents, top_rulesets)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (daily_metrics['id'], daily_metrics['tenant_id'], daily_metrics['date'],
                  daily_metrics['hour'], daily_metrics['total_validations'],
                  daily_metrics['successful_validations'], daily_metrics['failed_validations'],
                  daily_metrics['avg_processing_time_ms'], daily_metrics['avg_score'],
                  daily_metrics['total_violations'], daily_metrics['unique_documents'],
                  daily_metrics['top_rulesets']))

        print("[OK] Created sample validation metrics")

        # Commit all changes
        conn.commit()
        print("\n[SUCCESS] All Phase C Validation Logs enhancements created successfully!")

        # Show table summary
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name LIKE '%validation%' OR name LIKE '%log%' ORDER BY name;")
        tables = cursor.fetchall()
        print(f"\nValidation/Log related tables:")
        for table in tables:
            print(f"  - {table[0]}")

    except Exception as e:
        print(f"[ERROR] Error creating enhancements: {e}")
        conn.rollback()
        raise
    finally:
        conn.close()

if __name__ == "__main__":
    if not os.path.exists(db_path):
        print(f"[ERROR] Database file {db_path} not found!")
        exit(1)

    print("[INFO] Creating Phase C Validation Logs enhancements...")
    create_validation_logs_enhancements()