create_rule_versions_table.py

from app.utils.time import utcnow
#!/usr/bin/env python3
"""
Simple script to create rule_versions table for Phase C
This bypasses Alembic migration issues and creates the table directly
"""

import sqlite3
import os
from datetime import datetime

def create_rule_versions_table():
    """Create the rule_versions table directly in SQLite"""
    db_path = "icc_rules.db"

    # Connect to the database
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    try:
        # Create rule_versions table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS rule_versions (
                id TEXT PRIMARY KEY,
                ruleset TEXT NOT NULL,
                version TEXT NOT NULL,
                description TEXT,
                status TEXT NOT NULL DEFAULT 'sandbox',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
                created_by TEXT,
                released_at DATETIME,
                changelog JSON,
                rules_count INTEGER NOT NULL DEFAULT 0,
                parent_version_id TEXT,
                UNIQUE(ruleset, version),
                FOREIGN KEY(parent_version_id) REFERENCES rule_versions(id)
            )
        """)

        # Create indexes
        cursor.execute("CREATE INDEX IF NOT EXISTS ix_rule_versions_ruleset ON rule_versions (ruleset)")
        cursor.execute("CREATE INDEX IF NOT EXISTS ix_rule_versions_status ON rule_versions (status)")
        cursor.execute("CREATE INDEX IF NOT EXISTS ix_rule_versions_created_at ON rule_versions (created_at)")

        # Create default UCP600 v1.0 production version
        import uuid
        default_id = str(uuid.uuid4())
        default_changelog = '[{"article": "General", "change": "Initial version of UCP 600 rules"}]'

        cursor.execute("""
            INSERT OR IGNORE INTO rule_versions
            (id, ruleset, version, description, status, created_at, created_by, released_at, changelog, rules_count)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            default_id,
            "UCP600",
            "1.0",
            "Default UCP 600 version for ICC Documentary Credits",
            "production",
            utcnow().isoformat(),
            "system",
            utcnow().isoformat(),
            default_changelog,
            0
        ))

        conn.commit()
        print("Successfully created rule_versions table")
        print("Successfully seeded default UCP600 v1.0 production version")

        # Verify the table was created
        cursor.execute("SELECT count(*) FROM rule_versions")
        count = cursor.fetchone()[0]
        print(f"Current rule_versions count: {count}")

    except Exception as e:
        print(f"Error creating rule_versions table: {e}")
        conn.rollback()
    finally:
        conn.close()

if __name__ == "__main__":
    create_rule_versions_table()