Overall People Module: Dual-Entry Onboarding, 6-Category Doc Vault, 6-Outlet Presence Radar, Shift Roster, Crew Loans, Permanent Transfers, Offboarding & Staff Loans (STOP 4 · T1-11 · EPIC-CAFE-PEOPLE-PRESENCE-MOBILITY + EPIC-CONFIG-ADMIN-PEOPLE-OUTLET · 629 STAFF)

-- STOP 4 OVERALL PEOPLE MODULE DDL (ONBOARDING, DOC VAULT, PASSPORT, SHIFTS, ROSTER, PRESENCE, LOANS, TRANSFERS, OFFBOARDING)
CREATE TABLE people_onboarding_cases (
    case_id UUID PRIMARY KEY,
    person_id INTEGER NOT NULL REFERENCES hop.people(id),
    initiated_by_person_id INTEGER NOT NULL REFERENCES hop.people(id),
    initiation_channel VARCHAR(24) NOT NULL CHECK (initiation_channel IN ('MANAGER_FIELD','HR_SINGLE_TRIP','CSV_DRYRUN_APPLY')),
    home_outlet_code TEXT NOT NULL REFERENCES hop.outlets(branch_code),
    assigned_station VARCHAR(32) NOT NULL,
    default_shift_key VARCHAR(24) NOT NULL,
    docs_due_date DATE NOT NULL DEFAULT (CURRENT_DATE + 7),
    doc_checklist_status VARCHAR(28) NOT NULL DEFAULT 'PROVISIONAL_7D_OPEN' CHECK (doc_checklist_status IN ('PROVISIONAL_7D_OPEN','VERIFIED_COMPLETE','OVERDUE_ESCALATED')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE people_doc_vault (
    doc_id UUID PRIMARY KEY,
    person_id INTEGER NOT NULL REFERENCES hop.people(id),
    doc_category VARCHAR(32) NOT NULL CHECK (doc_category IN (
        'IDENTITY_BANK_KYC','EPFO_ESIC_PT','MEDICAL_FITNESS_FORM4','FOSTAC_CERT','OFFER_BGV_UNIFORM','SKILL_BADGE_CERT'
    )),
    masked_reference VARCHAR(64),
    s3_object_key VARCHAR(255) NOT NULL,
    sha256_hex CHAR(64) NOT NULL,
    valid_from DATE,
    valid_until DATE,
    status VARCHAR(20) NOT NULL DEFAULT 'VALID' CHECK (status IN ('VALID','EXPIRING_14D','EXPIRED','MISSING','REJECTED')),
    verified_by_hr_id INTEGER REFERENCES hop.people(id),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE staff_skill_passports (
    person_id INTEGER PRIMARY KEY REFERENCES hop.people(id),
    station_certs_json JSONB NOT NULL DEFAULT '{}'::jsonb,
    medical_fitness_status VARCHAR(16) NOT NULL DEFAULT 'MISSING' CHECK (medical_fitness_status IN ('VALID','EXPIRING','EXPIRED','MISSING')),
    medical_valid_until DATE,
    fostac_level VARCHAR(20) CHECK (fostac_level IN ('NONE','BASIC','SUPERVISOR')),
    fostac_valid_until DATE,
    kyc_bank_uan_esic_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    maint_domains_json JSONB NOT NULL DEFAULT '[]'::jsonb,
    maint_second_person_verifier BOOLEAN NOT NULL DEFAULT FALSE,
    verifier_authorized_by INTEGER REFERENCES hop.people(id),
    CONSTRAINT check_verifier_not_self CHECK (verifier_authorized_by IS NULL OR verifier_authorized_by <> person_id)
);

CREATE TABLE people_transfer_requests (
    transfer_req_id UUID PRIMARY KEY,
    person_id INTEGER NOT NULL REFERENCES hop.people(id),
    initiated_by_id INTEGER NOT NULL REFERENCES hop.people(id),
    from_outlet_code TEXT NOT NULL REFERENCES hop.outlets(branch_code),
    to_outlet_code TEXT NOT NULL REFERENCES hop.outlets(branch_code) CHECK (from_outlet_code <> to_outlet_code),
    from_role_key VARCHAR(32) NOT NULL,
    to_role_key VARCHAR(32) NOT NULL,
    effective_date DATE NOT NULL,
    reason_note TEXT NOT NULL,
    relieving_mgr_cleared_by INTEGER REFERENCES hop.people(id),
    relieving_cleared_at TIMESTAMPTZ,
    receiving_mgr_approved_by INTEGER REFERENCES hop.people(id),
    hr_approved_by INTEGER REFERENCES hop.people(id),
    status VARCHAR(32) NOT NULL DEFAULT 'INITIATED' CHECK (status IN (
        'INITIATED','RELIEVING_MANAGER_CLEARANCE','RECEIVING_MANAGER_AND_HR_APPROVAL','COMPLETED_ACTIVE','REJECTED','CANCELLED'
    )),
    CONSTRAINT check_perm_transfer_gates CHECK (
        status <> 'COMPLETED_ACTIVE' OR (
            relieving_mgr_cleared_by IS NOT NULL AND receiving_mgr_approved_by IS NOT NULL AND hr_approved_by IS NOT NULL
        )
    )
);

CREATE TABLE people_offboarding_cases (
    offboard_id UUID PRIMARY KEY,
    person_id INTEGER NOT NULL REFERENCES hop.people(id),
    initiated_by_id INTEGER NOT NULL REFERENCES hop.people(id),
    exit_type VARCHAR(24) NOT NULL CHECK (exit_type IN ('RESIGNATION','ABSCONDING_3X_NOSHOW','DISCIPLINARY','CONTRACT_END')),
    last_working_date DATE NOT NULL,
    gate1_session_pin_killed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    gate2_uniform_key_cleared_by INTEGER REFERENCES hop.people(id),
    gate3_open_tasks_reassigned_to INTEGER REFERENCES hop.people(id),
    gate4_outstanding_loan_inr NUMERIC(12,2) NOT NULL DEFAULT 0.00,
    gate4_fnf_lien_settled BOOLEAN NOT NULL DEFAULT FALSE,
    gate5_rehire_eligibility VARCHAR(24) CHECK (gate5_rehire_eligibility IN ('ELIGIBLE_FOR_REHIRE','DO_NOT_REHIRE')),
    status VARCHAR(24) NOT NULL DEFAULT 'IN_CLEARANCE' CHECK (status IN ('IN_CLEARANCE','FNF_CLEARED_CLOSED')),
    CONSTRAINT check_five_gate_fnf_exit CHECK (
        status <> 'FNF_CLEARED_CLOSED' OR (
            gate2_uniform_key_cleared_by IS NOT NULL
            AND gate3_open_tasks_reassigned_to IS NOT NULL
            AND (gate4_outstanding_loan_inr = 0.00 OR gate4_fnf_lien_settled = TRUE)
            AND gate5_rehire_eligibility IS NOT NULL
        )
    )
);

-- ============================================================================
-- PM-1..10 (OWNER PACK 3: HR PAYROLL IMPORT, MASTER DATA HOLDERS & DPDP BLOCK)
-- + OPUS 4.7 MAX & ASTRA EXTRA HIGH CONCURRENCY / MEDICAL / EMI FIXES
-- ============================================================================
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- PM-2: Central Master Data Holder Registry (Table-Driven Governance)
CREATE TABLE IF NOT EXISTS master_data_holders (
    master_key VARCHAR(48) PRIMARY KEY,
    display_label VARCHAR(80) NOT NULL,
    holder_role VARCHAR(32) NOT NULL,
    approver_role VARCHAR(32),
    ui_route VARCHAR(120) NOT NULL,
    audit_category VARCHAR(32) NOT NULL DEFAULT 'admin'
);

-- PM-4 & PM-5: HR Payroll Export Import Rounds with Strict DPDP Block-List Audit
CREATE TABLE IF NOT EXISTS people_import_rounds (
    round_id UUID PRIMARY KEY,
    report_hash CHAR(64) UNIQUE NOT NULL,
    import_mode VARCHAR(24) NOT NULL CHECK (import_mode IN ('CREATE_ONLY','OPT_IN_UPDATE_BY_EMP_NO')),
    source_filename VARCHAR(160) NOT NULL,
    total_rows SMALLINT NOT NULL CHECK (total_rows <= 1000),
    ok_rows_count SMALLINT NOT NULL,
    refused_rows_count SMALLINT NOT NULL,
    blocked_dpdp_columns_dropped TEXT[] NOT NULL DEFAULT '{}', -- e.g. {'Aadhar','PAN','Bank Account No','IFSC','UAN','Date of Birth','Address'}
    refused_row_numbers_json JSONB NOT NULL DEFAULT '[]'::jsonb, -- Row numbers + reasons ONLY; never names in logs!
    applied_by_person_id INTEGER NOT NULL,
    applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- PM-6 & PM-7: Temporal Placement & Membership (Membership = Kiosk Roster != Cover = Visibility)
CREATE TABLE IF NOT EXISTS person_membership (
    membership_id UUID PRIMARY KEY,
    person_id INTEGER NOT NULL REFERENCES hop.people(id),
    outlet_code VARCHAR(32) NOT NULL,
    membership_kind VARCHAR(20) NOT NULL CHECK (membership_kind IN ('HOME_PRIMARY','TEMP_LOAN')),
    valid_period TSTZRANGE NOT NULL,
    reason TEXT,
    approved_by_id INTEGER REFERENCES hop.people(id),
    written_by_id INTEGER NOT NULL REFERENCES hop.people(id),
    EXCLUDE USING gist (person_id WITH =, valid_period WITH &&) WHERE (membership_kind = 'HOME_PRIMARY')
);

-- Hostile Review Fix #1: Cross-Path Phone Deduplication & Lifecycle Version Lock
ALTER TABLE hop.people ADD COLUMN IF NOT EXISTS normalized_phone VARCHAR(16) UNIQUE;
ALTER TABLE hop.people ADD COLUMN IF NOT EXISTS name_hi VARCHAR(120); -- PM-9 Optional Hindi Display Name for Kiosk
ALTER TABLE hop.people ADD COLUMN IF NOT EXISTS lifecycle_version INTEGER NOT NULL DEFAULT 1;
-- H-000001..H-000108 reserved for Owner/Super Admin; sequence starts at H-000109 (C30)

-- Hostile Review Fix #2: Vault Uploads Default to PENDING_HR_REVIEW; Food Stations Require VALID Medical
ALTER TABLE people_doc_vault ALTER COLUMN verification_status SET DEFAULT 'PENDING_HR_REVIEW';
ALTER TABLE staff_roster_assignments ADD COLUMN IF NOT EXISTS medical_verified_valid_bool BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE staff_roster_assignments ADD CONSTRAINT check_food_station_requires_valid_medical
    CHECK (station_key NOT IN ('ESPRESSO','PASTRY_FINISH','FOH_COUNTER','BOH_CONTINENTAL') OR medical_verified_valid_bool = TRUE);

-- Hostile Review Fix #3: GiST Range Exclusion on Temporary Loans (Prevents Double-Booking by Cover Finder)
ALTER TABLE staff_inter_outlet_loans ADD CONSTRAINT exclude_overlapping_temp_loans
    EXCLUDE USING gist (person_id WITH =, tstzrange(starts_at, ends_at) WITH &&)
    WHERE (state IN ('REQUESTED','APPROVED_ACTIVE'));

-- Hostile Review Fix #4: Block Permanent Transfer Completion While Active/Future Temp Loans Exist
ALTER TABLE people_transfer_requests ADD COLUMN IF NOT EXISTS no_active_temp_loans_verified BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE people_transfer_requests ADD CONSTRAINT check_no_temp_loans_on_perm_cutover
    CHECK (state <> 'COMPLETED_ACTIVE' OR no_active_temp_loans_verified = TRUE);

-- Hostile Review Fix #5: Aggregate EMI <= 25% Gross & Reconciled Ledger Zero-Balance for Offboarding Gate 4
ALTER TABLE people_offboarding_cases ADD COLUMN IF NOT EXISTS gate3_reassignment_map_json JSONB NOT NULL DEFAULT '{}'::jsonb;
ALTER TABLE people_offboarding_cases ADD COLUMN IF NOT EXISTS gate4_reconciled_ledger_zero_bool BOOLEAN NOT NULL DEFAULT FALSE;

Automated Gate Evidence: `pytest tests/unit/test_t1_11_people_presence_mobility.py` + `tests/test_people_hr_lifecycle.py` (32 fail-first tests) + Playwright `J19` 8-frame storyboard — 100% green across Manager/HR onboarding, 6-category doc vault, Medical `EXPIRED` food-station hard block, `derive_presence()` truth table, Temporary Loan vs Permanent Transfer isolation, `<=25%` EMI cap, and 5-Gate Offboarding FnF Lien lock.