-- Admin centralized: businesses, user_business, account_authorizations
-- Run this in Supabase SQL Editor or via Supabase CLI.

-- Businesses
CREATE TABLE IF NOT EXISTS public.businesses (
  business_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- User <-> Business (many-to-many)
CREATE TABLE IF NOT EXISTS public.user_business (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
  business_id INTEGER NOT NULL REFERENCES public.businesses(business_id) ON DELETE CASCADE,
  role TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (user_id, business_id)
);

-- Authorization checklist: who is authorized for which business
CREATE TABLE IF NOT EXISTS public.account_authorizations (
  id SERIAL PRIMARY KEY,
  business_id INTEGER NOT NULL REFERENCES public.businesses(business_id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
  authorized_by_admin_id INTEGER NOT NULL REFERENCES public.users(user_id) ON DELETE CASCADE,
  account_identifier TEXT,
  notes TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (business_id, user_id)
);

-- Indexes for filters
CREATE INDEX IF NOT EXISTS idx_user_business_business_id ON public.user_business(business_id);
CREATE INDEX IF NOT EXISTS idx_user_business_user_id ON public.user_business(user_id);
CREATE INDEX IF NOT EXISTS idx_account_authorizations_business_id ON public.account_authorizations(business_id);
CREATE INDEX IF NOT EXISTS idx_account_authorizations_user_id ON public.account_authorizations(user_id);

-- RLS: restrict to service role only (API uses requireAdmin + supabaseAdmin)
ALTER TABLE public.businesses ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.user_business ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.account_authorizations ENABLE ROW LEVEL SECURITY;

-- No policies: anon/authenticated cannot access; service role bypasses RLS
-- So only server-side API with SUPABASE_SERVICE_ROLE_KEY can read/write.
