hiffi-artist-intelligence/scripts/init_catalog_db.py

"""
scripts/init_catalog_db.py
Creates database schema and B-Tree indexes for sub-10ms response times.
"""

import sys
from pathlib import Path

# Ensure project root is in sys.path
ROOT_DIR = Path(__file__).resolve().parent.parent
if str(ROOT_DIR) not in sys.path:
    sys.path.insert(0, str(ROOT_DIR))

from core.database import get_connection

SCHEMA_SQL = """
CREATE TABLE IF NOT EXISTS artists (
    artist_id TEXT PRIMARY KEY,
    artist_name TEXT NOT NULL,
    country_origin TEXT DEFAULT 'Unknown',
    image_url TEXT DEFAULT '',
    image_verified_flag INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS rankings (
    ranking_id INTEGER PRIMARY KEY AUTOINCREMENT,
    artist_id TEXT NOT NULL UNIQUE,
    catalog_rank INTEGER NOT NULL,
    tier_rank INTEGER DEFAULT 0,
    catalog_intelligence_tier TEXT DEFAULT 'Emerging',
    FOREIGN KEY(artist_id) REFERENCES artists(artist_id)
);

CREATE TABLE IF NOT EXISTS commercial_sales (
    sales_id INTEGER PRIMARY KEY AUTOINCREMENT,
    artist_id TEXT NOT NULL UNIQUE,
    audited_sales_units REAL DEFAULT 0.0,
    awards_count INTEGER DEFAULT 0,
    provenance TEXT DEFAULT 'N/A',
    sales_rank INTEGER DEFAULT 0,
    FOREIGN KEY(artist_id) REFERENCES artists(artist_id)
);

CREATE TABLE IF NOT EXISTS telemetry_metrics (
    telemetry_id INTEGER PRIMARY KEY AUTOINCREMENT,
    artist_id TEXT NOT NULL UNIQUE,
    subscribers_count INTEGER DEFAULT 0,
    spotify_monthly_listeners INTEGER DEFAULT 0,
    growth_velocity_7d REAL NOT NULL DEFAULT 0.0,
    growth_velocity_30d REAL NOT NULL DEFAULT 0.0,
    last_synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(artist_id) REFERENCES artists(artist_id)
);

-- Performance B-Tree Indexes
CREATE INDEX IF NOT EXISTS idx_rankings_catalog_rank ON rankings(catalog_rank);
CREATE INDEX IF NOT EXISTS idx_artists_name ON artists(artist_name);
CREATE INDEX IF NOT EXISTS idx_telemetry_artist ON telemetry_metrics(artist_id);
"""


def init_db():
    conn = get_connection()
    try:
        conn.executescript(SCHEMA_SQL)
        conn.commit()
        print("Schema successfully created with B-Tree indexes.")
    finally:
        conn.close()


if __name__ == "__main__":
    init_db()