Finish your workspace setup

Run this setup script once in your workspace — it prepares everything the app needs to store your work.

Open setup editor

Workspace services

A quick health check of the services powering your workspace — storage, sign-in, saved content and AI writing.

Checking your workspace…

Owner account

Creates (or resets) the owner login using the details configured for this workspace, and gives it full admin access.

Scheduled delivery

Scheduled sending runs automatically every few minutes. When a time slot arrives, all the posts planned for it are delivered together in one email.

-- Run this once in YOUR Supabase project (SQL editor) — the app reads the
-- project URL + publishable key from .env, so no Lovable Cloud is involved.

CREATE TABLE IF NOT EXISTS public.moments (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  title text NOT NULL DEFAULT 'Untitled moment',
  summary text,
  brief text NOT NULL DEFAULT '',
  platforms text[] NOT NULL DEFAULT '{}',
  doc_paths text[] NOT NULL DEFAULT '{}',
  doc_names text[] NOT NULL DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.moments TO authenticated;
GRANT ALL ON public.moments TO service_role;
ALTER TABLE public.moments ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users read own moments" ON public.moments;
CREATE POLICY "Users read own moments" ON public.moments FOR SELECT TO authenticated USING (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users create own moments" ON public.moments;
CREATE POLICY "Users create own moments" ON public.moments FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users update own moments" ON public.moments;
CREATE POLICY "Users update own moments" ON public.moments FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users delete own moments" ON public.moments;
CREATE POLICY "Users delete own moments" ON public.moments FOR DELETE TO authenticated USING (auth.uid() = user_id);

CREATE TABLE IF NOT EXISTS public.moment_posts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  moment_id uuid NOT NULL REFERENCES public.moments(id) ON DELETE CASCADE,
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  platform text NOT NULL,
  post text NOT NULL DEFAULT '',
  hashtags text[] NOT NULL DEFAULT '{}',
  image_spec text,
  image_prompt text,
  posted boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS moment_posts_moment_idx ON public.moment_posts(moment_id);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.moment_posts TO authenticated;
GRANT ALL ON public.moment_posts TO service_role;
ALTER TABLE public.moment_posts ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users read own posts" ON public.moment_posts;
CREATE POLICY "Users read own posts" ON public.moment_posts FOR SELECT TO authenticated USING (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users create own posts" ON public.moment_posts;
CREATE POLICY "Users create own posts" ON public.moment_posts FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users update own posts" ON public.moment_posts;
CREATE POLICY "Users update own posts" ON public.moment_posts FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users delete own posts" ON public.moment_posts;
CREATE POLICY "Users delete own posts" ON public.moment_posts FOR DELETE TO authenticated USING (auth.uid() = user_id);

CREATE TABLE IF NOT EXISTS public.user_settings (
  user_id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  provider text NOT NULL DEFAULT 'openai',
  model text,
  api_key text,
  brand_voice text,
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.user_settings TO authenticated;
GRANT ALL ON public.user_settings TO service_role;
ALTER TABLE public.user_settings ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users read own settings" ON public.user_settings;
CREATE POLICY "Users read own settings" ON public.user_settings FOR SELECT TO authenticated USING (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users write own settings" ON public.user_settings;
CREATE POLICY "Users write own settings" ON public.user_settings FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users update own settings" ON public.user_settings;
CREATE POLICY "Users update own settings" ON public.user_settings FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

CREATE OR REPLACE FUNCTION public.touch_updated_at()
RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END; $$ LANGUAGE plpgsql SET search_path = public;

DROP TRIGGER IF EXISTS moments_touch ON public.moments;
CREATE TRIGGER moments_touch BEFORE UPDATE ON public.moments FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();
DROP TRIGGER IF EXISTS moment_posts_touch ON public.moment_posts;
CREATE TRIGGER moment_posts_touch BEFORE UPDATE ON public.moment_posts FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

-- Private bucket for uploaded source documents (images / PDFs).
INSERT INTO storage.buckets (id, name, public) VALUES ('moment-docs', 'moment-docs', false)
ON CONFLICT (id) DO NOTHING;

DROP POLICY IF EXISTS "Users read own docs" ON storage.objects;
CREATE POLICY "Users read own docs" ON storage.objects FOR SELECT TO authenticated
  USING (bucket_id = 'moment-docs' AND (storage.foldername(name))[1] = auth.uid()::text);
DROP POLICY IF EXISTS "Users upload own docs" ON storage.objects;
CREATE POLICY "Users upload own docs" ON storage.objects FOR INSERT TO authenticated
  WITH CHECK (bucket_id = 'moment-docs' AND (storage.foldername(name))[1] = auth.uid()::text);
DROP POLICY IF EXISTS "Users delete own docs" ON storage.objects;
CREATE POLICY "Users delete own docs" ON storage.objects FOR DELETE TO authenticated
  USING (bucket_id = 'moment-docs' AND (storage.foldername(name))[1] = auth.uid()::text);

-- Run AFTER db/schema.sql in your Supabase SQL editor.
-- Adds saved content calendars, scheduled entries and WhatsApp delivery settings.

ALTER TABLE public.user_settings
  ADD COLUMN IF NOT EXISTS whatsapp_number text,
  ADD COLUMN IF NOT EXISTS whatsapp_enabled boolean NOT NULL DEFAULT false;

ALTER TABLE public.user_settings ALTER COLUMN provider SET DEFAULT 'lovable';

CREATE TABLE IF NOT EXISTS public.calendars (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  name text NOT NULL DEFAULT 'Content calendar',
  description text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.calendars TO authenticated;
GRANT ALL ON public.calendars TO service_role;
ALTER TABLE public.calendars ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users read own calendars" ON public.calendars;
CREATE POLICY "Users read own calendars" ON public.calendars FOR SELECT TO authenticated USING (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users create own calendars" ON public.calendars;
CREATE POLICY "Users create own calendars" ON public.calendars FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users update own calendars" ON public.calendars;
CREATE POLICY "Users update own calendars" ON public.calendars FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users delete own calendars" ON public.calendars;
CREATE POLICY "Users delete own calendars" ON public.calendars FOR DELETE TO authenticated USING (auth.uid() = user_id);

CREATE TABLE IF NOT EXISTS public.calendar_entries (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  calendar_id uuid NOT NULL REFERENCES public.calendars(id) ON DELETE CASCADE,
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  post_id uuid REFERENCES public.moment_posts(id) ON DELETE CASCADE,
  moment_id uuid REFERENCES public.moments(id) ON DELETE CASCADE,
  platform text NOT NULL DEFAULT 'instagram',
  scheduled_date date NOT NULL,
  scheduled_time text,
  note text,
  sent_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS calendar_entries_cal_idx ON public.calendar_entries(calendar_id, scheduled_date);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.calendar_entries TO authenticated;
GRANT ALL ON public.calendar_entries TO service_role;
ALTER TABLE public.calendar_entries ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users read own entries" ON public.calendar_entries;
CREATE POLICY "Users read own entries" ON public.calendar_entries FOR SELECT TO authenticated USING (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users create own entries" ON public.calendar_entries;
CREATE POLICY "Users create own entries" ON public.calendar_entries FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users update own entries" ON public.calendar_entries;
CREATE POLICY "Users update own entries" ON public.calendar_entries FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users delete own entries" ON public.calendar_entries;
CREATE POLICY "Users delete own entries" ON public.calendar_entries FOR DELETE TO authenticated USING (auth.uid() = user_id);

DROP TRIGGER IF EXISTS calendars_touch ON public.calendars;
CREATE TRIGGER calendars_touch BEFORE UPDATE ON public.calendars FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

-- ─────────────────────────────────────────────────────────────
-- Billing: roles, monthly plans, subscriptions, payments
-- ─────────────────────────────────────────────────────────────

DO $$ BEGIN
  CREATE TYPE public.app_role AS ENUM ('admin', 'user');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

CREATE TABLE IF NOT EXISTS public.user_roles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role public.app_role NOT NULL DEFAULT 'user',
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (user_id, role)
);
GRANT SELECT ON public.user_roles TO authenticated;
GRANT ALL ON public.user_roles TO service_role;
ALTER TABLE public.user_roles ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users read own roles" ON public.user_roles;
CREATE POLICY "Users read own roles" ON public.user_roles FOR SELECT TO authenticated USING (auth.uid() = user_id);

CREATE OR REPLACE FUNCTION public.has_role(_user_id uuid, _role public.app_role)
RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  SELECT EXISTS (SELECT 1 FROM public.user_roles WHERE user_id = _user_id AND role = _role)
$$;

CREATE TABLE IF NOT EXISTS public.plans (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  slug text NOT NULL UNIQUE,
  name text NOT NULL,
  tagline text,
  price_ngn numeric NOT NULL DEFAULT 0,
  price_usd numeric NOT NULL DEFAULT 0,
  billing_interval text NOT NULL DEFAULT 'month',
  moment_limit integer NOT NULL DEFAULT 10,
  features text[] NOT NULL DEFAULT '{}',
  sort_order integer NOT NULL DEFAULT 0,
  highlight boolean NOT NULL DEFAULT false,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.plans TO anon;
GRANT SELECT ON public.plans TO authenticated;
GRANT ALL ON public.plans TO service_role;
ALTER TABLE public.plans ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Anyone reads active plans" ON public.plans;
CREATE POLICY "Anyone reads active plans" ON public.plans FOR SELECT TO anon, authenticated USING (active);
DROP POLICY IF EXISTS "Admins read all plans" ON public.plans;
CREATE POLICY "Admins read all plans" ON public.plans FOR SELECT TO authenticated USING (public.has_role(auth.uid(), 'admin'));

DROP TRIGGER IF EXISTS plans_touch ON public.plans;
CREATE TRIGGER plans_touch BEFORE UPDATE ON public.plans FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

CREATE TABLE IF NOT EXISTS public.subscriptions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  plan_id uuid REFERENCES public.plans(id) ON DELETE SET NULL,
  status text NOT NULL DEFAULT 'inactive',
  currency text NOT NULL DEFAULT 'NGN',
  amount numeric NOT NULL DEFAULT 0,
  provider text,
  reference text,
  current_period_start timestamptz,
  current_period_end timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (user_id)
);
GRANT SELECT ON public.subscriptions TO authenticated;
GRANT ALL ON public.subscriptions TO service_role;
ALTER TABLE public.subscriptions ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users read own subscription" ON public.subscriptions;
CREATE POLICY "Users read own subscription" ON public.subscriptions FOR SELECT TO authenticated USING (auth.uid() = user_id);
DROP POLICY IF EXISTS "Admins read subscriptions" ON public.subscriptions;
CREATE POLICY "Admins read subscriptions" ON public.subscriptions FOR SELECT TO authenticated USING (public.has_role(auth.uid(), 'admin'));

DROP TRIGGER IF EXISTS subscriptions_touch ON public.subscriptions;
CREATE TRIGGER subscriptions_touch BEFORE UPDATE ON public.subscriptions FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

CREATE TABLE IF NOT EXISTS public.payments (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  plan_id uuid REFERENCES public.plans(id) ON DELETE SET NULL,
  provider text NOT NULL,
  reference text NOT NULL UNIQUE,
  currency text NOT NULL,
  amount numeric NOT NULL,
  status text NOT NULL DEFAULT 'pending',
  status_detail text,
  email text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS payments_user_idx ON public.payments(user_id);
GRANT SELECT ON public.payments TO authenticated;
GRANT ALL ON public.payments TO service_role;
ALTER TABLE public.payments ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users read own payments" ON public.payments;
CREATE POLICY "Users read own payments" ON public.payments FOR SELECT TO authenticated USING (auth.uid() = user_id);
DROP POLICY IF EXISTS "Admins read payments" ON public.payments;
CREATE POLICY "Admins read payments" ON public.payments FOR SELECT TO authenticated USING (public.has_role(auth.uid(), 'admin'));

DROP TRIGGER IF EXISTS payments_touch ON public.payments;
CREATE TRIGGER payments_touch BEFORE UPDATE ON public.payments FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

-- Email notification settings (replaces the old WhatsApp delivery fields)
ALTER TABLE public.user_settings
  ADD COLUMN IF NOT EXISTS notify_email text,
  ADD COLUMN IF NOT EXISTS email_enabled boolean NOT NULL DEFAULT true;

-- Seed the monthly plans (idempotent)
INSERT INTO public.plans (slug, name, tagline, price_ngn, price_usd, moment_limit, features, sort_order, highlight)
VALUES
  ('starter', 'Starter', 'For solo creators finding their rhythm.', 7500, 5, 15,
   ARRAY['15 AI moments / month','3 platforms per moment','Content calendar','Email delivery of daily posts'], 1, false),
  ('growth', 'Growth', 'For brands publishing every single day.', 19500, 13, 75,
   ARRAY['75 AI moments / month','All platforms','Content calendar + scheduling','Daily email digests','AI rewrite & image prompts'], 2, true),
  ('studio', 'Studio', 'For teams and agencies running many brands.', 49500, 33, 400,
   ARRAY['400 AI moments / month','Unlimited brand voices','Priority AI models','Daily email digests','Priority support'], 3, false)
ON CONFLICT (slug) DO NOTHING;

-- Make the project owner an admin
INSERT INTO public.user_roles (user_id, role)
SELECT id, 'admin' FROM auth.users WHERE email = 'omoniyioluwaseun22@gmail.com'
ON CONFLICT (user_id, role) DO NOTHING;

-- ─────────────────────────────────────────────────────────────
-- Folders, day scheduling worker, per-post images, moment chat
-- history and multi-email delivery (Growth plan and above)
-- ─────────────────────────────────────────────────────────────

-- Folders: save posts / moments into named folders
CREATE TABLE IF NOT EXISTS public.folders (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  name text NOT NULL DEFAULT 'New folder',
  description text,
  color text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.folders TO authenticated;
GRANT ALL ON public.folders TO service_role;
ALTER TABLE public.folders ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users read own folders" ON public.folders;
CREATE POLICY "Users read own folders" ON public.folders FOR SELECT TO authenticated USING (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users create own folders" ON public.folders;
CREATE POLICY "Users create own folders" ON public.folders FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users update own folders" ON public.folders;
CREATE POLICY "Users update own folders" ON public.folders FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users delete own folders" ON public.folders;
CREATE POLICY "Users delete own folders" ON public.folders FOR DELETE TO authenticated USING (auth.uid() = user_id);

DROP TRIGGER IF EXISTS folders_touch ON public.folders;
CREATE TRIGGER folders_touch BEFORE UPDATE ON public.folders FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

CREATE TABLE IF NOT EXISTS public.folder_items (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  folder_id uuid NOT NULL REFERENCES public.folders(id) ON DELETE CASCADE,
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  post_id uuid REFERENCES public.moment_posts(id) ON DELETE CASCADE,
  moment_id uuid REFERENCES public.moments(id) ON DELETE CASCADE,
  note text,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS folder_items_folder_idx ON public.folder_items(folder_id);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.folder_items TO authenticated;
GRANT ALL ON public.folder_items TO service_role;
ALTER TABLE public.folder_items ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users read own folder items" ON public.folder_items;
CREATE POLICY "Users read own folder items" ON public.folder_items FOR SELECT TO authenticated USING (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users create own folder items" ON public.folder_items;
CREATE POLICY "Users create own folder items" ON public.folder_items FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users update own folder items" ON public.folder_items;
CREATE POLICY "Users update own folder items" ON public.folder_items FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users delete own folder items" ON public.folder_items;
CREATE POLICY "Users delete own folder items" ON public.folder_items FOR DELETE TO authenticated USING (auth.uid() = user_id);

-- Per-post uploaded images (attach an image to a post anywhere)
ALTER TABLE public.moment_posts
  ADD COLUMN IF NOT EXISTS image_paths text[] NOT NULL DEFAULT '{}',
  ADD COLUMN IF NOT EXISTS image_names text[] NOT NULL DEFAULT '{}';

-- Scheduling: exact send time + worker delivery state
ALTER TABLE public.calendar_entries
  ADD COLUMN IF NOT EXISTS scheduled_at timestamptz,
  ADD COLUMN IF NOT EXISTS timezone_offset integer NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS status text NOT NULL DEFAULT 'pending',
  ADD COLUMN IF NOT EXISTS attempts integer NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS send_error text,
  ADD COLUMN IF NOT EXISTS folder_id uuid REFERENCES public.folders(id) ON DELETE SET NULL;

CREATE INDEX IF NOT EXISTS calendar_entries_due_idx
  ON public.calendar_entries(status, scheduled_at);

-- Chat history for a moment (whole-content updates) and per post
CREATE TABLE IF NOT EXISTS public.moment_chats (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  moment_id uuid NOT NULL REFERENCES public.moments(id) ON DELETE CASCADE,
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  post_id uuid REFERENCES public.moment_posts(id) ON DELETE CASCADE,
  scope text NOT NULL DEFAULT 'moment',
  role text NOT NULL DEFAULT 'user',
  content text NOT NULL DEFAULT '',
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS moment_chats_moment_idx ON public.moment_chats(moment_id, created_at);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.moment_chats TO authenticated;
GRANT ALL ON public.moment_chats TO service_role;
ALTER TABLE public.moment_chats ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users read own chats" ON public.moment_chats;
CREATE POLICY "Users read own chats" ON public.moment_chats FOR SELECT TO authenticated USING (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users create own chats" ON public.moment_chats;
CREATE POLICY "Users create own chats" ON public.moment_chats FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users delete own chats" ON public.moment_chats;
CREATE POLICY "Users delete own chats" ON public.moment_chats FOR DELETE TO authenticated USING (auth.uid() = user_id);

-- Multiple delivery emails (Growth plan and above)
CREATE TABLE IF NOT EXISTS public.user_emails (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  email text NOT NULL,
  label text,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (user_id, email)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.user_emails TO authenticated;
GRANT ALL ON public.user_emails TO service_role;
ALTER TABLE public.user_emails ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users read own emails" ON public.user_emails;
CREATE POLICY "Users read own emails" ON public.user_emails FOR SELECT TO authenticated USING (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users create own emails" ON public.user_emails;
CREATE POLICY "Users create own emails" ON public.user_emails FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users update own emails" ON public.user_emails;
CREATE POLICY "Users update own emails" ON public.user_emails FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users delete own emails" ON public.user_emails;
CREATE POLICY "Users delete own emails" ON public.user_emails FOR DELETE TO authenticated USING (auth.uid() = user_id);

-- Multi-recipient delivery belongs to Growth and above
UPDATE public.plans
   SET features = ARRAY['75 AI moments / month','All platforms','Content calendar + scheduling','Send to multiple email addresses','Daily scheduled delivery worker','AI rewrite & image prompts']
 WHERE slug = 'growth';
UPDATE public.plans
   SET features = ARRAY['15 AI moments / month','3 platforms per moment','Content calendar','Single delivery email address']
 WHERE slug = 'starter';
UPDATE public.plans
   SET features = ARRAY['400 AI moments / month','Unlimited brand voices','Priority AI models','Send to multiple email addresses','Priority support']
 WHERE slug = 'studio';

-- ─────────────────────────────────────────────────────────────
-- Admin-managed AI, credit system, yearly pricing and per-entry
-- notification recipients
-- ─────────────────────────────────────────────────────────────

-- Global AI + credit configuration, owned by the admin only.
-- No anon/authenticated grants: the key is read server-side (service role).
CREATE TABLE IF NOT EXISTS public.admin_settings (
  id text PRIMARY KEY DEFAULT 'global',
  ai_provider text NOT NULL DEFAULT 'lovable',
  ai_model text,
  ai_api_key text,
  credits_per_generation numeric NOT NULL DEFAULT 1,
  credits_per_edit numeric NOT NULL DEFAULT 1,
  free_credits integer NOT NULL DEFAULT 10,
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT ALL ON public.admin_settings TO service_role;
ALTER TABLE public.admin_settings ENABLE ROW LEVEL SECURITY;
INSERT INTO public.admin_settings (id) VALUES ('global') ON CONFLICT (id) DO NOTHING;

-- Fractional costs such as 0.5 are supported for generation and AI edits.
ALTER TABLE public.admin_settings
  ALTER COLUMN credits_per_generation TYPE numeric USING credits_per_generation::numeric,
  ALTER COLUMN credits_per_edit TYPE numeric USING credits_per_edit::numeric;

-- Remembered API keys per AI provider, so switching engines keeps credentials.
ALTER TABLE public.admin_settings
  ADD COLUMN IF NOT EXISTS ai_keys jsonb NOT NULL DEFAULT '{}'::jsonb;

-- Extra AI engines the admin adds by hand (label, address and model per engine).
ALTER TABLE public.admin_settings
  ADD COLUMN IF NOT EXISTS ai_custom_providers jsonb NOT NULL DEFAULT '{}'::jsonb;

DROP TRIGGER IF EXISTS admin_settings_touch ON public.admin_settings;
CREATE TRIGGER admin_settings_touch BEFORE UPDATE ON public.admin_settings
  FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

-- Yearly pricing + credit allowance, all set by the admin
ALTER TABLE public.plans
  ADD COLUMN IF NOT EXISTS price_yearly_ngn numeric NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS price_yearly_usd numeric NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS credit_limit integer NOT NULL DEFAULT 100;

ALTER TABLE public.subscriptions
  ADD COLUMN IF NOT EXISTS billing_interval text NOT NULL DEFAULT 'month';
ALTER TABLE public.payments
  ADD COLUMN IF NOT EXISTS billing_interval text NOT NULL DEFAULT 'month';

-- Every AI action spends credits; the ledger is the source of truth.
CREATE TABLE IF NOT EXISTS public.credit_ledger (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  amount numeric NOT NULL,
  reason text NOT NULL DEFAULT 'ai',
  created_at timestamptz NOT NULL DEFAULT now()
);
ALTER TABLE public.credit_ledger
  ALTER COLUMN amount TYPE numeric USING amount::numeric;
CREATE INDEX IF NOT EXISTS credit_ledger_user_idx ON public.credit_ledger(user_id, created_at);
GRANT SELECT ON public.credit_ledger TO authenticated;
GRANT ALL ON public.credit_ledger TO service_role;
ALTER TABLE public.credit_ledger ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users read own credit ledger" ON public.credit_ledger;
CREATE POLICY "Users read own credit ledger" ON public.credit_ledger FOR SELECT TO authenticated USING (auth.uid() = user_id);
DROP POLICY IF EXISTS "Admins read credit ledger" ON public.credit_ledger;
CREATE POLICY "Admins read credit ledger" ON public.credit_ledger FOR SELECT TO authenticated USING (public.has_role(auth.uid(), 'admin'));

-- Who gets notified about a scheduled batch (chosen per entry in the calendar)
ALTER TABLE public.calendar_entries
  ADD COLUMN IF NOT EXISTS recipients text[] NOT NULL DEFAULT '{}';

-- Default credit allowances + yearly prices (2 months free on yearly)
UPDATE public.plans SET credit_limit = 60,  price_yearly_ngn = price_ngn * 10, price_yearly_usd = price_usd * 10 WHERE slug = 'starter'  AND price_yearly_ngn = 0;
UPDATE public.plans SET credit_limit = 300, price_yearly_ngn = price_ngn * 10, price_yearly_usd = price_usd * 10 WHERE slug = 'growth'   AND price_yearly_ngn = 0;
UPDATE public.plans SET credit_limit = 1600, price_yearly_ngn = price_ngn * 10, price_yearly_usd = price_usd * 10 WHERE slug = 'studio'  AND price_yearly_ngn = 0;

-- ─────────────────────────────────────────────────────────────
-- Admin credit grants: extra credits given to a specific user,
-- optionally with an expiry date.
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS public.credit_grants (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  amount integer NOT NULL DEFAULT 0,
  note text,
  expires_at timestamptz,
  revoked boolean NOT NULL DEFAULT false,
  created_by uuid REFERENCES auth.users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS credit_grants_user_idx ON public.credit_grants(user_id, expires_at);
GRANT SELECT ON public.credit_grants TO authenticated;
GRANT ALL ON public.credit_grants TO service_role;
ALTER TABLE public.credit_grants ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users read own credit grants" ON public.credit_grants;
CREATE POLICY "Users read own credit grants" ON public.credit_grants FOR SELECT TO authenticated USING (auth.uid() = user_id);
DROP POLICY IF EXISTS "Admins read credit grants" ON public.credit_grants;
CREATE POLICY "Admins read credit grants" ON public.credit_grants FOR SELECT TO authenticated USING (public.has_role(auth.uid(), 'admin'));

-- ============================================================
-- Brand switcher + customer email journey (admin controlled)
-- ============================================================

-- Which logo the whole app (and every email) uses: 'moments' or 'vendorgap'.
ALTER TABLE public.admin_settings
  ADD COLUMN IF NOT EXISTS brand_variant text NOT NULL DEFAULT 'moments';

-- Post editor switches, flipped by the admin.
ALTER TABLE public.admin_settings
  ADD COLUMN IF NOT EXISTS post_images_enabled boolean NOT NULL DEFAULT true,
  ADD COLUMN IF NOT EXISTS post_chat_attachments_enabled boolean NOT NULL DEFAULT true;

-- Several posts per platform in one generation (admin switchable).
ALTER TABLE public.admin_settings
  ADD COLUMN IF NOT EXISTS multi_posts_enabled boolean NOT NULL DEFAULT true;

-- One row per stage of the customer journey; active = the email is sent.
CREATE TABLE IF NOT EXISTS public.email_settings (
  key text PRIMARY KEY,
  active boolean NOT NULL DEFAULT true,
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT ALL ON public.email_settings TO service_role;
ALTER TABLE public.email_settings ENABLE ROW LEVEL SECURITY;

INSERT INTO public.email_settings (key) VALUES
  ('welcome'),
  ('moment_ready'),
  ('schedule_confirmed'),
  ('day_digest'),
  ('scheduled_delivery'),
  ('payment_receipt'),
  ('admin_payment_alert'),
  ('credits_low'),
  ('credits_exhausted'),
  ('contact_message')
ON CONFLICT (key) DO NOTHING;

-- Public contact details, editable (and hideable) from the admin console.
ALTER TABLE public.admin_settings
  ADD COLUMN IF NOT EXISTS contact_email text,
  ADD COLUMN IF NOT EXISTS contact_location text,
  ADD COLUMN IF NOT EXISTS contact_email_visible boolean NOT NULL DEFAULT true,
  ADD COLUMN IF NOT EXISTS contact_location_visible boolean NOT NULL DEFAULT true;

-- Delivery log, also used to avoid sending the same nudge twice.
CREATE TABLE IF NOT EXISTS public.email_log (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id) ON DELETE SET NULL,
  email_key text NOT NULL,
  to_emails text[] NOT NULL DEFAULT '{}',
  dedupe_key text UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS email_log_key_idx ON public.email_log(email_key, created_at DESC);
GRANT ALL ON public.email_log TO service_role;
ALTER TABLE public.email_log ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Admins read email log" ON public.email_log;
CREATE POLICY "Admins read email log" ON public.email_log FOR SELECT TO authenticated USING (public.has_role(auth.uid(), 'admin'));

-- ============================================================
-- Products & Services catalogue + post kind (moment vs product)
-- ============================================================

-- Every moment now records what it is: an idea/moment post, or a
-- promotional post for a saved product / service.
ALTER TABLE public.moments
  ADD COLUMN IF NOT EXISTS kind text NOT NULL DEFAULT 'moment';
CREATE INDEX IF NOT EXISTS moments_kind_idx ON public.moments(user_id, kind);

CREATE TABLE IF NOT EXISTS public.products (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  kind text NOT NULL DEFAULT 'product',           -- 'product' | 'service'
  name text NOT NULL DEFAULT 'Untitled',
  tagline text,
  description text,
  price text,
  audience text,
  cta text,
  features text[] NOT NULL DEFAULT '{}',
  image_paths text[] NOT NULL DEFAULT '{}',
  image_names text[] NOT NULL DEFAULT '{}',
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS products_user_idx ON public.products(user_id, created_at DESC);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.products TO authenticated;
GRANT ALL ON public.products TO service_role;
ALTER TABLE public.products ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users read own products" ON public.products;
CREATE POLICY "Users read own products" ON public.products FOR SELECT TO authenticated USING (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users create own products" ON public.products;
CREATE POLICY "Users create own products" ON public.products FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users update own products" ON public.products;
CREATE POLICY "Users update own products" ON public.products FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users delete own products" ON public.products;
CREATE POLICY "Users delete own products" ON public.products FOR DELETE TO authenticated USING (auth.uid() = user_id);

DROP TRIGGER IF EXISTS products_touch ON public.products;
CREATE TRIGGER products_touch BEFORE UPDATE ON public.products FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

-- A moment can be generated straight from a saved product / service.
ALTER TABLE public.moments
  ADD COLUMN IF NOT EXISTS product_id uuid REFERENCES public.products(id) ON DELETE SET NULL;

-- ---------------------------------------------------------------------------
-- Admin-managed branding: custom logo (upload / letter / icon) + home hero.
-- ---------------------------------------------------------------------------
ALTER TABLE public.admin_settings
  ADD COLUMN IF NOT EXISTS logo_mode text NOT NULL DEFAULT 'preset',
  ADD COLUMN IF NOT EXISTS logo_url text,
  ADD COLUMN IF NOT EXISTS logo_letter text,
  ADD COLUMN IF NOT EXISTS logo_icon text,
  ADD COLUMN IF NOT EXISTS brand_name text,
  ADD COLUMN IF NOT EXISTS hero_image_url text;

-- Public bucket for branding images (logo + landing hero).
INSERT INTO storage.buckets (id, name, public) VALUES ('brand-assets', 'brand-assets', true)
ON CONFLICT (id) DO UPDATE SET public = true;

DROP POLICY IF EXISTS "Anyone reads brand assets" ON storage.objects;
CREATE POLICY "Anyone reads brand assets" ON storage.objects FOR SELECT TO public
  USING (bucket_id = 'brand-assets');
DROP POLICY IF EXISTS "Admins upload brand assets" ON storage.objects;
CREATE POLICY "Admins upload brand assets" ON storage.objects FOR INSERT TO authenticated
  WITH CHECK (bucket_id = 'brand-assets' AND public.has_role(auth.uid(), 'admin'));
DROP POLICY IF EXISTS "Admins update brand assets" ON storage.objects;
CREATE POLICY "Admins update brand assets" ON storage.objects FOR UPDATE TO authenticated
  USING (bucket_id = 'brand-assets' AND public.has_role(auth.uid(), 'admin'));
DROP POLICY IF EXISTS "Admins delete brand assets" ON storage.objects;
CREATE POLICY "Admins delete brand assets" ON storage.objects FOR DELETE TO authenticated
  USING (bucket_id = 'brand-assets' AND public.has_role(auth.uid(), 'admin'));


-- ---------------------------------------------------------------------------
-- Hero carousel: multiple images plus the slide transition interval.
-- ---------------------------------------------------------------------------
ALTER TABLE public.admin_settings
  ADD COLUMN IF NOT EXISTS hero_images text[] NOT NULL DEFAULT '{}',
  ADD COLUMN IF NOT EXISTS hero_interval_ms integer NOT NULL DEFAULT 5000;


-- ---------------------------------------------------------------------------
-- Journal: a day-by-day record of moments worth turning into content.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.journal_entries (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  entry_date date NOT NULL DEFAULT (now() AT TIME ZONE 'utc')::date,
  title text NOT NULL DEFAULT 'Untitled entry',
  content text NOT NULL DEFAULT '',
  mood text,
  tags text[] NOT NULL DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS journal_entries_user_date_idx
  ON public.journal_entries(user_id, entry_date DESC);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.journal_entries TO authenticated;
GRANT ALL ON public.journal_entries TO service_role;
ALTER TABLE public.journal_entries ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users read own journal" ON public.journal_entries;
CREATE POLICY "Users read own journal" ON public.journal_entries FOR SELECT TO authenticated USING (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users create own journal" ON public.journal_entries;
CREATE POLICY "Users create own journal" ON public.journal_entries FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users update own journal" ON public.journal_entries;
CREATE POLICY "Users update own journal" ON public.journal_entries FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users delete own journal" ON public.journal_entries;
CREATE POLICY "Users delete own journal" ON public.journal_entries FOR DELETE TO authenticated USING (auth.uid() = user_id);
DROP TRIGGER IF EXISTS journal_entries_touch ON public.journal_entries;
CREATE TRIGGER journal_entries_touch BEFORE UPDATE ON public.journal_entries FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();


-- ---------------------------------------------------------------------------
-- Multiple brands (workspaces) + testimonial collection
-- Run this in the Supabase SQL editor AFTER db/setup.sql. Safe to re-run.
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS public.workspaces (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  name text NOT NULL DEFAULT 'My brand',
  brand_voice text,
  logo_url text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS workspaces_user_idx ON public.workspaces(user_id, created_at);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.workspaces TO authenticated;
GRANT ALL ON public.workspaces TO service_role;
ALTER TABLE public.workspaces ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users read own workspaces" ON public.workspaces;
CREATE POLICY "Users read own workspaces" ON public.workspaces FOR SELECT TO authenticated USING (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users create own workspaces" ON public.workspaces;
CREATE POLICY "Users create own workspaces" ON public.workspaces FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users update own workspaces" ON public.workspaces;
CREATE POLICY "Users update own workspaces" ON public.workspaces FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users delete own workspaces" ON public.workspaces;
CREATE POLICY "Users delete own workspaces" ON public.workspaces FOR DELETE TO authenticated USING (auth.uid() = user_id);
DROP TRIGGER IF EXISTS workspaces_touch ON public.workspaces;
CREATE TRIGGER workspaces_touch BEFORE UPDATE ON public.workspaces FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

-- How many brands a plan allows. Users with no active plan get one.
ALTER TABLE public.plans
  ADD COLUMN IF NOT EXISTS max_workspaces integer NOT NULL DEFAULT 1;
UPDATE public.plans SET max_workspaces = 3 WHERE slug = 'growth' AND max_workspaces < 3;
UPDATE public.plans SET max_workspaces = 10 WHERE slug = 'studio' AND max_workspaces < 10;

-- The brand a user is currently working in.
ALTER TABLE public.user_settings
  ADD COLUMN IF NOT EXISTS active_workspace_id uuid REFERENCES public.workspaces(id) ON DELETE SET NULL;

-- Per-brand ownership of the creative work.
ALTER TABLE public.moments         ADD COLUMN IF NOT EXISTS workspace_id uuid REFERENCES public.workspaces(id) ON DELETE CASCADE;
ALTER TABLE public.products        ADD COLUMN IF NOT EXISTS workspace_id uuid REFERENCES public.workspaces(id) ON DELETE CASCADE;
ALTER TABLE public.calendars       ADD COLUMN IF NOT EXISTS workspace_id uuid REFERENCES public.workspaces(id) ON DELETE CASCADE;
ALTER TABLE public.folders         ADD COLUMN IF NOT EXISTS workspace_id uuid REFERENCES public.workspaces(id) ON DELETE CASCADE;
ALTER TABLE public.journal_entries ADD COLUMN IF NOT EXISTS workspace_id uuid REFERENCES public.workspaces(id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS moments_ws_idx   ON public.moments(workspace_id, created_at DESC);
CREATE INDEX IF NOT EXISTS products_ws_idx  ON public.products(workspace_id, created_at DESC);
CREATE INDEX IF NOT EXISTS calendars_ws_idx ON public.calendars(workspace_id);
CREATE INDEX IF NOT EXISTS folders_ws_idx   ON public.folders(workspace_id);
CREATE INDEX IF NOT EXISTS journal_ws_idx   ON public.journal_entries(workspace_id, entry_date DESC);

-- Give every existing account a first brand and move their work into it.
INSERT INTO public.workspaces (user_id, name, brand_voice)
SELECT u.id, 'My brand', s.brand_voice
FROM auth.users u
LEFT JOIN public.user_settings s ON s.user_id = u.id
WHERE NOT EXISTS (SELECT 1 FROM public.workspaces w WHERE w.user_id = u.id);

WITH first_ws AS (
  SELECT DISTINCT ON (user_id) user_id, id
  FROM public.workspaces ORDER BY user_id, created_at
)
UPDATE public.user_settings s SET active_workspace_id = f.id
FROM first_ws f WHERE f.user_id = s.user_id AND s.active_workspace_id IS NULL;

DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY['moments','products','calendars','folders','journal_entries'] LOOP
    EXECUTE format(
      'WITH first_ws AS (SELECT DISTINCT ON (user_id) user_id, id FROM public.workspaces ORDER BY user_id, created_at)
       UPDATE public.%I t SET workspace_id = f.id FROM first_ws f
       WHERE f.user_id = t.user_id AND t.workspace_id IS NULL', t);
  END LOOP;
END $$;

-- ---------------------------------------------------------------------------
-- Testimonials: shareable collection links + submissions
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS public.testimonial_forms (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  workspace_id uuid REFERENCES public.workspaces(id) ON DELETE CASCADE,
  product_id uuid REFERENCES public.products(id) ON DELETE SET NULL,
  slug text NOT NULL UNIQUE,
  title text NOT NULL DEFAULT 'Share your experience',
  intro text,
  thanks_title text NOT NULL DEFAULT 'Thank you!',
  thanks_message text,
  fields jsonb NOT NULL DEFAULT '[]'::jsonb,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS testimonial_forms_ws_idx ON public.testimonial_forms(workspace_id, created_at DESC);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.testimonial_forms TO authenticated;
GRANT ALL ON public.testimonial_forms TO service_role;
ALTER TABLE public.testimonial_forms ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users read own testimonial forms" ON public.testimonial_forms;
CREATE POLICY "Users read own testimonial forms" ON public.testimonial_forms FOR SELECT TO authenticated USING (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users create own testimonial forms" ON public.testimonial_forms;
CREATE POLICY "Users create own testimonial forms" ON public.testimonial_forms FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users update own testimonial forms" ON public.testimonial_forms;
CREATE POLICY "Users update own testimonial forms" ON public.testimonial_forms FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users delete own testimonial forms" ON public.testimonial_forms;
CREATE POLICY "Users delete own testimonial forms" ON public.testimonial_forms FOR DELETE TO authenticated USING (auth.uid() = user_id);
DROP TRIGGER IF EXISTS testimonial_forms_touch ON public.testimonial_forms;
CREATE TRIGGER testimonial_forms_touch BEFORE UPDATE ON public.testimonial_forms FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

CREATE TABLE IF NOT EXISTS public.testimonials (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  form_id uuid NOT NULL REFERENCES public.testimonial_forms(id) ON DELETE CASCADE,
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  workspace_id uuid REFERENCES public.workspaces(id) ON DELETE CASCADE,
  product_id uuid REFERENCES public.products(id) ON DELETE SET NULL,
  customer_name text,
  role_company text,
  email text,
  rating integer,
  quote text NOT NULL DEFAULT '',
  answers jsonb NOT NULL DEFAULT '{}'::jsonb,
  photo_path text,
  video_path text,
  status text NOT NULL DEFAULT 'received',
  used_in_moment_id uuid REFERENCES public.moments(id) ON DELETE SET NULL,
  ip_hash text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT testimonials_status_check CHECK (status IN ('received', 'revisit', 'approved')),
  CONSTRAINT testimonials_rating_check CHECK (rating IS NULL OR (rating >= 1 AND rating <= 5))
);
CREATE INDEX IF NOT EXISTS testimonials_ws_idx ON public.testimonials(workspace_id, status, created_at DESC);
CREATE INDEX IF NOT EXISTS testimonials_rate_idx ON public.testimonials(form_id, ip_hash, created_at DESC);
GRANT SELECT, UPDATE, DELETE ON public.testimonials TO authenticated;
GRANT ALL ON public.testimonials TO service_role;
ALTER TABLE public.testimonials ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users read own testimonials" ON public.testimonials;
CREATE POLICY "Users read own testimonials" ON public.testimonials FOR SELECT TO authenticated USING (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users update own testimonials" ON public.testimonials;
CREATE POLICY "Users update own testimonials" ON public.testimonials FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
DROP POLICY IF EXISTS "Users delete own testimonials" ON public.testimonials;
CREATE POLICY "Users delete own testimonials" ON public.testimonials FOR DELETE TO authenticated USING (auth.uid() = user_id);
DROP TRIGGER IF EXISTS testimonials_touch ON public.testimonials;
CREATE TRIGGER testimonials_touch BEFORE UPDATE ON public.testimonials FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

-- A moment can be generated from a collected testimonial.
ALTER TABLE public.moments
  ADD COLUMN IF NOT EXISTS testimonial_id uuid REFERENCES public.testimonials(id) ON DELETE SET NULL;

-- Private bucket for customer photo / video uploads. Nothing is publicly
-- readable: the app serves short-lived signed links to the brand owner only.
INSERT INTO storage.buckets (id, name, public, file_size_limit)
VALUES ('testimonial-media', 'testimonial-media', false, 52428800)
ON CONFLICT (id) DO UPDATE SET public = false, file_size_limit = 52428800;

DROP POLICY IF EXISTS "Owners read testimonial media" ON storage.objects;
CREATE POLICY "Owners read testimonial media" ON storage.objects FOR SELECT TO authenticated
  USING (bucket_id = 'testimonial-media' AND (storage.foldername(name))[1] = auth.uid()::text);
DROP POLICY IF EXISTS "Owners delete testimonial media" ON storage.objects;
CREATE POLICY "Owners delete testimonial media" ON storage.objects FOR DELETE TO authenticated
  USING (bucket_id = 'testimonial-media' AND (storage.foldername(name))[1] = auth.uid()::text);