63 lines
1.6 KiB
PL/PgSQL
63 lines
1.6 KiB
PL/PgSQL
ALTER TABLE meeting_agenda_item
|
||
ADD COLUMN IF NOT EXISTS case_id BIGINT REFERENCES br_case(id) ON DELETE SET NULL;
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_meeting_agenda_item_case_id ON meeting_agenda_item(case_id);
|
||
|
||
CREATE TABLE IF NOT EXISTS meeting_agenda_counter (
|
||
meeting_id BIGINT PRIMARY KEY REFERENCES meeting(id) ON DELETE CASCADE,
|
||
last_value INTEGER NOT NULL DEFAULT 0,
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE OR REPLACE FUNCTION next_agenda_number(p_meeting_id BIGINT)
|
||
RETURNS TEXT
|
||
LANGUAGE plpgsql
|
||
AS $$
|
||
DECLARE
|
||
v_seq INTEGER;
|
||
BEGIN
|
||
IF p_meeting_id IS NULL THEN
|
||
RAISE EXCEPTION 'meeting_id is required for next_agenda_number';
|
||
END IF;
|
||
|
||
LOOP
|
||
UPDATE meeting_agenda_counter
|
||
SET last_value = last_value + 1,
|
||
updated_at = NOW()
|
||
WHERE meeting_id = p_meeting_id
|
||
RETURNING last_value INTO v_seq;
|
||
|
||
IF FOUND THEN
|
||
EXIT;
|
||
END IF;
|
||
|
||
BEGIN
|
||
INSERT INTO meeting_agenda_counter(meeting_id, last_value)
|
||
VALUES (p_meeting_id, 1);
|
||
v_seq := 1;
|
||
EXIT;
|
||
EXCEPTION
|
||
WHEN unique_violation THEN
|
||
NULL;
|
||
END;
|
||
END LOOP;
|
||
|
||
RETURN v_seq::TEXT;
|
||
END;
|
||
$$;
|
||
|
||
INSERT INTO meeting_agenda_item(meeting_id, agenda_number, title, description, duration_minutes, case_id)
|
||
SELECT NULL,
|
||
NULL,
|
||
c.case_number || ' – ' || c.title,
|
||
COALESCE(c.summary, 'Automatisch aus Vorgang ' || c.case_number || ' erzeugt'),
|
||
NULL,
|
||
c.id
|
||
FROM br_case c
|
||
WHERE c.deleted_at IS NULL
|
||
AND NOT EXISTS (
|
||
SELECT 1
|
||
FROM meeting_agenda_item ai
|
||
WHERE ai.case_id = c.id
|
||
);
|