-- =============================================================================
-- Migration 66 — Social Engine Foundation
-- =============================================================================
-- Date:        2026-05-03
-- Purpose:     Schema for auto-posting to Instagram + Facebook + LinkedIn + TikTok
--              with full UTM-based attribution (post → signup → paid customer).
-- Cost:        €0 — runs on existing Supabase (free tier covers expected load).
-- Idempotency: Safe to re-run. All CREATE statements use IF NOT EXISTS.
-- =============================================================================

BEGIN;

-- -----------------------------------------------------------------------------
-- 1. Extensions
-- -----------------------------------------------------------------------------
CREATE EXTENSION IF NOT EXISTS pgcrypto;     -- gen_random_uuid()
CREATE EXTENSION IF NOT EXISTS pg_trgm;      -- fuzzy search on captions

-- -----------------------------------------------------------------------------
-- 2. Enums
-- -----------------------------------------------------------------------------
DO $$ BEGIN
  CREATE TYPE social_network AS ENUM ('facebook', 'instagram', 'linkedin', 'tiktok');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE TYPE social_post_status AS ENUM (
    'draft',         -- created, not yet ready
    'scheduled',     -- has scheduled_at, awaiting publisher
    'publishing',    -- publisher fn picked it up
    'published',     -- live on at least one network
    'partial',       -- live on some networks, failed on others
    'failed',        -- all targets failed
    'cancelled'      -- manually cancelled before publish
  );
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE TYPE social_target_status AS ENUM (
    'pending', 'publishing', 'published', 'failed', 'skipped'
  );
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE TYPE social_post_format AS ENUM (
    'image', 'carousel', 'video', 'reel', 'story', 'text_only'
  );
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE TYPE social_attribution_kind AS ENUM (
    'first_touch', 'last_touch', 'multi_touch', 'direct'
  );
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE TYPE tiktok_publish_mode AS ENUM ('upload', 'direct_post');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- -----------------------------------------------------------------------------
-- 3. Tables
-- -----------------------------------------------------------------------------

-- 3.1 social_accounts — connected handles per network
CREATE TABLE IF NOT EXISTS public.social_accounts (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  network         social_network NOT NULL,
  handle          text NOT NULL,
  display_name    text NOT NULL,
  external_id     text NOT NULL,                  -- FB Page ID, IG Business ID, LI Org URN, TT Open ID
  publish_enabled boolean NOT NULL DEFAULT false, -- master kill switch per account
  tiktok_mode     tiktok_publish_mode DEFAULT 'upload',
  -- Token storage: NEVER store tokens in this table — they live in Supabase Vault / secrets.
  -- We only store a fingerprint to detect rotation.
  token_fingerprint text,
  token_expires_at  timestamptz,
  metadata        jsonb DEFAULT '{}'::jsonb,
  created_at      timestamptz NOT NULL DEFAULT NOW(),
  updated_at      timestamptz NOT NULL DEFAULT NOW(),
  CONSTRAINT social_accounts_network_handle_uniq UNIQUE (network, handle)
);

CREATE INDEX IF NOT EXISTS social_accounts_enabled_idx
  ON public.social_accounts (network) WHERE publish_enabled = true;

