-- 009: Member profile completion (UI/UX order 2026-10-28)
--  - membership_no: the OFFICIAL membership number — unique, searchable, NOT the db id.
--    Existing members keep NULL until the numbering policy is decided (safe approach).
--  - education: single record per member (v1 — multiple degrees would be a new decision).
--  - photo: official profile photo stored as bytes IN the database (secure: served only
--    through the authenticated, scope-checked endpoint; included in encrypted backups).
--  - member_affiliations: civil-society MEMBERSHIPS (syndicates, clubs, NGOs, universities).
--  - member_external_positions: official POSITIONS held in external organizations.
--    Distinct tables: membership ≠ holding a position (explicit owner requirement).
--    Internal union positions remain in org_positions (G-1 documented appointments).

-- +up
ALTER TABLE members ADD COLUMN membership_no VARCHAR(32) NULL;
CREATE UNIQUE INDEX uq_members_membership_no
  ON members(membership_no)
  WHERE membership_no IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX idx_members_membership_no
  ON members(membership_no)
  WHERE membership_no IS NOT NULL AND deleted_at IS NULL;

ALTER TABLE members ADD COLUMN university VARCHAR(200) NULL;
ALTER TABLE members ADD COLUMN faculty VARCHAR(200) NULL;
ALTER TABLE members ADD COLUMN qualification VARCHAR(200) NULL;
ALTER TABLE members ADD COLUMN graduation_year SMALLINT NULL
  CHECK (graduation_year IS NULL OR graduation_year BETWEEN 1950 AND 2100);

ALTER TABLE members ADD COLUMN photo BYTEA NULL;
ALTER TABLE members ADD COLUMN photo_mime VARCHAR(40) NULL;
ALTER TABLE members ADD COLUMN photo_updated_at TIMESTAMPTZ NULL;

CREATE TABLE member_affiliations (
  id BIGSERIAL PRIMARY KEY,
  member_id BIGINT NOT NULL REFERENCES members(id),
  kind VARCHAR(30) NOT NULL CHECK (kind IN ('syndicate','club','ngo','university','other')),
  org_name VARCHAR(200) NOT NULL,
  member_role VARCHAR(150) NULL,
  started_on DATE NULL,
  ended_on DATE NULL,
  notes VARCHAR(500) NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by BIGINT NULL REFERENCES users(id),
  deleted_at TIMESTAMPTZ NULL
);
CREATE INDEX idx_member_affiliations ON member_affiliations(member_id) WHERE deleted_at IS NULL;

CREATE TABLE member_external_positions (
  id BIGSERIAL PRIMARY KEY,
  member_id BIGINT NOT NULL REFERENCES members(id),
  org_name VARCHAR(200) NOT NULL,
  title VARCHAR(150) NOT NULL,
  kind VARCHAR(30) NOT NULL CHECK (kind IN ('board','council','other')),
  started_on DATE NULL,
  ended_on DATE NULL,
  notes VARCHAR(500) NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by BIGINT NULL REFERENCES users(id),
  deleted_at TIMESTAMPTZ NULL
);
CREATE INDEX idx_member_external_positions ON member_external_positions(member_id) WHERE deleted_at IS NULL;

-- +down
DROP TABLE IF EXISTS member_external_positions;
DROP TABLE IF EXISTS member_affiliations;
DROP INDEX IF EXISTS idx_members_membership_no;
DROP INDEX IF EXISTS uq_members_membership_no;
ALTER TABLE members DROP COLUMN IF EXISTS photo_updated_at;
ALTER TABLE members DROP COLUMN IF EXISTS photo_mime;
ALTER TABLE members DROP COLUMN IF EXISTS photo;
ALTER TABLE members DROP COLUMN IF EXISTS graduation_year;
ALTER TABLE members DROP COLUMN IF EXISTS qualification;
ALTER TABLE members DROP COLUMN IF EXISTS faculty;
ALTER TABLE members DROP COLUMN IF EXISTS university;
ALTER TABLE members DROP COLUMN IF EXISTS membership_no;