-- 006: Organizational structure core (conservative, rule-free data holders)
-- Constraints honoring open governance questions:
--  - G-1: every position REQUIRES a documented decision (appointment_decision_ref NOT NULL);
--    no accounts are created for holders; the optional member link is data only.
--  - G-4: the 20-member readiness threshold is DISPLAYED as a reference only — nothing automated.
--  - No seeded units/positions and no invented unit/title taxonomies: humans create everything
--    with documentation. Unit kind is the minimal structural fact (national vs governorate).

-- +up
CREATE TABLE org_units (
  id BIGSERIAL PRIMARY KEY,
  unit_kind VARCHAR(30) NOT NULL CHECK (unit_kind IN ('national', 'governorate')),
  governorate_id INT NULL REFERENCES governorates(id),
  name_ar VARCHAR(200) NOT NULL,
  formation_decision_ref VARCHAR(255) NOT NULL,
  notes TEXT NULL,
  is_demo BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by BIGINT NULL REFERENCES users(id),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by BIGINT NULL REFERENCES users(id),
  deleted_at TIMESTAMPTZ NULL,
  archive_reason VARCHAR(500) NULL,
  CONSTRAINT ck_org_units_kind_gov CHECK (
    (unit_kind = 'governorate' AND governorate_id IS NOT NULL)
    OR (unit_kind = 'national' AND governorate_id IS NULL)
  )
);
CREATE INDEX idx_org_units_gov ON org_units(governorate_id) WHERE deleted_at IS NULL;

CREATE TABLE org_positions (
  id BIGSERIAL PRIMARY KEY,
  org_unit_id BIGINT NOT NULL REFERENCES org_units(id),
  member_id BIGINT NULL REFERENCES members(id),
  title_ar VARCHAR(150) NOT NULL,
  appointment_decision_ref VARCHAR(255) NOT NULL,
  decided_by VARCHAR(150) NULL,
  started_on DATE NULL,
  ended_on DATE NULL,
  end_reason VARCHAR(500) NULL,
  is_demo BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by BIGINT NULL REFERENCES users(id),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by BIGINT NULL REFERENCES users(id),
  deleted_at TIMESTAMPTZ NULL,
  archive_reason VARCHAR(500) NULL
);
CREATE INDEX idx_org_positions_unit ON org_positions(org_unit_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_org_positions_member ON org_positions(member_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_org_units_positions_open ON org_positions(org_unit_id) WHERE deleted_at IS NULL AND ended_on IS NULL;

-- +down
DROP TABLE IF EXISTS org_positions;
DROP TABLE IF EXISTS org_units;