import sqlite3
from pathlib import Path
from datetime import datetime, timezone
import pandas as pd

PROJECT_ROOT = Path(__file__).resolve().parent.parent
DB_PATH = PROJECT_ROOT / "data" / "hiffi_catalog.db"

conn = sqlite3.connect(DB_PATH)

query = """
    SELECT 
        a.artist_id,
        a.artist_name,
        a.country_origin,
        a.youtube_url,
        t.subscribers_count AS youtube_subscribers,
        t.total_views AS youtube_total_views,
        a.spotify_url,
        t.spotify_monthly_listeners,
        a.instagram_url,
        t.instagram_followers,
        t.last_synced_at
    FROM artists a
    JOIN telemetry_metrics t ON a.artist_id = t.artist_id
    WHERE a.image_verified_flag = 1
    ORDER BY t.spotify_monthly_listeners DESC
"""

df = pd.read_sql_query(query, conn)
conn.close()

# Format clean numeric indicators for audit reporting
df["youtube_subscribers_formatted"] = df["youtube_subscribers"].apply(lambda x: f"{int(x):,}" if pd.notnull(x) else "0")
df["spotify_listeners_formatted"] = df["spotify_monthly_listeners"].apply(lambda x: f"{int(x):,}" if pd.notnull(x) else "0")
df["instagram_followers_formatted"] = df["instagram_followers"].apply(lambda x: f"{int(x):,}" if pd.notnull(x) else "0")

# Telemetry Integrity Verification Flags
df["has_canonical_yt"] = df["youtube_url"].str.contains(r"youtube\.com/channel/UC", regex=True, na=False)
df["has_canonical_sp"] = df["spotify_url"].str.contains(r"open\.spotify\.com/artist/[0-9A-Za-z]{22}", regex=True, na=False)
df["has_canonical_ig"] = df["instagram_url"].str.contains(r"instagram\.com/[A-Za-z0-9_.]+", regex=True, na=False)

df["telemetry_verified"] = (
    (df["youtube_subscribers"] > 0) &
    (df["spotify_monthly_listeners"] > 0) &
    (df["instagram_followers"] > 0) &
    df["has_canonical_yt"] &
    df["has_canonical_sp"] &
    df["has_canonical_ig"]
)

# Export verified proof ledger
proof_path = PROJECT_ROOT / "data" / "hiffi_verified_telemetry_proof_500.csv"
df.to_csv(proof_path, index=False, encoding="utf-8")

print("=====================================================================")
print("          HIFFI 500-ARTIST TELEMETRY PROOF AUDIT REPORT              ")
print(f"Timestamp (UTC): {datetime.now(timezone.utc).strftime('%Y-%m-%d %H:%M:%S')}")
print("=====================================================================")
print(f"Total Cohort Size:                    {len(df)}")
print(f"Canonical YouTube Profiles (UC...):   {df['has_canonical_yt'].sum()} / {len(df)}")
print(f"Canonical Spotify Profiles (22-char): {df['has_canonical_sp'].sum()} / {len(df)}")
print(f"Canonical Instagram Profiles:         {df['has_canonical_ig'].sum()} / {len(df)}")
print(f"100% Fully Verified Telemetry Proof:  {df['telemetry_verified'].sum()} / {len(df)}")
print("---------------------------------------------------------------------")
print(f"Saved complete verifiable audit ledger to:\n{proof_path}")