-- 3.2 social_posts — one logical post (master record, language-specific)
CREATE TABLE IF NOT EXISTS public.social_posts (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  post_slug       text NOT NULL UNIQUE,           -- post_017_de_organizer_hours_not_weeks
  campaign_tag    text NOT NULL,                  -- hotel_intake_template_2026q2
  language        text NOT NULL CHECK (language IN ('en','es','de','fr','it','pt')),
  audience        text NOT NULL CHECK (audience IN ('organizer','hotel','mixed')),
  format          social_post_format NOT NULL DEFAULT 'image',
  caption         text NOT NULL,
  hashtags        text[] DEFAULT ARRAY[]::text[],
  cta_text        text,                           -- "DM us 'rfp'" / "comment your city"
  link_url        text,                           -- canonical destination (UTMs added at publish time per network)
  media_paths     text[] DEFAULT ARRAY[]::text[], -- supabase storage refs
  scheduled_at    timestamptz,                    -- NULL = not yet scheduled
  status          social_post_status NOT NULL DEFAULT 'draft',
  published_at    timestamptz,
  metadata        jsonb DEFAULT '{}'::jsonb,
  created_by      uuid REFERENCES auth.users(id),
  created_at      timestamptz NOT NULL DEFAULT NOW(),
  updated_at      timestamptz NOT NULL DEFAULT NOW(),
  -- Coherence guards
  CONSTRAINT social_posts_caption_not_empty CHECK (length(trim(caption)) > 0),
  CONSTRAINT social_posts_no_old_prices CHECK (
    caption !~* '€45|€120|49\s*EUR|129\s*EUR'
  ),
  CONSTRAINT social_posts_no_hotels_pay CHECK (
    caption !~* 'hotels?\s+pay|hotels?\s+subscribe|paid\s+by\s+hotels?'
  ),
  CONSTRAINT social_posts_no_beta_markers CHECK (
    caption !~* 'coming\s+soon|join\s+waitlist|launches?\s+later'
  )
);

CREATE INDEX IF NOT EXISTS social_posts_scheduled_idx
  ON public.social_posts (scheduled_at)
  WHERE status = 'scheduled' AND scheduled_at IS NOT NULL;
CREATE INDEX IF NOT EXISTS social_posts_status_idx ON public.social_posts (status);
CREATE INDEX IF NOT EXISTS social_posts_campaign_idx ON public.social_posts (campaign_tag);
CREATE INDEX IF NOT EXISTS social_posts_language_idx ON public.social_posts (language);
CREATE INDEX IF NOT EXISTS social_posts_caption_trgm_idx
  ON public.social_posts USING gin (caption gin_trgm_ops);

-- 3.3 social_post_targets — fan-out: 1 post → N networks
CREATE TABLE IF NOT EXISTS public.social_post_targets (
  id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id               uuid NOT NULL REFERENCES public.social_posts(id) ON DELETE CASCADE,
  network               social_network NOT NULL,
  account_id            uuid NOT NULL REFERENCES public.social_accounts(id),
  status                social_target_status NOT NULL DEFAULT 'pending',
  -- Network-specific final URL (with UTMs baked in for THIS network)
  final_link_url        text,
  -- Set by publisher fn after success
  platform_post_id      text,                     -- IG media id / FB post id / LI URN / TT video id
  platform_post_url     text,                     -- public URL of the live post
  published_at          timestamptz,
  -- Errors
  attempt_count         integer NOT NULL DEFAULT 0,
  last_error            text,
  last_error_at         timestamptz,
  -- Metadata
  metadata              jsonb DEFAULT '{}'::jsonb,
  created_at            timestamptz NOT NULL DEFAULT NOW(),
  updated_at            timestamptz NOT NULL DEFAULT NOW(),
  CONSTRAINT social_post_targets_uniq UNIQUE (post_id, network)
);

CREATE INDEX IF NOT EXISTS social_post_targets_pending_idx
  ON public.social_post_targets (network, status) WHERE status = 'pending';
CREATE INDEX IF NOT EXISTS social_post_targets_failed_idx
  ON public.social_post_targets (network, last_error_at) WHERE status = 'failed';

