-- Signup codes table
CREATE TABLE public.signup_codes (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  code text NOT NULL UNIQUE,
  role public.app_role NOT NULL DEFAULT 'homeowner',
  owner_id uuid REFERENCES public.owners(id) ON DELETE SET NULL,
  association_id uuid REFERENCES public.associations(id) ON DELETE SET NULL,
  note text,
  expires_at timestamptz,
  redeemed_at timestamptz,
  redeemed_by_user_id uuid,
  redeemed_email text,
  created_by uuid,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_signup_codes_code ON public.signup_codes(code);

ALTER TABLE public.signup_codes ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Staff can view signup codes"
  ON public.signup_codes FOR SELECT
  USING (
    public.has_role(auth.uid(), 'admin'::public.app_role)
    OR public.has_role(auth.uid(), 'manager'::public.app_role)
  );

CREATE POLICY "Staff can create signup codes"
  ON public.signup_codes FOR INSERT
  WITH CHECK (
    public.has_role(auth.uid(), 'admin'::public.app_role)
    OR public.has_role(auth.uid(), 'manager'::public.app_role)
  );

CREATE POLICY "Staff can update signup codes"
  ON public.signup_codes FOR UPDATE
  USING (
    public.has_role(auth.uid(), 'admin'::public.app_role)
    OR public.has_role(auth.uid(), 'manager'::public.app_role)
  );

CREATE POLICY "Staff can delete signup codes"
  ON public.signup_codes FOR DELETE
  USING (
    public.has_role(auth.uid(), 'admin'::public.app_role)
    OR public.has_role(auth.uid(), 'manager'::public.app_role)
  );

CREATE TRIGGER set_signup_codes_updated_at
BEFORE UPDATE ON public.signup_codes
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

-- Public lookup function (safe: returns minimal info needed to render the registration form)
CREATE OR REPLACE FUNCTION public.lookup_signup_code(p_code text)
RETURNS TABLE(
  id uuid,
  role public.app_role,
  owner_id uuid,
  association_id uuid,
  expires_at timestamptz,
  redeemed_at timestamptz,
  owner_first_name text,
  owner_last_name text,
  owner_email text,
  association_name text
)
LANGUAGE sql STABLE SECURITY DEFINER
SET search_path = public
AS $$
  SELECT
    sc.id, sc.role, sc.owner_id, sc.association_id, sc.expires_at, sc.redeemed_at,
    o.first_name, o.last_name, o.email,
    a.name
  FROM public.signup_codes sc
  LEFT JOIN public.owners o ON o.id = sc.owner_id
  LEFT JOIN public.associations a ON a.id = sc.association_id
  WHERE sc.code = p_code
  LIMIT 1;
$$;

GRANT EXECUTE ON FUNCTION public.lookup_signup_code(text) TO anon, authenticated;