acmcc/supabase/migrations/20260522181809_6a328913-da2e-4818-8dc1-c68e1b4ab942.sql

-- Tenant info request flow (mirrors vendor_profile_requests pattern)

CREATE TABLE public.tenant_info_requests (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id uuid NOT NULL REFERENCES public.owners(id) ON DELETE CASCADE,
  unit_id uuid REFERENCES public.units(id) ON DELETE SET NULL,
  association_id uuid NOT NULL,
  token text NOT NULL UNIQUE DEFAULT encode(extensions.gen_random_bytes(24), 'hex'),
  sent_to_email text,
  custom_message text,
  sent_at timestamptz NOT NULL DEFAULT now(),
  submitted_at timestamptz,
  expires_at timestamptz NOT NULL DEFAULT (now() + interval '30 days'),
  created_by uuid,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_tenant_info_requests_token ON public.tenant_info_requests(token);
CREATE INDEX idx_tenant_info_requests_owner ON public.tenant_info_requests(owner_id);
CREATE INDEX idx_tenant_info_requests_unit ON public.tenant_info_requests(unit_id);

ALTER TABLE public.tenant_info_requests ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Staff manage tenant info requests" ON public.tenant_info_requests
  TO authenticated
  USING (has_role(auth.uid(),'admin'::app_role) OR has_role(auth.uid(),'manager'::app_role))
  WITH CHECK (has_role(auth.uid(),'admin'::app_role) OR has_role(auth.uid(),'manager'::app_role));

-- Submission rows store full tenant data per request
CREATE TABLE public.tenant_info_submissions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  request_id uuid NOT NULL REFERENCES public.tenant_info_requests(id) ON DELETE CASCADE,
  owner_id uuid NOT NULL,
  unit_id uuid,
  association_id uuid NOT NULL,
  -- Basic
  tenant_first_name text NOT NULL,
  tenant_last_name text NOT NULL,
  occupants jsonb DEFAULT '[]'::jsonb,
  -- Contact
  email text,
  phone text,
  emergency_contact_name text,
  emergency_contact_phone text,
  -- Lease
  lease_start date,
  lease_end date,
  lease_length_months integer,
  lease_document_url text,
  -- Vehicles & pets (free-form arrays)
  vehicles jsonb DEFAULT '[]'::jsonb,
  pets jsonb DEFAULT '[]'::jsonb,
  notes text,
  submitted_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_tenant_info_submissions_owner ON public.tenant_info_submissions(owner_id);
CREATE INDEX idx_tenant_info_submissions_unit ON public.tenant_info_submissions(unit_id);

ALTER TABLE public.tenant_info_submissions ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Staff manage tenant info submissions" ON public.tenant_info_submissions
  TO authenticated
  USING (has_role(auth.uid(),'admin'::app_role) OR has_role(auth.uid(),'manager'::app_role))
  WITH CHECK (has_role(auth.uid(),'admin'::app_role) OR has_role(auth.uid(),'manager'::app_role));

-- Lookup RPC (public, security definer — token is the secret)
CREATE OR REPLACE FUNCTION public.lookup_tenant_info_request(p_token text)
RETURNS TABLE (
  request_id uuid,
  owner_id uuid,
  unit_id uuid,
  association_id uuid,
  owner_name text,
  property_address text,
  association_name text,
  custom_message text,
  expires_at timestamptz,
  submitted_at timestamptz
)
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  SELECT r.id, r.owner_id, r.unit_id, r.association_id,
         trim(coalesce(o.first_name,'') || ' ' || coalesce(o.last_name,'')) AS owner_name,
         coalesce(u.address, o.property_address) AS property_address,
         a.name AS association_name,
         r.custom_message,
         r.expires_at, r.submitted_at
  FROM public.tenant_info_requests r
  JOIN public.owners o ON o.id = r.owner_id
  LEFT JOIN public.units u ON u.id = r.unit_id
  LEFT JOIN public.associations a ON a.id = r.association_id
  WHERE r.token = p_token
  LIMIT 1;
$$;
GRANT EXECUTE ON FUNCTION public.lookup_tenant_info_request(text) TO anon, authenticated;

-- Submit RPC
CREATE OR REPLACE FUNCTION public.submit_tenant_info(
  p_token text,
  p_tenant_first_name text,
  p_tenant_last_name text,
  p_email text DEFAULT NULL,
  p_phone text DEFAULT NULL,
  p_emergency_contact_name text DEFAULT NULL,
  p_emergency_contact_phone text DEFAULT NULL,
  p_lease_start date DEFAULT NULL,
  p_lease_end date DEFAULT NULL,
  p_lease_length_months integer DEFAULT NULL,
  p_lease_document_url text DEFAULT NULL,
  p_vehicles jsonb DEFAULT '[]'::jsonb,
  p_pets jsonb DEFAULT '[]'::jsonb,
  p_occupants jsonb DEFAULT '[]'::jsonb,
  p_notes text DEFAULT NULL
)
RETURNS boolean
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  r record;
BEGIN
  SELECT * INTO r FROM public.tenant_info_requests WHERE token = p_token;
  IF r IS NULL THEN RETURN false; END IF;
  IF r.submitted_at IS NOT NULL THEN RETURN false; END IF;
  IF r.expires_at < now() THEN RETURN false; END IF;

  INSERT INTO public.tenant_info_submissions (
    request_id, owner_id, unit_id, association_id,
    tenant_first_name, tenant_last_name, occupants,
    email, phone, emergency_contact_name, emergency_contact_phone,
    lease_start, lease_end, lease_length_months, lease_document_url,
    vehicles, pets, notes
  ) VALUES (
    r.id, r.owner_id, r.unit_id, r.association_id,
    p_tenant_first_name, p_tenant_last_name, coalesce(p_occupants,'[]'::jsonb),
    p_email, p_phone, p_emergency_contact_name, p_emergency_contact_phone,
    p_lease_start, p_lease_end, p_lease_length_months, p_lease_document_url,
    coalesce(p_vehicles,'[]'::jsonb), coalesce(p_pets,'[]'::jsonb), p_notes
  );

  -- Upsert a basic record into unit_tenants for roster visibility
  IF r.unit_id IS NOT NULL THEN
    INSERT INTO public.unit_tenants (unit_id, name, email, phone, lease_start, lease_end, status)
    VALUES (
      r.unit_id,
      trim(p_tenant_first_name || ' ' || p_tenant_last_name),
      p_email, p_phone, p_lease_start, p_lease_end, 'active'
    );
  END IF;

  UPDATE public.tenant_info_requests SET submitted_at = now() WHERE id = r.id;
  RETURN true;
END;
$$;
GRANT EXECUTE ON FUNCTION public.submit_tenant_info(
  text,text,text,text,text,text,text,date,date,integer,text,jsonb,jsonb,jsonb,text
) TO anon, authenticated;