create_phase_c_tables_fixed.py

#!/usr/bin/env python3
"""
Create Phase C Rules tables with proper names to avoid conflicts
"""
import sqlite3
import uuid
import os

# Database file path
db_path = "icc_rules.db"

def create_phase_c_tables():
    """Create Phase C Rules tables"""
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    try:
        # Check if approval columns exist in rules table first
        cursor.execute("PRAGMA table_info(rules);")
        columns = [col[1] for col in cursor.fetchall()]

        # Add approval workflow columns to rules table if they don't exist
        if 'approval_status' not in columns:
            print("Adding approval workflow columns to rules table...")
            cursor.execute("ALTER TABLE rules ADD COLUMN approval_status TEXT DEFAULT 'draft' NOT NULL;")
            cursor.execute("ALTER TABLE rules ADD COLUMN approved_by TEXT;")
            cursor.execute("ALTER TABLE rules ADD COLUMN approved_at DATETIME;")
            cursor.execute("ALTER TABLE rules ADD COLUMN rejection_reason TEXT;")
            conn.commit()
            print("[OK] Added approval workflow columns to rules table")
        else:
            print("[OK] Approval workflow columns already exist in rules table")

        # Create rule_change_history table (renamed from rule_versions to avoid conflict)
        print("Creating rule_change_history table...")
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS rule_change_history (
                id TEXT PRIMARY KEY,
                rule_db_id TEXT NOT NULL,  -- references rules.id
                rule_id TEXT NOT NULL,     -- the rule's rule_id field
                version_number INTEGER NOT NULL,
                content TEXT NOT NULL,     -- JSON stored as TEXT
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                created_by TEXT,
                change_summary TEXT
            );
        ''')
        cursor.execute('CREATE INDEX IF NOT EXISTS ix_rule_change_history_rule_db_id ON rule_change_history (rule_db_id);')
        cursor.execute('CREATE INDEX IF NOT EXISTS ix_rule_change_history_rule_id ON rule_change_history (rule_id);')
        cursor.execute('CREATE INDEX IF NOT EXISTS ix_rule_change_history_created_at ON rule_change_history (created_at);')
        print("[OK] Created rule_change_history table with indexes")

        # Create rule_usage_analytics table
        print("Creating rule_usage_analytics table...")
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS rule_usage_analytics (
                id TEXT PRIMARY KEY,
                rule_db_id TEXT NOT NULL,  -- references rules.id
                rule_id TEXT NOT NULL,     -- the rule's rule_id field
                applied_count INTEGER DEFAULT 0 NOT NULL,
                violation_count INTEGER DEFAULT 0 NOT NULL,
                last_used DATETIME,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
            );
        ''')
        cursor.execute('CREATE UNIQUE INDEX IF NOT EXISTS ix_rule_usage_analytics_rule_db_id ON rule_usage_analytics (rule_db_id);')
        cursor.execute('CREATE INDEX IF NOT EXISTS ix_rule_usage_analytics_rule_id ON rule_usage_analytics (rule_id);')
        cursor.execute('CREATE INDEX IF NOT EXISTS ix_rule_usage_analytics_last_used ON rule_usage_analytics (last_used);')
        print("[OK] Created rule_usage_analytics table with indexes")

        # Create rule_templates table
        print("Creating rule_templates table...")
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS rule_templates (
                id TEXT PRIMARY KEY,
                name TEXT NOT NULL,
                description TEXT,
                category TEXT NOT NULL,
                template_content TEXT NOT NULL,  -- JSON stored as TEXT
                usage_count INTEGER DEFAULT 0 NOT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
            );
        ''')
        cursor.execute('CREATE UNIQUE INDEX IF NOT EXISTS ix_rule_templates_name ON rule_templates (name);')
        cursor.execute('CREATE INDEX IF NOT EXISTS ix_rule_templates_category ON rule_templates (category);')
        print("[OK] Created rule_templates table with indexes")

        # Create rule_audit_logs table
        print("Creating rule_audit_logs table...")
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS rule_audit_logs (
                id TEXT PRIMARY KEY,
                rule_db_id TEXT,           -- references rules.id
                rule_id TEXT NOT NULL,     -- the rule's rule_id field
                action TEXT NOT NULL,      -- created, updated, deleted, approved, rejected, tested
                user_id TEXT,
                tenant_id TEXT,
                before_state TEXT,         -- JSON stored as TEXT
                after_state TEXT,          -- JSON stored as TEXT
                diff_summary TEXT,
                ip_address TEXT,
                user_agent TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
            );
        ''')
        cursor.execute('CREATE INDEX IF NOT EXISTS ix_rule_audit_logs_rule_db_id ON rule_audit_logs (rule_db_id);')
        cursor.execute('CREATE INDEX IF NOT EXISTS ix_rule_audit_logs_rule_id ON rule_audit_logs (rule_id);')
        cursor.execute('CREATE INDEX IF NOT EXISTS ix_rule_audit_logs_action ON rule_audit_logs (action);')
        cursor.execute('CREATE INDEX IF NOT EXISTS ix_rule_audit_logs_user_id ON rule_audit_logs (user_id);')
        cursor.execute('CREATE INDEX IF NOT EXISTS ix_rule_audit_logs_created_at ON rule_audit_logs (created_at);')
        print("[OK] Created rule_audit_logs table with indexes")

        # Create rule_test_results table
        print("Creating rule_test_results table...")
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS rule_test_results (
                id TEXT PRIMARY KEY,
                rule_db_id TEXT,           -- references rules.id
                rule_id TEXT NOT NULL,     -- the rule's rule_id field
                test_input TEXT NOT NULL,  -- JSON stored as TEXT
                test_result TEXT NOT NULL, -- JSON stored as TEXT
                passed INTEGER NOT NULL,   -- Boolean as INTEGER (0/1)
                execution_time_ms REAL,
                tested_by TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
            );
        ''')
        cursor.execute('CREATE INDEX IF NOT EXISTS ix_rule_test_results_rule_db_id ON rule_test_results (rule_db_id);')
        cursor.execute('CREATE INDEX IF NOT EXISTS ix_rule_test_results_rule_id ON rule_test_results (rule_id);')
        cursor.execute('CREATE INDEX IF NOT EXISTS ix_rule_test_results_created_at ON rule_test_results (created_at);')
        print("[OK] Created rule_test_results table with indexes")

        # Insert sample rule templates
        print("Inserting sample rule templates...")
        sample_templates = [
            {
                'id': str(uuid.uuid4()),
                'name': 'Document Amount Validation',
                'description': 'Template for validating document amounts against limits',
                'category': 'Financial',
                'template_content': '''{
                    "conditions": [
                        {
                            "field": "amount",
                            "operator": "<=",
                            "value": "{{max_amount}}",
                            "error_message": "Amount exceeds maximum limit of {{max_amount}}"
                        }
                    ],
                    "variables": {
                        "max_amount": {
                            "type": "number",
                            "description": "Maximum allowed amount",
                            "default": 100000
                        }
                    }
                }'''
            },
            {
                'id': str(uuid.uuid4()),
                'name': 'Required Fields Check',
                'description': 'Template for validating required document fields',
                'category': 'Validation',
                'template_content': '''{
                    "conditions": [
                        {
                            "field": "{{field_name}}",
                            "operator": "exists",
                            "error_message": "Required field {{field_name}} is missing"
                        }
                    ],
                    "variables": {
                        "field_name": {
                            "type": "string",
                            "description": "Name of the required field",
                            "default": "beneficiary_name"
                        }
                    }
                }'''
            },
            {
                'id': str(uuid.uuid4()),
                'name': 'Date Range Validation',
                'description': 'Template for validating dates within acceptable ranges',
                'category': 'Temporal',
                'template_content': '''{
                    "conditions": [
                        {
                            "field": "{{date_field}}",
                            "operator": "date_between",
                            "value": ["{{start_date}}", "{{end_date}}"],
                            "error_message": "Date must be between {{start_date}} and {{end_date}}"
                        }
                    ],
                    "variables": {
                        "date_field": {
                            "type": "string",
                            "description": "Date field to validate",
                            "default": "issue_date"
                        },
                        "start_date": {
                            "type": "date",
                            "description": "Start of valid date range",
                            "default": "2024-01-01"
                        },
                        "end_date": {
                            "type": "date",
                            "description": "End of valid date range",
                            "default": "2025-12-31"
                        }
                    }
                }'''
            }
        ]

        for template in sample_templates:
            cursor.execute('''
                INSERT OR IGNORE INTO rule_templates (id, name, description, category, template_content, usage_count)
                VALUES (?, ?, ?, ?, ?, 0)
            ''', (template['id'], template['name'], template['description'], template['category'], template['template_content']))

        print(f"[OK] Inserted {len(sample_templates)} sample rule templates")

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

        # Show new tables
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name LIKE '%rule_%' ORDER BY name;")
        tables = cursor.fetchall()
        print(f"\nRule-related tables:")
        for table in tables:
            print(f"  - {table[0]}")

    except Exception as e:
        print(f"[ERROR] Error creating tables: {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 Rules tables...")
    create_phase_c_tables()