-- ============================================================
-- Migration: bb.author_profiles
-- Date:      2026-05-20
-- Purpose:   Per-author bio + contact card backing /authors/[slug].
--            Replaces the 540-line static map in src/app/authors/[slug]/page.tsx
--            with a DB-driven profile that works for ALL 160 distinct
--            author_name values in wp_imported_posts (not just the 7 we
--            hand-coded). Join date is computed live from
--            MIN(wp_published_at), never stored, so it stays correct
--            as more legacy posts get backfilled.
-- ============================================================

CREATE TABLE IF NOT EXISTS bb.author_profiles (
  slug              TEXT PRIMARY KEY,                   -- url slug: "desert-moon-communications"
  display_name      TEXT NOT NULL,                      -- "Desert Moon Communications"
  author_names      TEXT[] NOT NULL DEFAULT '{}',       -- name variants in wp_imported_posts.author_name
  role              TEXT NOT NULL DEFAULT 'Contributor',-- "Contributor" | "Founder of Broadcast Beat" | "Senior Editor" | ...
  kind              TEXT NOT NULL DEFAULT 'contributor',-- 'staff' | 'contributor' | 'pr_firm' | 'person'
  bio               TEXT,                               -- AI-generated paragraph
  bio_generated_at  TIMESTAMPTZ,                        -- when Ollama wrote it
  bio_source_model  TEXT,                               -- 'qwen2.5-coder:32b' etc.

  -- contact card
  address_line1     TEXT,
  address_line2     TEXT,
  city              TEXT,
  state             TEXT,
  postal_code       TEXT,
  country           TEXT,
  phone             TEXT,
  email             TEXT,
  website           TEXT,

  -- social
  twitter           TEXT,
  linkedin          TEXT,
  facebook          TEXT,
  instagram         TEXT,

  -- media
  avatar_url        TEXT,
  cover_url         TEXT,

  -- editorial extras
  specialties       TEXT[] DEFAULT '{}',
  location_text     TEXT,                               -- free-text "Los Angeles, CA" fallback if structured fields absent

  meta              JSONB DEFAULT '{}'::jsonb,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS author_profiles_kind_idx ON bb.author_profiles(kind);
CREATE INDEX IF NOT EXISTS author_profiles_names_gin ON bb.author_profiles USING GIN (author_names);

-- Trigger to keep updated_at fresh
CREATE OR REPLACE FUNCTION bb.author_profiles_touch() RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS author_profiles_touch_t ON bb.author_profiles;
CREATE TRIGGER author_profiles_touch_t
  BEFORE UPDATE ON bb.author_profiles
  FOR EACH ROW EXECUTE FUNCTION bb.author_profiles_touch();

-- Convenience view: join date + post count computed live from imports
CREATE OR REPLACE VIEW bb.author_profiles_with_stats AS
SELECT
  p.*,
  s.first_post_date,
  s.last_post_date,
  s.total_articles
FROM bb.author_profiles p
LEFT JOIN LATERAL (
  SELECT
    MIN(wp_published_at)::date AS first_post_date,
    MAX(wp_published_at)::date AS last_post_date,
    COUNT(*)                    AS total_articles
  FROM bb.wp_imported_posts
  WHERE status = 'published'
    AND author_name = ANY(p.author_names)
) s ON true;

-- RLS: read-only to anon, full to service_role
ALTER TABLE bb.author_profiles ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS author_profiles_public_read ON bb.author_profiles;
CREATE POLICY author_profiles_public_read
  ON bb.author_profiles FOR SELECT
  TO anon, authenticated
  USING (true);

DROP POLICY IF EXISTS author_profiles_service_write ON bb.author_profiles;
CREATE POLICY author_profiles_service_write
  ON bb.author_profiles FOR ALL
  TO service_role
  USING (true)
  WITH CHECK (true);

-- Seed Ryan immediately so the live site is correct the moment this lands.
INSERT INTO bb.author_profiles (
  slug, display_name, author_names, role, kind, bio,
  email, website, twitter, linkedin,
  location_text, country,
  avatar_url, cover_url,
  specialties
)
VALUES (
  'ryan-salazar',
  'Ryan Salazar',
  ARRAY['Ryan Salazar'],
  'Founder of Broadcast Beat',
  'staff',
  'Ryan Salazar is the founder of Broadcast Beat Magazine and the Executive Producer of NAB Show LIVE for the National Association of Broadcasters. With more than 20 years in audio, visual, and broadcast technology, Ryan began his career in 1997 as an audio engineer and producer/director at ProComm Studios, then spent seven years as Chief Technology Officer at SunSpots Productions running all AV and IT operations. In 2005 he launched AV Beat to cover the audio-visual industry, and in 2014 transitioned to SMPTE LIVE as Executive Producer and on-camera talent, interviewing industry luminaries including James Cameron, Douglas Trumbull, Billy Zane, and Richard Edlund. Ryan founded Broadcast Beat in December 2013 to give broadcast and production engineers a dedicated trade publication focused on the engineering side of the industry — live production, IP workflows, streaming, cloud, audio, and post. In 2020 he also became President and CTO of Broadcast Beat Studios, a production company based in South Florida. Today he continues to lead Broadcast Beat alongside Relevant Media Properties'' growing portfolio of trade publications.',
  'ryan.salazar@relevantmediaproperties.com',
  'https://www.relevantmediaproperties.com/',
  'https://twitter.com/broadcastbeat',
  'https://linkedin.com/company/broadcastbeat',
  'South Florida, United States',
  'United States',
  '/assets/images/logo.png',
  '/assets/images/og-image.png',
  ARRAY['Broadcast Engineering','Live Production','Industry Strategy','Editorial Direction']
)
ON CONFLICT (slug) DO UPDATE SET
  display_name = EXCLUDED.display_name,
  author_names = EXCLUDED.author_names,
  role = EXCLUDED.role,
  kind = EXCLUDED.kind,
  bio = EXCLUDED.bio,
  email = EXCLUDED.email,
  website = EXCLUDED.website,
  twitter = EXCLUDED.twitter,
  linkedin = EXCLUDED.linkedin,
  location_text = EXCLUDED.location_text,
  country = EXCLUDED.country,
  avatar_url = EXCLUDED.avatar_url,
  cover_url = EXCLUDED.cover_url,
  specialties = EXCLUDED.specialties;