schema.sql

-- Database schema for records-of-the-day.
-- Run this in the Supabase SQL editor for a fresh project, or apply migrations
-- individually from ./migrations/.

-- Enable UUID generation if not already enabled
create extension if not exists pgcrypto;

-- Artists discovered from configured Spotify playlist(s)
create table if not exists public.spotify_artists (
  id uuid primary key default gen_random_uuid(),
  spotify_id text unique not null,
  name text not null,
  spotify_data jsonb not null,
  is_classical boolean default false,
  last_seen_in_playlist_at timestamptz default now(),
  dismissed_at timestamptz,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

-- Featured albums per day (one album per artist per day)
create table if not exists public.spotify_featured_albums (
  id uuid primary key default gen_random_uuid(),
  feature_date date not null,
  spotify_album_id text not null,
  artist_spotify_id text not null,
  album_name text not null,
  artist_name text not null,
  spotify_data jsonb not null,
  created_at timestamptz default now(),
  unique (feature_date, artist_spotify_id)
);

-- Helpful indexes
create index if not exists idx_spotify_artists_last_seen
  on public.spotify_artists(last_seen_in_playlist_at desc);

create index if not exists idx_spotify_artists_dismissed_at
  on public.spotify_artists(dismissed_at) where dismissed_at is not null;

create index if not exists idx_spotify_featured_albums_date
  on public.spotify_featured_albums(feature_date desc);

-- Row Level Security
alter table public.spotify_artists enable row level security;
alter table public.spotify_featured_albums enable row level security;

-- Drop existing policies if re-running
drop policy if exists "Read artists" on public.spotify_artists;
drop policy if exists "Service role insert artists" on public.spotify_artists;
drop policy if exists "Service role update artists" on public.spotify_artists;

drop policy if exists "Read featured albums" on public.spotify_featured_albums;
drop policy if exists "Service role insert featured" on public.spotify_featured_albums;

-- Public read access (the homepage reads featured albums anonymously)
create policy "Read artists" on public.spotify_artists for select
  using (true);

create policy "Read featured albums" on public.spotify_featured_albums for select
  using (true);

-- Only the service_role (used by cron jobs and the dismiss endpoint) can write
create policy "Service role insert artists" on public.spotify_artists for insert
  with check (auth.role() = 'service_role');

create policy "Service role update artists" on public.spotify_artists for update
  using (auth.role() = 'service_role') with check (auth.role() = 'service_role');

create policy "Service role insert featured" on public.spotify_featured_albums for insert
  with check (auth.role() = 'service_role');