82 lines
2.9 KiB
Python
82 lines
2.9 KiB
Python
|
"""
|
||
|
|
scripts/sync_platform_urls.py
|
||
|
|
Populates verified direct URLs for YouTube, Spotify, and Instagram,
|
||
|
|
and reports on profile image coverage for the 500 cohort.
|
||
|
|
"""
|
||
|
|
from pathlib import Path
|
||
|
|
import sys
|
||
|
|
import pandas as pd
|
||
|
|
|
||
|
|
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
|
||
|
|
|
||
|
|
def main():
|
||
|
|
print("=" * 65)
|
||
|
|
print("HIFFI INTELLIGENCE: DIRECT SOCIAL URLS & IMAGE AUDIT")
|
||
|
|
print("=" * 65)
|
||
|
|
|
||
|
|
csv_path = ROOT_DIR / "data" / "hiffi_multiplatform_500.csv"
|
||
|
|
if not csv_path.exists():
|
||
|
|
csv_path = ROOT_DIR / "cleaned_artists_inventory.csv"
|
||
|
|
|
||
|
|
df = pd.read_csv(csv_path)
|
||
|
|
name_col = "name" if "name" in df.columns else "artist_name"
|
||
|
|
|
||
|
|
conn = get_connection()
|
||
|
|
cur = conn.cursor()
|
||
|
|
|
||
|
|
updated = 0
|
||
|
|
for _, row in df.iterrows():
|
||
|
|
name = str(row[name_col]).strip()
|
||
|
|
|
||
|
|
ch_id = str(row["channel_id"]).strip() if pd.notna(row.get("channel_id")) else ""
|
||
|
|
yt_url = f"https://www.youtube.com/channel/{ch_id}" if ch_id and ch_id.lower() not in ["none", "nan"] else None
|
||
|
|
|
||
|
|
sp_id = str(row["spotify_id"]).strip() if pd.notna(row.get("spotify_id")) else ""
|
||
|
|
sp_url = f"https://open.spotify.com/artist/{sp_id}" if sp_id and sp_id.lower() not in ["none", "nan"] else None
|
||
|
|
|
||
|
|
ig_raw = str(row["instagram_handle"]).strip().lstrip("@") if pd.notna(row.get("instagram_handle")) else ""
|
||
|
|
ig_url = f"https://www.instagram.com/{ig_raw}/" if ig_raw and ig_raw.lower() not in ["none", "nan"] else None
|
||
|
|
|
||
|
|
cur.execute("""
|
||
|
|
UPDATE artists
|
||
|
|
SET youtube_url = ?,
|
||
|
|
spotify_url = ?,
|
||
|
|
instagram_url = ?
|
||
|
|
WHERE LOWER(artist_name) = LOWER(?)
|
||
|
|
""", (yt_url, sp_url, ig_url, name))
|
||
|
|
|
||
|
|
if cur.rowcount > 0:
|
||
|
|
updated += 1
|
||
|
|
|
||
|
|
conn.commit()
|
||
|
|
print(f"Direct platform URLs successfully populated for {updated} artists.")
|
||
|
|
|
||
|
|
# Image Audit across the 500 cohort
|
||
|
|
cohort = [str(n).strip() for n in df[name_col].dropna()]
|
||
|
|
placeholders = ",".join(["?"] * len(cohort))
|
||
|
|
cur.execute(f"""
|
||
|
|
SELECT
|
||
|
|
COUNT(*) as total,
|
||
|
|
SUM(CASE WHEN image_url IS NOT NULL AND image_url != '' THEN 1 ELSE 0 END) as with_images,
|
||
|
|
SUM(CASE WHEN image_url IS NULL OR image_url == '' THEN 1 ELSE 0 END) as missing_images,
|
||
|
|
SUM(CASE WHEN image_verified_flag = 1 THEN 1 ELSE 0 END) as verified_images
|
||
|
|
FROM artists
|
||
|
|
WHERE artist_name IN ({placeholders})
|
||
|
|
""", cohort)
|
||
|
|
img_stats = cur.fetchone()
|
||
|
|
|
||
|
|
print("\n--- COHORT PROFILE IMAGE STATUS ---")
|
||
|
|
print(f"Total Cohort Artists : {img_stats[0]}")
|
||
|
|
print(f"Artists with Images : {img_stats[1]}")
|
||
|
|
print(f"Missing Images : {img_stats[2]}")
|
||
|
|
print(f"Verified Images Flag : {img_stats[3]}")
|
||
|
|
print("=" * 65)
|
||
|
|
|
||
|
|
conn.close()
|
||
|
|
|
||
|
|
if __name__ == "__main__":
|
||
|
|
main()
|