-- 004: Member 360 core — structural, zero-value tables (BOUSLA-SPEC v0.2 §3)
-- Implements: members (MEM-REQ-001/002/017), member_contacts (MEM-REQ-003/004),
--             member_status_history (MEM-REQ-006), users.member_id (MEM-REQ-012).
-- Deliberately NOT here (BLOCKED by decision register): affiliations (DEC-09),
-- skills (DEC-16), notes (DEC-11), participation snapshots (DEC-21), KPI tables (DEC-04/05).
-- Status values: ONLY the documented G-3 safe default is seeded. No other values invented.

-- +up
-- Formal membership status group + the single documented safe default (OPEN_QUESTIONS G-3)
INSERT INTO status_groups (code, name_ar) VALUES ('member_formal_status', 'حالة العضوية الرسمية')
ON CONFLICT (code) DO NOTHING;

INSERT INTO statuses (group_id, code, name_ar, sort_order, is_terminal)
SELECT sg.id, 'unspecified', 'غير محدد — بانتظار التقرير التنظيمي', 10, FALSE
FROM status_groups sg WHERE sg.code = 'member_formal_status'
ON CONFLICT (group_id, code) DO NOTHING;

CREATE TABLE members (
  id BIGSERIAL PRIMARY KEY,
  -- UUIDv4 generated by the application; native uuid type = DB-level format validation.
  -- NOT an authorization mechanism — every access still passes permission + scope checks (MEM-REQ-002).
  public_uuid UUID NOT NULL UNIQUE CHECK (public_uuid::text <> '00000000-0000-0000-0000-000000000000'),
  full_name VARCHAR(255) NOT NULL,
  governorate_id SMALLINT NULL REFERENCES governorates(id), -- NULL = unknown at intake
  formal_membership_status_id BIGINT NOT NULL REFERENCES statuses(id), -- defaults to the safe G-3 value
  record_source VARCHAR(100) NOT NULL DEFAULT 'manual_entry',
  source_record_ref VARCHAR(255) NULL,
  verified_at TIMESTAMPTZ NULL,
  is_demo BOOLEAN NOT NULL DEFAULT FALSE,
  deleted_at TIMESTAMPTZ NULL,          -- soft archive (MEM-REQ-015 active->archived; NO irreversible delete)
  archive_reason VARCHAR(500) NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by BIGINT NULL REFERENCES users(id),
  updated_by BIGINT NULL REFERENCES users(id)
);
CREATE INDEX idx_members_governorate ON members(governorate_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_members_full_name ON members(full_name text_pattern_ops);
-- Phone is NEVER a key or unique (accepting legitimate duplicates); index is for duplicate DETECTION only
CREATE INDEX idx_members_status ON members(formal_membership_status_id);

-- Restricted contact data — separate table, separate permission, masked by default (MEM-REQ-003)
CREATE TABLE member_contacts (
  id BIGSERIAL PRIMARY KEY,
  member_id BIGINT NOT NULL UNIQUE REFERENCES members(id) ON DELETE CASCADE,
  phone_primary VARCHAR(32) NULL CHECK (phone_primary ~ '^[0-9]{8,15}$'), -- digits-only, normalized at entry
  phone_alt VARCHAR(32) NULL CHECK (phone_alt ~ '^[0-9]{8,15}$'),
  email VARCHAR(255) NULL,
  preferred_channel VARCHAR(20) NULL,
  -- Consent structure (MEM-REQ-004): timestamps + notice version; legal activation awaits DEC-20
  consent_contact_at TIMESTAMPTZ NULL,
  consent_channel VARCHAR(20) NULL,
  consent_notice_version VARCHAR(50) NULL,
  consent_withdrawn_at TIMESTAMPTZ NULL,
  contact_verification_status VARCHAR(20) NOT NULL DEFAULT 'unverified'
    CHECK (contact_verification_status IN ('unverified', 'verified', 'invalid')),
  verified_at TIMESTAMPTZ NULL,
  last_contacted_at TIMESTAMPTZ NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by BIGINT NULL REFERENCES users(id),
  updated_by BIGINT NULL REFERENCES users(id)
);
CREATE INDEX idx_member_contacts_phone ON member_contacts(phone_primary); -- duplicate detection ONLY, not unique

-- Formal status history — facts, never mutated (MEM-REQ-006). Free-text decision refs are
-- documented-but-provisional (DEC-24): NOT referential integrity.
CREATE TABLE member_status_history (
  id BIGSERIAL PRIMARY KEY,
  member_id BIGINT NOT NULL REFERENCES members(id) ON DELETE CASCADE,
  from_status_id BIGINT NULL REFERENCES statuses(id),
  to_status_id BIGINT NOT NULL REFERENCES statuses(id),
  effective_from TIMESTAMPTZ NOT NULL DEFAULT now(),
  decision_ref VARCHAR(255) NULL,   -- provisional textual reference (DEC-24)
  decided_by VARCHAR(100) NULL,     -- authority name/role (organizational, not a user account)
  reason VARCHAR(500) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by BIGINT NULL REFERENCES users(id)
);
CREATE INDEX idx_member_status_history_member ON member_status_history(member_id, effective_from DESC);

-- Optional member<->account link (MEM-REQ-012): never auto-created, grants nothing by itself
ALTER TABLE users ADD COLUMN member_id BIGINT NULL REFERENCES members(id);
CREATE UNIQUE INDEX idx_users_member ON users(member_id) WHERE member_id IS NOT NULL;

-- +down
DROP INDEX IF EXISTS idx_users_member;
ALTER TABLE users DROP COLUMN IF EXISTS member_id;
DROP TABLE IF EXISTS member_status_history;
DROP TABLE IF EXISTS member_contacts;
DROP TABLE IF EXISTS members;
DELETE FROM statuses WHERE group_id IN (SELECT id FROM status_groups WHERE code = 'member_formal_status');
DELETE FROM status_groups WHERE code = 'member_formal_status';