CREATE TABLE IF NOT EXISTS agreements (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  client_id UUID REFERENCES clients(id) ON DELETE CASCADE,
  type TEXT NOT NULL CHECK (type IN ('master', 'loe')),
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','sent','accepted','expired')),
  ref_number TEXT,
  agreement_data JSONB NOT NULL DEFAULT '{}',
  acceptance_token TEXT UNIQUE DEFAULT gen_random_uuid()::text,
  accepted_by_name TEXT,
  accepted_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  sent_at TIMESTAMPTZ
);
ALTER TABLE agreements ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Admin full access" ON agreements FOR ALL
  USING (EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND role IN ('admin','super_admin')));
CREATE POLICY "Anon read by token" ON agreements FOR SELECT TO anon USING (true);
CREATE POLICY "Anon accept" ON agreements FOR UPDATE TO anon
  USING (status = 'sent') WITH CHECK (status = 'accepted');

CREATE TABLE IF NOT EXISTS cpd_resources (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  url TEXT,
  category TEXT CHECK (category IN ('sfrs','tax','corpsec','cpf','ethics')),
  source TEXT,
  published_date DATE DEFAULT CURRENT_DATE,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
ALTER TABLE cpd_resources ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Admin full access" ON cpd_resources FOR ALL
  USING (EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND role IN ('admin','super_admin')));
CREATE POLICY "Professionals read active" ON cpd_resources FOR SELECT
  USING (is_active = true);