CREATE TABLE IF NOT EXISTS case_number_counter (
  period_key VARCHAR(7) PRIMARY KEY,
  last_value INTEGER NOT NULL DEFAULT 0,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION next_case_number(p_now TIMESTAMPTZ DEFAULT NOW())
RETURNS VARCHAR(50)
LANGUAGE plpgsql
AS $$
DECLARE
  period_key VARCHAR(7);
  next_value INTEGER;
BEGIN
  period_key := TO_CHAR(p_now, 'YYYY_MM');

  INSERT INTO case_number_counter (period_key, last_value, updated_at)
  VALUES (period_key, 1, NOW())
  ON CONFLICT (period_key)
  DO UPDATE SET
    last_value = case_number_counter.last_value + 1,
    updated_at = NOW()
  RETURNING last_value INTO next_value;

  RETURN period_key || '_' || LPAD(next_value::TEXT, 4, '0');
END;
$$;

ALTER TABLE br_case
  ALTER COLUMN case_number SET DEFAULT next_case_number();

UPDATE br_case
SET case_number = next_case_number(created_at)
WHERE case_number IS NULL OR BTRIM(case_number) = '';

ALTER TABLE committee
  ADD COLUMN IF NOT EXISTS chair_user_id BIGINT REFERENCES app_user(id),
  ADD COLUMN IF NOT EXISTS secretary_user_id BIGINT REFERENCES app_user(id),
  ADD COLUMN IF NOT EXISTS default_user_id BIGINT REFERENCES app_user(id);

CREATE INDEX IF NOT EXISTS idx_committee_chair_user_id ON committee(chair_user_id);
CREATE INDEX IF NOT EXISTS idx_committee_secretary_user_id ON committee(secretary_user_id);
CREATE INDEX IF NOT EXISTS idx_committee_default_user_id ON committee(default_user_id);
CREATE INDEX IF NOT EXISTS idx_br_case_committee_id ON br_case(committee_id);
CREATE INDEX IF NOT EXISTS idx_task_assigned_user_id ON task(assigned_user_id);
CREATE INDEX IF NOT EXISTS idx_meeting_committee_id ON meeting(committee_id);
CREATE INDEX IF NOT EXISTS idx_deadline_case_id ON deadline(case_id);