-- 3.4 social_metrics — daily snapshots per (target, day)
CREATE TABLE IF NOT EXISTS public.social_metrics (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id         uuid NOT NULL REFERENCES public.social_posts(id) ON DELETE CASCADE,
  target_id       uuid NOT NULL REFERENCES public.social_post_targets(id) ON DELETE CASCADE,
  network         social_network NOT NULL,
  snapshot_date   date NOT NULL,
  -- Network-agnostic counters (NULL where API doesn't expose it)
  impressions     integer,
  reach           integer,
  likes           integer,
  comments        integer,
  shares          integer,
  saves           integer,
  video_views     integer,
  click_through   integer,                        -- clicks to outbound link
  profile_visits  integer,
  engagement_rate numeric(5,2),                   -- precomputed: (likes+comments+shares) / impressions * 100
  raw             jsonb DEFAULT '{}'::jsonb,      -- full API payload for debugging
  fetched_at      timestamptz NOT NULL DEFAULT NOW(),
  CONSTRAINT social_metrics_uniq UNIQUE (target_id, snapshot_date)
);

CREATE INDEX IF NOT EXISTS social_metrics_post_idx ON public.social_metrics (post_id);
CREATE INDEX IF NOT EXISTS social_metrics_network_date_idx
  ON public.social_metrics (network, snapshot_date DESC);

-- 3.5 social_attribution — UTM → user_id → paid customer chain
CREATE TABLE IF NOT EXISTS public.social_attribution (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id             uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  -- First-touch UTMs
  first_touch_at      timestamptz,
  first_source        social_network,
  first_medium        text,
  first_campaign      text,
  first_content       text,                       -- post_slug
  first_term          text,                       -- language
  -- Last-touch UTMs
  last_touch_at       timestamptz,
  last_source         social_network,
  last_medium         text,
  last_campaign       text,
  last_content        text,
  last_term           text,
  -- Convenience denorm (default report uses first-touch)
  source              social_network,
  post_slug           text,                       -- first-touch post for default reports
  campaign            text,
  language            text,
  attribution_kind    social_attribution_kind NOT NULL DEFAULT 'first_touch',
  -- Conversion ladder
  signup_at           timestamptz NOT NULL DEFAULT NOW(),
  paid_at             timestamptz,
  mrr_eur             numeric(10,2),
  stripe_subscription_id text,
  metadata            jsonb DEFAULT '{}'::jsonb,
  created_at          timestamptz NOT NULL DEFAULT NOW(),
  updated_at          timestamptz NOT NULL DEFAULT NOW(),
  CONSTRAINT social_attribution_user_uniq UNIQUE (user_id)
);

CREATE INDEX IF NOT EXISTS social_attribution_post_idx
  ON public.social_attribution (post_slug) WHERE post_slug IS NOT NULL;
CREATE INDEX IF NOT EXISTS social_attribution_source_idx
  ON public.social_attribution (source);
CREATE INDEX IF NOT EXISTS social_attribution_paid_idx
  ON public.social_attribution (paid_at) WHERE paid_at IS NOT NULL;
CREATE INDEX IF NOT EXISTS social_attribution_signup_idx
  ON public.social_attribution (signup_at DESC);

-- -----------------------------------------------------------------------------
-- 4. RLS — admin-only on writes; public read blocked
-- -----------------------------------------------------------------------------
ALTER TABLE public.social_accounts        ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.social_posts           ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.social_post_targets    ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.social_metrics         ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.social_attribution     ENABLE ROW LEVEL SECURITY;

-- service_role bypasses RLS by default; for authenticated admin users we gate via JWT claim
DROP POLICY IF EXISTS "social_accounts_admin_all" ON public.social_accounts;
CREATE POLICY "social_accounts_admin_all" ON public.social_accounts
  FOR ALL TO authenticated
  USING ((auth.jwt() ->> 'is_admin')::boolean = true)
  WITH CHECK ((auth.jwt() ->> 'is_admin')::boolean = true);

DROP POLICY IF EXISTS "social_posts_admin_all" ON public.social_posts;
CREATE POLICY "social_posts_admin_all" ON public.social_posts
  FOR ALL TO authenticated
  USING ((auth.jwt() ->> 'is_admin')::boolean = true)
  WITH CHECK ((auth.jwt() ->> 'is_admin')::boolean = true);

DROP POLICY IF EXISTS "social_post_targets_admin_all" ON public.social_post_targets;
CREATE POLICY "social_post_targets_admin_all" ON public.social_post_targets
  FOR ALL TO authenticated
  USING ((auth.jwt() ->> 'is_admin')::boolean = true)
  WITH CHECK ((auth.jwt() ->> 'is_admin')::boolean = true);

DROP POLICY IF EXISTS "social_metrics_admin_all" ON public.social_metrics;
CREATE POLICY "social_metrics_admin_all" ON public.social_metrics
  FOR ALL TO authenticated
  USING ((auth.jwt() ->> 'is_admin')::boolean = true)
  WITH CHECK ((auth.jwt() ->> 'is_admin')::boolean = true);

DROP POLICY IF EXISTS "social_attribution_self_read" ON public.social_attribution;
CREATE POLICY "social_attribution_self_read" ON public.social_attribution
  FOR SELECT TO authenticated
  USING (user_id = auth.uid() OR (auth.jwt() ->> 'is_admin')::boolean = true);

-- -----------------------------------------------------------------------------
-- 5. Trigger — keep updated_at fresh
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.tg_set_updated_at() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN NEW.updated_at = NOW(); RETURN NEW; END $$;

DROP TRIGGER IF EXISTS tg_social_accounts_updated_at      ON public.social_accounts;
CREATE TRIGGER tg_social_accounts_updated_at      BEFORE UPDATE ON public.social_accounts      FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

DROP TRIGGER IF EXISTS tg_social_posts_updated_at         ON public.social_posts;
CREATE TRIGGER tg_social_posts_updated_at         BEFORE UPDATE ON public.social_posts         FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

DROP TRIGGER IF EXISTS tg_social_post_targets_updated_at  ON public.social_post_targets;
CREATE TRIGGER tg_social_post_targets_updated_at  BEFORE UPDATE ON public.social_post_targets  FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

DROP TRIGGER IF EXISTS tg_social_attribution_updated_at   ON public.social_attribution;
CREATE TRIGGER tg_social_attribution_updated_at   BEFORE UPDATE ON public.social_attribution   FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

-- -----------------------------------------------------------------------------
-- 6. RPC — fetch_due_posts (called by social-publisher cron)
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.fetch_due_posts(
  p_now      timestamptz DEFAULT NOW(),
  p_limit    integer     DEFAULT 20
) RETURNS TABLE (
  post_id          uuid,
  post_slug        text,
  language         text,
  caption          text,
  hashtags         text[],
  cta_text         text,
  link_url         text,
  media_paths      text[],
  format           social_post_format,
  scheduled_at     timestamptz,
  target_id        uuid,
  network          social_network,
  account_id       uuid,
  account_external_id text,
  account_handle   text
)
LANGUAGE sql SECURITY DEFINER STABLE AS $$
  SELECT
    sp.id, sp.post_slug, sp.language, sp.caption, sp.hashtags, sp.cta_text,
    sp.link_url, sp.media_paths, sp.format, sp.scheduled_at,
    spt.id, spt.network, spt.account_id, sa.external_id, sa.handle
  FROM public.social_posts sp
  JOIN public.social_post_targets spt ON spt.post_id = sp.id
  JOIN public.social_accounts sa      ON sa.id = spt.account_id
  WHERE sp.status IN ('scheduled')
    AND sp.scheduled_at <= p_now
    AND spt.status = 'pending'
    AND sa.publish_enabled = true
    AND spt.attempt_count < 3
  ORDER BY sp.scheduled_at ASC, spt.created_at ASC
  LIMIT p_limit;
$$;

REVOKE ALL ON FUNCTION public.fetch_due_posts(timestamptz, integer) FROM public, anon;
GRANT EXECUTE ON FUNCTION public.fetch_due_posts(timestamptz, integer) TO service_role;

-- -----------------------------------------------------------------------------
-- 7. RPC — mark_target_published (called after successful publish)
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.mark_target_published(
  p_target_id          uuid,
  p_platform_post_id   text,
  p_platform_post_url  text
) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
  v_post_id uuid;
  v_remaining integer;
BEGIN
  UPDATE public.social_post_targets
  SET status = 'published',
      platform_post_id = p_platform_post_id,
      platform_post_url = p_platform_post_url,
      published_at = NOW(),
      last_error = NULL
  WHERE id = p_target_id
  RETURNING post_id INTO v_post_id;

  IF v_post_id IS NULL THEN
    RAISE EXCEPTION 'target % not found', p_target_id;
  END IF;

  -- Roll up to parent post
  SELECT count(*) INTO v_remaining
  FROM public.social_post_targets
  WHERE post_id = v_post_id AND status IN ('pending','publishing');

  IF v_remaining = 0 THEN
    UPDATE public.social_posts
    SET status = CASE
      WHEN EXISTS (
        SELECT 1 FROM public.social_post_targets
        WHERE post_id = v_post_id AND status = 'failed'
      ) THEN 'partial'::social_post_status
      ELSE 'published'::social_post_status
    END,
    published_at = COALESCE(published_at, NOW())
    WHERE id = v_post_id;
  END IF;
END $$;

REVOKE ALL ON FUNCTION public.mark_target_published(uuid, text, text) FROM public, anon;
GRANT EXECUTE ON FUNCTION public.mark_target_published(uuid, text, text) TO service_role;

-- -----------------------------------------------------------------------------
-- 8. RPC — mark_target_failed
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.mark_target_failed(
  p_target_id  uuid,
  p_error      text
) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
  v_post_id uuid;
  v_attempts integer;
BEGIN
  UPDATE public.social_post_targets
  SET attempt_count = attempt_count + 1,
      last_error = p_error,
      last_error_at = NOW(),
      status = CASE
        WHEN attempt_count + 1 >= 3 THEN 'failed'::social_target_status
        ELSE 'pending'::social_target_status
      END
  WHERE id = p_target_id
  RETURNING post_id, attempt_count INTO v_post_id, v_attempts;

  -- Roll up parent if all targets are terminal
  UPDATE public.social_posts sp
  SET status = CASE
    WHEN NOT EXISTS (
      SELECT 1 FROM public.social_post_targets
      WHERE post_id = v_post_id AND status IN ('pending','publishing')
    ) AND EXISTS (
      SELECT 1 FROM public.social_post_targets
      WHERE post_id = v_post_id AND status = 'published'
    ) THEN 'partial'::social_post_status
    WHEN NOT EXISTS (
      SELECT 1 FROM public.social_post_targets
      WHERE post_id = v_post_id AND status IN ('pending','publishing','published')
    ) THEN 'failed'::social_post_status
    ELSE sp.status
  END
  WHERE sp.id = v_post_id;
END $$;

REVOKE ALL ON FUNCTION public.mark_target_failed(uuid, text) FROM public, anon;
GRANT EXECUTE ON FUNCTION public.mark_target_failed(uuid, text) TO service_role;

-- -----------------------------------------------------------------------------
-- 9. RPC — attribute_signup_to_post (called by webhook on auth.users insert)
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.attribute_signup_to_post(
  p_user_id          uuid,
  p_first_touch_utm  jsonb,
  p_last_touch_utm   jsonb
) RETURNS uuid
LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
  v_attribution_id uuid;
  v_first_source  social_network;
  v_last_source   social_network;
  v_kind          social_attribution_kind := 'direct';
BEGIN
  -- Coerce source strings to enum if valid
  BEGIN v_first_source := (p_first_touch_utm ->> 'source')::social_network; EXCEPTION WHEN OTHERS THEN v_first_source := NULL; END;
  BEGIN v_last_source  := (p_last_touch_utm  ->> 'source')::social_network; EXCEPTION WHEN OTHERS THEN v_last_source  := NULL; END;

  IF v_first_source IS NOT NULL THEN
    v_kind := CASE
      WHEN v_first_source = v_last_source THEN 'first_touch'
      WHEN v_last_source IS NOT NULL THEN 'multi_touch'
      ELSE 'first_touch'
    END;
  END IF;

  INSERT INTO public.social_attribution (
    user_id,
    first_touch_at, first_source, first_medium, first_campaign, first_content, first_term,
    last_touch_at,  last_source,  last_medium,  last_campaign,  last_content,  last_term,
    source, post_slug, campaign, language, attribution_kind,
    signup_at
  )
  VALUES (
    p_user_id,
    to_timestamp(((p_first_touch_utm->>'t')::bigint) / 1000.0),
    v_first_source,
    p_first_touch_utm->>'medium',
    p_first_touch_utm->>'campaign',
    p_first_touch_utm->>'content',
    p_first_touch_utm->>'term',
    to_timestamp(((p_last_touch_utm->>'t')::bigint) / 1000.0),
    v_last_source,
    p_last_touch_utm->>'medium',
    p_last_touch_utm->>'campaign',
    p_last_touch_utm->>'content',
    p_last_touch_utm->>'term',
    v_first_source,                              -- default report = first-touch
    p_first_touch_utm->>'content',
    p_first_touch_utm->>'campaign',
    p_first_touch_utm->>'term',
    v_kind,
    NOW()
  )
  ON CONFLICT (user_id) DO UPDATE
    SET last_touch_at  = EXCLUDED.last_touch_at,
        last_source    = EXCLUDED.last_source,
        last_medium    = EXCLUDED.last_medium,
        last_campaign  = EXCLUDED.last_campaign,
        last_content   = EXCLUDED.last_content,
        last_term      = EXCLUDED.last_term,
        attribution_kind = EXCLUDED.attribution_kind
  RETURNING id INTO v_attribution_id;

  RETURN v_attribution_id;
END $$;

REVOKE ALL ON FUNCTION public.attribute_signup_to_post(uuid, jsonb, jsonb) FROM public, anon;
GRANT EXECUTE ON FUNCTION public.attribute_signup_to_post(uuid, jsonb, jsonb) TO service_role;

-- -----------------------------------------------------------------------------
-- 10. RPC — stamp_paid_attribution (called by Stripe webhook)
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.stamp_paid_attribution(
  p_user_id                uuid,
  p_mrr_eur                numeric,
  p_stripe_subscription_id text
) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
  UPDATE public.social_attribution
  SET paid_at = COALESCE(paid_at, NOW()),
      mrr_eur = p_mrr_eur,
      stripe_subscription_id = p_stripe_subscription_id
  WHERE user_id = p_user_id;
  -- If user has no attribution row yet (signed up before this engine), create a 'direct' row
  IF NOT FOUND THEN
    INSERT INTO public.social_attribution (
      user_id, attribution_kind, signup_at, paid_at, mrr_eur, stripe_subscription_id
    ) VALUES (
      p_user_id, 'direct', NOW(), NOW(), p_mrr_eur, p_stripe_subscription_id
    );
  END IF;
END $$;

REVOKE ALL ON FUNCTION public.stamp_paid_attribution(uuid, numeric, text) FROM public, anon;
GRANT EXECUTE ON FUNCTION public.stamp_paid_attribution(uuid, numeric, text) TO service_role;

-- -----------------------------------------------------------------------------
-- 11. View — v_social_post_roi (powers analytics dashboard)
-- -----------------------------------------------------------------------------
CREATE OR REPLACE VIEW public.v_social_post_roi AS
SELECT
  sp.id                                            AS post_id,
  sp.post_slug,
  sp.language,
  sp.audience,
  sp.format,
  sp.campaign_tag,
  sp.published_at,
  -- Network metrics (last snapshot per target, summed across networks)
  COALESCE(SUM(sm_latest.impressions), 0)          AS impressions,
  COALESCE(SUM(sm_latest.reach), 0)                AS reach,
  COALESCE(SUM(sm_latest.likes + sm_latest.comments + sm_latest.shares + COALESCE(sm_latest.saves,0)), 0) AS engagement,
  COALESCE(SUM(sm_latest.click_through), 0)        AS clicks,
  -- Attribution
  (SELECT COUNT(DISTINCT sa.user_id) FROM public.social_attribution sa WHERE sa.post_slug = sp.post_slug) AS attributed_signups,
  (SELECT COUNT(DISTINCT sa.user_id) FROM public.social_attribution sa WHERE sa.post_slug = sp.post_slug AND sa.paid_at IS NOT NULL) AS attributed_paid,
  (SELECT COALESCE(SUM(sa.mrr_eur), 0)  FROM public.social_attribution sa WHERE sa.post_slug = sp.post_slug AND sa.paid_at IS NOT NULL) AS attributed_mrr_eur
FROM public.social_posts sp
LEFT JOIN LATERAL (
  SELECT m.target_id, m.impressions, m.reach, m.likes, m.comments, m.shares, m.saves, m.click_through
  FROM public.social_metrics m
  WHERE m.post_id = sp.id
  ORDER BY m.snapshot_date DESC
  LIMIT 1
) sm_latest ON true
WHERE sp.status IN ('published','partial')
GROUP BY sp.id, sp.post_slug, sp.language, sp.audience, sp.format, sp.campaign_tag, sp.published_at;

GRANT SELECT ON public.v_social_post_roi TO authenticated;

-- -----------------------------------------------------------------------------
-- 12. View — v_social_channel_roi
-- -----------------------------------------------------------------------------
CREATE OR REPLACE VIEW public.v_social_channel_roi AS
SELECT
  sa.source AS network,
  COUNT(DISTINCT sa.user_id)                                   AS signups_90d,
  COUNT(DISTINCT sa.user_id) FILTER (WHERE sa.paid_at IS NOT NULL) AS paid_90d,
  COALESCE(SUM(sa.mrr_eur), 0)                                  AS mrr_eur,
  ROUND(
    100.0 * COUNT(DISTINCT sa.user_id) FILTER (WHERE sa.paid_at IS NOT NULL)
        / NULLIF(COUNT(DISTINCT sa.user_id), 0),
    2
  )                                                             AS conversion_rate_pct
FROM public.social_attribution sa
WHERE sa.signup_at > NOW() - INTERVAL '90 days'
  AND sa.source IS NOT NULL
GROUP BY sa.source;

GRANT SELECT ON public.v_social_channel_roi TO authenticated;

-- -----------------------------------------------------------------------------
-- 13. Smoke test — verify migration applied cleanly
-- -----------------------------------------------------------------------------
DO $$
DECLARE
  v_table_count integer;
  v_rpc_count   integer;
BEGIN
  SELECT count(*) INTO v_table_count
  FROM information_schema.tables
  WHERE table_schema = 'public'
    AND table_name IN ('social_accounts','social_posts','social_post_targets','social_metrics','social_attribution');

  IF v_table_count <> 5 THEN
    RAISE EXCEPTION 'Mig 66 smoke test FAILED: expected 5 tables, found %', v_table_count;
  END IF;

  SELECT count(*) INTO v_rpc_count
  FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
  WHERE n.nspname = 'public'
    AND p.proname IN ('fetch_due_posts','mark_target_published','mark_target_failed','attribute_signup_to_post','stamp_paid_attribution');

  IF v_rpc_count <> 5 THEN
    RAISE EXCEPTION 'Mig 66 smoke test FAILED: expected 5 RPCs, found %', v_rpc_count;
  END IF;

  RAISE NOTICE 'Mig 66 smoke test PASSED — % tables, % RPCs', v_table_count, v_rpc_count;
END $$;

COMMIT;
