-- Add is_client column to profiles
ALTER TABLE public.profiles
  ADD COLUMN is_client boolean NOT NULL DEFAULT false;

-- Create client_invites table
CREATE TABLE public.client_invites (
  id serial PRIMARY KEY,
  tablo_id text NOT NULL REFERENCES public.tablos(id) ON DELETE CASCADE,
  invited_email varchar(255) NOT NULL,
  invited_by uuid NOT NULL REFERENCES public.profiles(id),
  invite_token text NOT NULL,
  expires_at timestamptz NOT NULL DEFAULT (now() + interval '30 days'),
  is_pending boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Index for token lookups
CREATE UNIQUE INDEX idx_client_invites_token ON public.client_invites(invite_token);

-- Index for listing invites by tablo
CREATE INDEX idx_client_invites_tablo ON public.client_invites(tablo_id, is_pending);

-- RLS
ALTER TABLE public.client_invites ENABLE ROW LEVEL SECURITY;

-- Admins can manage invites they created
CREATE POLICY "Admins can manage their client invites"
  ON public.client_invites
  FOR ALL
  USING (invited_by = auth.uid());

-- Client users can read invites sent to their email
CREATE POLICY "Clients can read their own invites"
  ON public.client_invites
  FOR SELECT
  USING (
    invited_email = (
      SELECT email FROM auth.users WHERE id = auth.uid()
    )
  );
