mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 09:50:01 +00:00
51 lines
1.5 KiB
PL/PgSQL
51 lines
1.5 KiB
PL/PgSQL
|
|
-- Add new columns to legal_matters to match the reference design
|
|
ALTER TABLE public.legal_matters
|
|
ADD COLUMN IF NOT EXISTS case_type TEXT DEFAULT NULL,
|
|
ADD COLUMN IF NOT EXISTS official_case_name TEXT DEFAULT NULL,
|
|
ADD COLUMN IF NOT EXISTS current_stage TEXT DEFAULT 'intent_to_lien',
|
|
ADD COLUMN IF NOT EXISTS judge TEXT DEFAULT NULL,
|
|
ADD COLUMN IF NOT EXISTS amount_due NUMERIC DEFAULT 0,
|
|
ADD COLUMN IF NOT EXISTS opposing_counsel TEXT DEFAULT NULL,
|
|
ADD COLUMN IF NOT EXISTS unit_id UUID REFERENCES public.units(id) DEFAULT NULL,
|
|
ADD COLUMN IF NOT EXISTS collection_id UUID REFERENCES public.collections(id) DEFAULT NULL;
|
|
|
|
-- Create a DB function that auto-creates a legal matter when a collection is inserted
|
|
CREATE OR REPLACE FUNCTION public.auto_create_legal_matter_from_collection()
|
|
RETURNS TRIGGER
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
BEGIN
|
|
INSERT INTO public.legal_matters (
|
|
association_id,
|
|
title,
|
|
case_type,
|
|
category,
|
|
current_stage,
|
|
amount_due,
|
|
collection_id,
|
|
status,
|
|
description
|
|
) VALUES (
|
|
NEW.association_id,
|
|
'Collection Case - ' || COALESCE(NEW.notes, 'No description'),
|
|
'collection',
|
|
'collections',
|
|
'intent_to_lien',
|
|
NEW.amount_owed,
|
|
NEW.id,
|
|
'open',
|
|
NEW.notes
|
|
);
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
-- Create the trigger
|
|
CREATE TRIGGER trg_collection_to_legal_matter
|
|
AFTER INSERT ON public.collections
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.auto_create_legal_matter_from_collection();
|