-- ===========================================
-- Supabase SSoT Schema for OTnest User Hub
-- Run this in: Supabase Dashboard → SQL Editor
-- ===========================================

-- Main users table (Single Source of Truth)
CREATE TABLE IF NOT EXISTS public.users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  base44_user_id TEXT UNIQUE,
  email TEXT UNIQUE NOT NULL,
  full_name TEXT,
  phone TEXT,
  locale TEXT DEFAULT 'he',
  role TEXT DEFAULT 'user',
  admin_roles JSONB DEFAULT '[]'::jsonb,
  is_community_approved BOOLEAN DEFAULT FALSE,
  has_completed_onboarding BOOLEAN DEFAULT FALSE,
  profile_data JSONB DEFAULT '{}'::jsonb,
  base44_created_at TIMESTAMPTZ,
  base44_updated_at TIMESTAMPTZ,
  last_synced_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_users_email ON public.users(email);
CREATE INDEX IF NOT EXISTS idx_users_base44_id ON public.users(base44_user_id);

-- Audit log
CREATE TABLE IF NOT EXISTS public.user_audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES public.users(id) ON DELETE CASCADE,
  action TEXT NOT NULL,
  changed_fields JSONB,
  source TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Consents (GDPR)
CREATE TABLE IF NOT EXISTS public.user_consents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES public.users(id) ON DELETE CASCADE,
  consent_type TEXT NOT NULL,
  granted BOOLEAN DEFAULT FALSE,
  granted_at TIMESTAMPTZ,
  revoked_at TIMESTAMPTZ
);

-- Auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ language 'plpgsql';

DROP TRIGGER IF EXISTS update_users_updated_at ON public.users;
CREATE TRIGGER update_users_updated_at
  BEFORE UPDATE ON public.users
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Enable RLS
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.user_audit_log ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.user_consents ENABLE ROW LEVEL SECURITY;

-- service_role full access (Base44 uses service_role key)
CREATE POLICY "Service role full access on users"
  ON public.users FOR ALL TO service_role USING (true) WITH CHECK (true);

CREATE POLICY "Service role full access on audit"
  ON public.user_audit_log FOR ALL TO service_role USING (true) WITH CHECK (true);

CREATE POLICY "Service role full access on consents"
  ON public.user_consents FOR ALL TO service_role USING (true) WITH CHECK (true);