-- 005: G-3 institutional decision (owner-approved 2026-10-27)
-- Approved formal membership status values: عضو / عضو منتسب / قيد التحقق / موقوف
-- plus suspension duration tracking (suspension_until).
-- Reference data only — no transition matrix is seeded (no approved transition rules exist;
-- status_transitions stays empty until governance supplies one).

-- +up
INSERT INTO statuses (group_id, code, name_ar, sort_order, is_terminal)
SELECT sg.id, v.code, v.name_ar, v.sort_order, FALSE
FROM status_groups sg,
     (VALUES
       ('member',            'عضو',          20),
       ('associate_member',  'عضو منتسب',    30),
       ('under_verification','قيد التحقق',   40),
       ('suspended',         'موقوف',        50)
     ) AS v(code, name_ar, sort_order)
WHERE sg.code = 'member_formal_status'
ON CONFLICT (group_id, code) DO NOTHING;

-- Suspension duration: on members for the CURRENT state, and on the history row
-- that decided it (source of truth for the decision record). NULL = indefinite
-- («حتى إشعار آخر»). Expiry is surfaced for humans — NEVER auto-changes status.
ALTER TABLE members ADD COLUMN suspension_until DATE NULL;
ALTER TABLE member_status_history ADD COLUMN suspension_until DATE NULL;

-- +down
-- Repoint any records using the G-3 values back to the safe default, then remove them.
UPDATE member_status_history h
   SET from_status_id = NULL
 WHERE h.from_status_id IN (
   SELECT s.id FROM statuses s JOIN status_groups sg ON sg.id = s.group_id
    WHERE sg.code = 'member_formal_status'
      AND s.code IN ('member','associate_member','under_verification','suspended'));
UPDATE member_status_history h
   SET to_status_id = (SELECT s.id FROM statuses s JOIN status_groups sg ON sg.id = s.group_id
                        WHERE sg.code = 'member_formal_status' AND s.code = 'unspecified'),
       suspension_until = NULL
 WHERE h.to_status_id IN (
   SELECT s.id FROM statuses s JOIN status_groups sg ON sg.id = s.group_id
    WHERE sg.code = 'member_formal_status'
      AND s.code IN ('member','associate_member','under_verification','suspended'));
UPDATE members m
   SET formal_membership_status_id = (SELECT s.id FROM statuses s JOIN status_groups sg ON sg.id = s.group_id
                                       WHERE sg.code = 'member_formal_status' AND s.code = 'unspecified'),
       suspension_until = NULL
 WHERE m.formal_membership_status_id IN (
   SELECT s.id FROM statuses s JOIN status_groups sg ON sg.id = s.group_id
    WHERE sg.code = 'member_formal_status'
      AND s.code IN ('member','associate_member','under_verification','suspended'));
DELETE FROM statuses s
 USING status_groups sg
 WHERE sg.id = s.group_id
   AND sg.code = 'member_formal_status'
   AND s.code IN ('member','associate_member','under_verification','suspended');
ALTER TABLE member_status_history DROP COLUMN IF EXISTS suspension_until;
ALTER TABLE members DROP COLUMN IF EXISTS suspension_until;