hiffi-artist-intelligence/scripts/audit_commercial_sales.py

import sqlite3

def audit_sales_table():
    conn = sqlite3.connect('data/hiffi_catalog.db')
    cur = conn.cursor()
    
    # 1. Check schema
    cur.execute("PRAGMA table_info(commercial_sales)")
    columns = [c[1] for c in cur.fetchall()]
    
    if not columns:
        print("❌ ERROR: commercial_sales table does not exist in the database.")
        conn.close()
        return

    print(f"✅ Table Found. Columns: {', '.join(columns)}\n")
    
    # 2. Check Provenance Flags distribution
    try:
        cur.execute("SELECT provenance_flag, COUNT(*) FROM commercial_sales GROUP BY provenance_flag")
        flags = cur.fetchall()
        print("Provenance Flag Distribution:")
        for flag, count in flags:
            print(f" - {flag}: {count} records")
            
        # 3. Check for NULLs in critical format breakdown columns
        cur.execute("""
            SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN pure_album_sales IS NULL THEN 1 ELSE 0 END) as null_albums,
                SUM(CASE WHEN physical_singles_sales IS NULL THEN 1 ELSE 0 END) as null_physical,
                SUM(CASE WHEN digital_singles_sales IS NULL THEN 1 ELSE 0 END) as null_digital,
                SUM(CASE WHEN streaming_eas IS NULL THEN 1 ELSE 0 END) as null_streaming
            FROM commercial_sales
        """)
        nulls = cur.fetchone()
        print("\nMissing Data (NULL check):")
        print(f"Total Sales Records: {nulls[0]}")
        print(f"Null Pure Albums: {nulls[1]} | Null Physical Singles: {nulls[2]}")
        print(f"Null Digital Singles: {nulls[3]} | Null Streaming EAS: {nulls[4]}")
        
    except sqlite3.OperationalError as e:
        print(f"\n⚠️ Schema Error: {e}. The table is missing required columns.")

    conn.close()

if __name__ == "__main__":
    audit_sales_table()