-- =====================================================================
--  FinLend Core — Production DDL
--  Target      : MySQL 8.0.16+ (InnoDB, utf8mb4)  |  MariaDB 10.6+
--  Entity      : NBFC / Nidhi Company / MFI (JLG-SHG) — multi-entity
--  Author      : Infossel Soft Solutions
-- ---------------------------------------------------------------------
--  MONEY CONVENTION  (read this before touching any amount column)
--  ---------------------------------------------------------------
--  Every monetary column is BIGINT and stores PAISE (1 INR = 100 paise).
--  Rationale: exact integer arithmetic end-to-end in PHP with zero
--  float drift, no DECIMAL<->float coercion at the PDO boundary, and
--  penny-perfect reconciliation of amortisation schedules. Presentation
--  layer divides by 100 exactly once, in FinLend\Core\Money.
--  Rates are stored in BASIS POINTS (bps): 100 bps = 1.00%.
-- ---------------------------------------------------------------------
--  IMMUTABILITY
--  Ledger (acc_journal_lines), audit_logs and loan_repayments are
--  APPEND-ONLY. Corrections are contra/reversal rows, never UPDATEs.
--  Triggers at the bottom of this file enforce this at the DB level so
--  that a compromised application account still cannot rewrite history.
-- =====================================================================


-- =====================================================================
-- 01. ORGANISATION, BRANCH & FINANCIAL CALENDAR
-- =====================================================================

CREATE TABLE organisations (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    code                VARCHAR(16)     NOT NULL COMMENT 'Short tenant code, appears in all human-readable refs',
    legal_name          VARCHAR(200)    NOT NULL,
    trade_name          VARCHAR(200)        NULL,
    entity_type         ENUM('nbfc','nidhi','mfi','nbfc_mfi','section8_mfi','coop')
                                        NOT NULL COMMENT 'Selects the regulatory rule-pack applied at runtime',
    cin                 VARCHAR(21)         NULL COMMENT 'MCA Corporate Identity Number',
    rbi_cor_number      VARCHAR(32)         NULL COMMENT 'RBI Certificate of Registration (NBFCs)',
    rbi_layer           ENUM('base','middle','upper','top')     NULL COMMENT 'RBI Scale Based Regulation layer',
    nidhi_ndh4_status   ENUM('not_applicable','pending','approved','rejected') NOT NULL DEFAULT 'not_applicable',
    gstin               VARCHAR(15)         NULL,
    pan                 VARCHAR(10)         NULL,
    registered_address  VARCHAR(500)        NULL,
    incorporation_date  DATE                NULL,
    fy_start_month      TINYINT UNSIGNED NOT NULL DEFAULT 4 COMMENT 'Indian FY starts in April',
    base_currency       CHAR(3)         NOT NULL DEFAULT 'INR',
    logo_path           VARCHAR(255)        NULL,
    is_active           TINYINT(1)      NOT NULL DEFAULT 1,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_org_code (code),
    UNIQUE KEY uq_org_cin (cin),
    KEY idx_org_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE branches (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED    NOT NULL,
    parent_branch_id    INT UNSIGNED        NULL COMMENT 'Self-reference: region -> cluster -> branch hierarchy',
    code                VARCHAR(16)     NOT NULL COMMENT 'Used as prefix in member/loan account numbers',
    name                VARCHAR(150)    NOT NULL,
    branch_type         ENUM('head_office','regional','branch','collection_centre','business_correspondent')
                                        NOT NULL DEFAULT 'branch',
    address_line1       VARCHAR(200)        NULL,
    address_line2       VARCHAR(200)        NULL,
    city                VARCHAR(100)        NULL,
    district            VARCHAR(100)        NULL,
    state_code          CHAR(2)             NULL COMMENT 'GST state code, e.g. 33 = Tamil Nadu',
    pincode             CHAR(6)             NULL,
    latitude            DECIMAL(10,7)       NULL COMMENT 'For geo-fencing field collections',
    longitude           DECIMAL(10,7)       NULL,
    phone               VARCHAR(20)         NULL,
    email               VARCHAR(150)        NULL,
    opening_date        DATE                NULL,
    cash_limit          BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Paise. Max cash-in-hand before forced remittance',
    -- Every branch has its own control accounts so the trial balance is
    -- provable branch-wise without scanning the whole journal.
    cash_gl_id          INT UNSIGNED        NULL,
    bank_gl_id          INT UNSIGNED        NULL,
    is_active           TINYINT(1)      NOT NULL DEFAULT 1,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_branch_code (org_id, code),
    KEY idx_branch_org_active (org_id, is_active),
    KEY idx_branch_parent (parent_branch_id),
    CONSTRAINT fk_branch_org    FOREIGN KEY (org_id)           REFERENCES organisations (id) ON DELETE RESTRICT,
    CONSTRAINT fk_branch_parent FOREIGN KEY (parent_branch_id) REFERENCES branches (id)      ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Business-day control. Nothing may post to a CLOSED day; the EOD job
-- flips the flag after NPA marking + accrual + trial-balance proof.
CREATE TABLE business_days (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED    NOT NULL,
    branch_id           INT UNSIGNED        NULL COMMENT 'NULL = org-wide holiday',
    business_date       DATE            NOT NULL,
    status              ENUM('open','closing','closed','reopened') NOT NULL DEFAULT 'open',
    is_holiday          TINYINT(1)      NOT NULL DEFAULT 0,
    holiday_reason      VARCHAR(150)        NULL,
    opened_by           INT UNSIGNED        NULL,
    closed_by           INT UNSIGNED        NULL,
    opened_at           DATETIME(6)         NULL,
    closed_at           DATETIME(6)         NULL,
    eod_report_hash     CHAR(64)            NULL COMMENT 'SHA-256 of the day-end trial balance snapshot',
    PRIMARY KEY (id),
    UNIQUE KEY uq_busday (org_id, branch_id, business_date),
    KEY idx_busday_status (org_id, status, business_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 02. RBAC — ROLES, PERMISSIONS, USERS, SESSIONS
-- =====================================================================

CREATE TABLE roles (
    id                  SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED      NOT NULL,
    slug                VARCHAR(48)       NOT NULL COMMENT 'super_admin, admin, branch_manager, loan_officer, collector, accountant, auditor, customer',
    name                VARCHAR(100)      NOT NULL,
    description         VARCHAR(255)          NULL,
    scope               ENUM('global','branch','self') NOT NULL DEFAULT 'branch'
                        COMMENT 'Row-level data scope automatically injected into every query',
    is_system           TINYINT(1)        NOT NULL DEFAULT 0 COMMENT 'System roles cannot be deleted',
    requires_2fa        TINYINT(1)        NOT NULL DEFAULT 0,
    max_approval_amount BIGINT UNSIGNED   NOT NULL DEFAULT 0 COMMENT 'Paise. 0 = no sanction authority',
    created_at          DATETIME(6)       NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_role_slug (org_id, slug),
    CONSTRAINT fk_role_org FOREIGN KEY (org_id) REFERENCES organisations (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE permissions (
    id                  SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    slug                VARCHAR(64)       NOT NULL COMMENT 'module.action e.g. loan.approve, repayment.reverse',
    module              VARCHAR(32)       NOT NULL,
    description         VARCHAR(255)          NULL,
    is_sensitive        TINYINT(1)        NOT NULL DEFAULT 0 COMMENT 'Sensitive grants force a maker-checker record',
    PRIMARY KEY (id),
    UNIQUE KEY uq_perm_slug (slug),
    KEY idx_perm_module (module)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE role_permissions (
    role_id             SMALLINT UNSIGNED NOT NULL,
    permission_id       SMALLINT UNSIGNED NOT NULL,
    granted_by          INT UNSIGNED          NULL,
    granted_at          DATETIME(6)       NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (role_id, permission_id),
    KEY idx_rp_perm (permission_id),
    CONSTRAINT fk_rp_role FOREIGN KEY (role_id)       REFERENCES roles (id)       ON DELETE CASCADE,
    CONSTRAINT fk_rp_perm FOREIGN KEY (permission_id) REFERENCES permissions (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE users (
    id                      INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    org_id                  INT UNSIGNED    NOT NULL,
    branch_id               INT UNSIGNED        NULL COMMENT 'Home branch. NULL for head-office/global users',
    employee_code           VARCHAR(32)         NULL,
    username                VARCHAR(64)     NOT NULL,
    email                   VARCHAR(150)        NULL,
    mobile                  VARCHAR(15)         NULL,
    full_name               VARCHAR(150)    NOT NULL,
    password_hash           VARCHAR(255)    NOT NULL COMMENT 'password_hash() with PASSWORD_ARGON2ID; bcrypt accepted for legacy rows',
    password_algo           VARCHAR(16)     NOT NULL DEFAULT 'argon2id',
    password_changed_at     DATETIME(6)         NULL,
    must_change_password    TINYINT(1)      NOT NULL DEFAULT 1,
    role_id                 SMALLINT UNSIGNED NOT NULL,
    -- TOTP secret is AES-256-GCM encrypted, never stored in plaintext
    twofa_secret_enc        VARBINARY(255)      NULL,
    twofa_enabled           TINYINT(1)      NOT NULL DEFAULT 0,
    twofa_recovery_codes    VARBINARY(2048)     NULL COMMENT 'Encrypted JSON array of single-use hashed codes',
    status                  ENUM('active','suspended','locked','resigned') NOT NULL DEFAULT 'active',
    failed_login_count      TINYINT UNSIGNED NOT NULL DEFAULT 0,
    locked_until            DATETIME(6)         NULL,
    last_login_at           DATETIME(6)         NULL,
    last_login_ip           VARBINARY(16)       NULL COMMENT 'INET6_ATON packed, IPv4+IPv6 safe',
    -- Field-staff attributes (collectors / loan officers)
    is_field_staff          TINYINT(1)      NOT NULL DEFAULT 0,
    cash_holding_limit      BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Paise. Max cash a collector may hold',
    device_binding_token    VARCHAR(128)        NULL COMMENT 'Ties the mobile app to one device',
    created_by              INT UNSIGNED        NULL,
    created_at              DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at              DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    deleted_at              DATETIME(6)         NULL COMMENT 'Soft delete — user rows are never hard-deleted (audit integrity)',
    PRIMARY KEY (id),
    UNIQUE KEY uq_user_username (org_id, username),
    UNIQUE KEY uq_user_employee (org_id, employee_code),
    KEY idx_user_branch (branch_id, status),
    KEY idx_user_role (role_id),
    KEY idx_user_email (email),
    CONSTRAINT fk_user_org    FOREIGN KEY (org_id)    REFERENCES organisations (id) ON DELETE RESTRICT,
    CONSTRAINT fk_user_branch FOREIGN KEY (branch_id) REFERENCES branches (id)      ON DELETE RESTRICT,
    CONSTRAINT fk_user_role   FOREIGN KEY (role_id)   REFERENCES roles (id)         ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Extra per-user grants/denies layered on top of the role matrix
CREATE TABLE user_permission_overrides (
    user_id             INT UNSIGNED      NOT NULL,
    permission_id       SMALLINT UNSIGNED NOT NULL,
    effect              ENUM('allow','deny') NOT NULL,
    valid_until         DATE                  NULL COMMENT 'Temporary elevation, e.g. leave cover',
    granted_by          INT UNSIGNED      NOT NULL,
    granted_at          DATETIME(6)       NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (user_id, permission_id),
    CONSTRAINT fk_upo_user FOREIGN KEY (user_id)       REFERENCES users (id)       ON DELETE CASCADE,
    CONSTRAINT fk_upo_perm FOREIGN KEY (permission_id) REFERENCES permissions (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Users may be granted visibility over branches beyond their home branch
CREATE TABLE user_branch_access (
    user_id             INT UNSIGNED NOT NULL,
    branch_id           INT UNSIGNED NOT NULL,
    access_level        ENUM('read','write') NOT NULL DEFAULT 'read',
    PRIMARY KEY (user_id, branch_id),
    CONSTRAINT fk_uba_user   FOREIGN KEY (user_id)   REFERENCES users (id)    ON DELETE CASCADE,
    CONSTRAINT fk_uba_branch FOREIGN KEY (branch_id) REFERENCES branches (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_sessions (
    id                  CHAR(64)        NOT NULL COMMENT 'SHA-256 of the session id — raw id never lands on disk',
    user_id             INT UNSIGNED    NOT NULL,
    ip_address          VARBINARY(16)       NULL,
    user_agent          VARCHAR(255)        NULL,
    device_fingerprint  VARCHAR(128)        NULL,
    channel             ENUM('web','mobile_app','api') NOT NULL DEFAULT 'web',
    payload             TEXT                NULL,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    last_activity_at    DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    expires_at          DATETIME(6)     NOT NULL,
    revoked_at          DATETIME(6)         NULL,
    PRIMARY KEY (id),
    KEY idx_sess_user (user_id, expires_at),
    KEY idx_sess_expiry (expires_at),
    CONSTRAINT fk_sess_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE login_attempts (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED        NULL,
    username_tried      VARCHAR(64)     NOT NULL,
    user_id             INT UNSIGNED        NULL,
    ip_address          VARBINARY(16)       NULL,
    user_agent          VARCHAR(255)        NULL,
    outcome             ENUM('success','bad_password','unknown_user','locked','2fa_failed','2fa_success') NOT NULL,
    attempted_at        DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    KEY idx_la_user_time (username_tried, attempted_at),
    KEY idx_la_ip_time (ip_address, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 03. MEMBERS / CUSTOMERS, KYC & GROUPS (SHG / JLG)
-- ---------------------------------------------------------------------
--  PII HANDLING
--  Aadhaar and PAN are stored as:
--    *_enc   VARBINARY  — AES-256-GCM ciphertext (nonce||tag||cipher)
--    *_bidx  CHAR(64)   — HMAC-SHA256 blind index, so we can still do
--                         exact-match de-duplication without decrypting
--    *_last4 CHAR(4)    — display-safe suffix for the UI and receipts
--  Under the Aadhaar Act (as amended) a lender that is not an
--  authorised authentication agency must NOT retain the full Aadhaar
--  number. Keep AADHAAR_MODE=offline_xml and populate
--  aadhaar_reference_no from the offline eKYC XML instead; the
--  aadhaar_enc column stays NULL unless legal counsel signs off.
-- =====================================================================

CREATE TABLE members (
    id                      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id                  INT UNSIGNED    NOT NULL,
    branch_id               INT UNSIGNED    NOT NULL,
    member_no               VARCHAR(32)     NOT NULL COMMENT 'Human-readable: {BRANCH}{YY}{SEQ}, generated in a serialisable txn',
    member_type             ENUM('individual','joint','proprietorship','partnership','pvt_ltd','llp','trust','shg','jlg')
                                            NOT NULL DEFAULT 'individual',
    -- ---- Identity -------------------------------------------------
    salutation              ENUM('Mr','Mrs','Ms','Dr','M/s')    NULL,
    first_name              VARCHAR(80)     NOT NULL,
    middle_name             VARCHAR(80)         NULL,
    last_name               VARCHAR(80)         NULL,
    full_name_search        VARCHAR(255) GENERATED ALWAYS AS
                            (TRIM(CONCAT_WS(' ', first_name, middle_name, last_name))) STORED,
    father_or_spouse_name   VARCHAR(160)        NULL,
    mother_name             VARCHAR(160)        NULL,
    date_of_birth           DATE                NULL,
    gender                  ENUM('male','female','transgender','not_disclosed') NULL,
    marital_status          ENUM('single','married','widowed','divorced','not_disclosed') NULL,
    -- ---- KYC identifiers (encrypted at rest) ----------------------
    aadhaar_enc             VARBINARY(255)      NULL,
    aadhaar_bidx            CHAR(64)            NULL,
    aadhaar_last4           CHAR(4)             NULL,
    aadhaar_reference_no    VARCHAR(64)         NULL COMMENT 'UIDAI offline eKYC reference — preferred over the raw number',
    aadhaar_verified_at     DATETIME(6)         NULL,
    aadhaar_verify_mode     ENUM('offline_xml','okyc','digilocker','biometric','manual') NULL,
    pan_enc                 VARBINARY(255)      NULL,
    pan_bidx                CHAR(64)            NULL,
    pan_last4               CHAR(4)             NULL,
    pan_verified_at         DATETIME(6)         NULL,
    pan_name_match_score    TINYINT UNSIGNED    NULL COMMENT '0-100 fuzzy match of NSDL name vs entered name',
    voter_id                VARCHAR(32)         NULL,
    driving_licence         VARCHAR(32)         NULL,
    passport_no             VARCHAR(32)         NULL,
    ckyc_number             VARCHAR(14)         NULL COMMENT 'CERSAI CKYC Identifier',
    ckyc_downloaded_at      DATETIME(6)         NULL,
    -- ---- Contact & address ----------------------------------------
    mobile                  VARCHAR(15)     NOT NULL,
    mobile_verified_at      DATETIME(6)         NULL,
    alt_mobile              VARCHAR(15)         NULL,
    email                   VARCHAR(150)        NULL,
    address_line1           VARCHAR(200)        NULL,
    address_line2           VARCHAR(200)        NULL,
    village_or_area         VARCHAR(120)        NULL,
    city                    VARCHAR(100)        NULL,
    district                VARCHAR(100)        NULL,
    state_code              CHAR(2)             NULL,
    pincode                 CHAR(6)             NULL,
    latitude                DECIMAL(10,7)       NULL,
    longitude               DECIMAL(10,7)       NULL,
    residence_type          ENUM('owned','rented','parental','company_provided','other') NULL,
    years_at_address        TINYINT UNSIGNED    NULL,
    -- ---- Livelihood / MFI socio-economic profile ------------------
    occupation              VARCHAR(120)        NULL,
    occupation_category     ENUM('agriculture','allied_agri','trade','services','manufacturing','salaried','other') NULL,
    monthly_income          BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Paise. Applicant only',
    annual_household_income BIGINT UNSIGNED NOT NULL DEFAULT 0
                            COMMENT 'Paise. RBI Microfinance Directions 2022 cap: Rs 3,00,000 for a loan to qualify as microfinance',
    household_size          TINYINT UNSIGNED NOT NULL DEFAULT 1,
    earning_members         TINYINT UNSIGNED NOT NULL DEFAULT 1,
    is_rural                TINYINT(1)      NOT NULL DEFAULT 0,
    ration_card_type        ENUM('apl','bpl','antyodaya','none') NULL,
    -- ---- Nidhi membership ------------------------------------------
    is_nidhi_member         TINYINT(1)      NOT NULL DEFAULT 0,
    shares_held             INT UNSIGNED    NOT NULL DEFAULT 0
                            COMMENT 'Nidhi Rules 2014 r.9/r.11: min 10 equity shares (Rs 100) to be admitted',
    share_certificate_no    VARCHAR(32)         NULL,
    membership_date         DATE                NULL,
    -- ---- Banking ----------------------------------------------------
    primary_bank_account_id BIGINT UNSIGNED     NULL,
    -- ---- Risk / AML -------------------------------------------------
    kyc_status              ENUM('pending','in_progress','verified','rejected','re_kyc_due') NOT NULL DEFAULT 'pending',
    kyc_verified_at         DATETIME(6)         NULL,
    kyc_verified_by         INT UNSIGNED        NULL,
    kyc_next_review_date    DATE                NULL COMMENT 'RBI KYC MD: 2y high-risk / 8y medium / 10y low',
    risk_category           ENUM('low','medium','high') NOT NULL DEFAULT 'low',
    is_pep                  TINYINT(1)      NOT NULL DEFAULT 0 COMMENT 'Politically Exposed Person — mandates enhanced due diligence',
    aml_screening_status    ENUM('not_screened','clear','potential_match','confirmed_match') NOT NULL DEFAULT 'not_screened',
    aml_screened_at         DATETIME(6)         NULL,
    is_blacklisted          TINYINT(1)      NOT NULL DEFAULT 0,
    blacklist_reason        VARCHAR(255)        NULL,
    -- ---- Portal access ----------------------------------------------
    portal_user_id          INT UNSIGNED        NULL COMMENT 'Optional linked users row with role=customer',
    passbook_no             VARCHAR(32)         NULL,
    photo_path              VARCHAR(255)        NULL,
    signature_path          VARCHAR(255)        NULL,
    -- ---- Lifecycle --------------------------------------------------
    status                  ENUM('prospect','active','dormant','closed','rejected','deceased') NOT NULL DEFAULT 'prospect',
    introduced_by_member_id BIGINT UNSIGNED     NULL,
    relationship_officer_id INT UNSIGNED        NULL COMMENT 'Owning loan officer',
    onboarded_channel       ENUM('branch','field_app','web_portal','bc_agent','api') NOT NULL DEFAULT 'branch',
    created_by              INT UNSIGNED        NULL,
    created_at              DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at              DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    deleted_at              DATETIME(6)         NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_member_no (org_id, member_no),
    -- Blind indexes give us duplicate detection without ever decrypting
    UNIQUE KEY uq_member_aadhaar_bidx (org_id, aadhaar_bidx),
    UNIQUE KEY uq_member_pan_bidx (org_id, pan_bidx),
    KEY idx_member_branch_status (branch_id, status),
    KEY idx_member_mobile (org_id, mobile),
    KEY idx_member_name (org_id, full_name_search(64)),
    KEY idx_member_kyc (org_id, kyc_status, kyc_next_review_date),
    KEY idx_member_officer (relationship_officer_id, status),
    KEY idx_member_risk (org_id, risk_category, is_pep),
    CONSTRAINT fk_member_org      FOREIGN KEY (org_id)                  REFERENCES organisations (id) ON DELETE RESTRICT,
    CONSTRAINT fk_member_branch   FOREIGN KEY (branch_id)               REFERENCES branches (id)      ON DELETE RESTRICT,
    CONSTRAINT fk_member_officer  FOREIGN KEY (relationship_officer_id) REFERENCES users (id)         ON DELETE SET NULL,
    CONSTRAINT fk_member_intro    FOREIGN KEY (introduced_by_member_id) REFERENCES members (id)       ON DELETE SET NULL,
    CONSTRAINT chk_member_hh      CHECK (earning_members <= household_size)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE member_bank_accounts (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    member_id           BIGINT UNSIGNED NOT NULL,
    account_holder_name VARCHAR(160)    NOT NULL,
    account_no_enc      VARBINARY(255)  NOT NULL COMMENT 'AES-256-GCM',
    account_no_bidx     CHAR(64)        NOT NULL,
    account_no_last4    CHAR(4)         NOT NULL,
    ifsc                CHAR(11)        NOT NULL,
    bank_name           VARCHAR(120)    NOT NULL,
    branch_name         VARCHAR(120)        NULL,
    account_type        ENUM('savings','current','od','cc') NOT NULL DEFAULT 'savings',
    is_primary          TINYINT(1)      NOT NULL DEFAULT 0,
    penny_drop_status   ENUM('not_done','pending','verified','failed') NOT NULL DEFAULT 'not_done',
    penny_drop_ref      VARCHAR(64)         NULL,
    penny_drop_name     VARCHAR(160)        NULL COMMENT 'Name returned by the bank — compare against account_holder_name',
    verified_at         DATETIME(6)         NULL,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_mba (member_id, account_no_bidx, ifsc),
    KEY idx_mba_member (member_id, is_primary),
    CONSTRAINT fk_mba_member FOREIGN KEY (member_id) REFERENCES members (id) ON DELETE CASCADE,
    CONSTRAINT chk_ifsc CHECK (ifsc REGEXP '^[A-Z]{4}0[A-Z0-9]{6}$')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE member_nominees (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    member_id           BIGINT UNSIGNED NOT NULL,
    nominee_name        VARCHAR(160)    NOT NULL,
    relationship        VARCHAR(60)     NOT NULL,
    date_of_birth       DATE                NULL,
    share_percent       DECIMAL(5,2)    NOT NULL DEFAULT 100.00,
    guardian_name       VARCHAR(160)        NULL COMMENT 'Mandatory when the nominee is a minor',
    address             VARCHAR(400)        NULL,
    mobile              VARCHAR(15)         NULL,
    applies_to          ENUM('all','deposits','loans','shares') NOT NULL DEFAULT 'all',
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    KEY idx_nominee_member (member_id),
    CONSTRAINT fk_nominee_member FOREIGN KEY (member_id) REFERENCES members (id) ON DELETE CASCADE,
    CONSTRAINT chk_nominee_share CHECK (share_percent > 0 AND share_percent <= 100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE kyc_documents (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED    NOT NULL,
    member_id           BIGINT UNSIGNED     NULL,
    loan_application_id BIGINT UNSIGNED     NULL,
    doc_category        ENUM('poi','poa','income','photo','signature','property','group_resolution','other') NOT NULL,
    doc_type            VARCHAR(64)     NOT NULL COMMENT 'aadhaar, pan, voter_id, ration_card, itr, salary_slip, bank_statement...',
    doc_number_masked   VARCHAR(64)         NULL,
    file_path           VARCHAR(255)    NOT NULL COMMENT 'Stored outside webroot; served through an authorised controller only',
    file_hash           CHAR(64)        NOT NULL COMMENT 'SHA-256 — proves the artefact was never swapped post-upload',
    mime_type           VARCHAR(80)     NOT NULL,
    file_size_bytes     INT UNSIGNED    NOT NULL,
    issue_date          DATE                NULL,
    expiry_date         DATE                NULL,
    verification_status ENUM('pending','verified','rejected','expired') NOT NULL DEFAULT 'pending',
    verified_by         INT UNSIGNED        NULL,
    verified_at         DATETIME(6)         NULL,
    rejection_reason    VARCHAR(255)        NULL,
    ocr_extracted_json  JSON                NULL COMMENT 'Raw OCR/API payload for later re-validation',
    uploaded_by         INT UNSIGNED        NULL,
    uploaded_at         DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    KEY idx_kycdoc_member (member_id, doc_category, verification_status),
    KEY idx_kycdoc_appl (loan_application_id),
    KEY idx_kycdoc_expiry (org_id, expiry_date),
    CONSTRAINT fk_kycdoc_member FOREIGN KEY (member_id) REFERENCES members (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Microfinance group structure: CENTRE -> GROUP (JLG/SHG) -> MEMBER
-- ---------------------------------------------------------------------
CREATE TABLE centres (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED    NOT NULL,
    branch_id           INT UNSIGNED    NOT NULL,
    centre_code         VARCHAR(24)     NOT NULL,
    centre_name         VARCHAR(150)    NOT NULL,
    village             VARCHAR(120)        NULL,
    meeting_day         ENUM('mon','tue','wed','thu','fri','sat','sun') NOT NULL,
    meeting_time        TIME            NOT NULL,
    meeting_frequency   ENUM('weekly','fortnightly','monthly') NOT NULL DEFAULT 'weekly',
    meeting_latitude    DECIMAL(10,7)       NULL COMMENT 'Geo-fence anchor for collection attendance',
    meeting_longitude   DECIMAL(10,7)       NULL,
    geofence_radius_m   SMALLINT UNSIGNED NOT NULL DEFAULT 200,
    field_officer_id    INT UNSIGNED        NULL,
    formed_on           DATE                NULL,
    status              ENUM('active','dormant','closed') NOT NULL DEFAULT 'active',
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_centre_code (org_id, centre_code),
    KEY idx_centre_branch (branch_id, status),
    KEY idx_centre_officer (field_officer_id, meeting_day),
    CONSTRAINT fk_centre_branch  FOREIGN KEY (branch_id)        REFERENCES branches (id) ON DELETE RESTRICT,
    CONSTRAINT fk_centre_officer FOREIGN KEY (field_officer_id) REFERENCES users (id)    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE member_groups (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED    NOT NULL,
    branch_id           INT UNSIGNED    NOT NULL,
    centre_id           BIGINT UNSIGNED     NULL,
    group_code          VARCHAR(24)     NOT NULL,
    group_name          VARCHAR(150)    NOT NULL,
    group_type          ENUM('jlg','shg','federation') NOT NULL DEFAULT 'jlg',
    formation_date      DATE            NOT NULL,
    -- JLG norms: 4-10 members; SHG: 10-20
    min_members         TINYINT UNSIGNED NOT NULL DEFAULT 4,
    max_members         TINYINT UNSIGNED NOT NULL DEFAULT 10,
    leader_member_id    BIGINT UNSIGNED     NULL,
    secretary_member_id BIGINT UNSIGNED     NULL,
    -- SHG savings-led attributes
    group_savings_bal   BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Paise',
    group_bank_acct_id  BIGINT UNSIGNED     NULL,
    -- Group Recognition Test (GRT) / Compulsory Group Training (CGT)
    cgt_completed_on    DATE                NULL,
    grt_conducted_on    DATE                NULL,
    grt_conducted_by    INT UNSIGNED        NULL,
    grt_result          ENUM('pending','passed','failed') NOT NULL DEFAULT 'pending',
    joint_liability     TINYINT(1)      NOT NULL DEFAULT 1,
    grading             ENUM('A','B','C','D')  NULL COMMENT 'Group grading drives ticket-size eligibility',
    status              ENUM('forming','active','dormant','dissolved') NOT NULL DEFAULT 'forming',
    created_by          INT UNSIGNED        NULL,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_group_code (org_id, group_code),
    KEY idx_group_centre (centre_id, status),
    KEY idx_group_branch (branch_id, status),
    CONSTRAINT fk_group_branch FOREIGN KEY (branch_id) REFERENCES branches (id) ON DELETE RESTRICT,
    CONSTRAINT fk_group_centre FOREIGN KEY (centre_id) REFERENCES centres (id)  ON DELETE SET NULL,
    CONSTRAINT chk_group_size  CHECK (min_members >= 2 AND max_members >= min_members)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE group_members (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    group_id            BIGINT UNSIGNED NOT NULL,
    member_id           BIGINT UNSIGNED NOT NULL,
    role_in_group       ENUM('member','leader','secretary','treasurer') NOT NULL DEFAULT 'member',
    joined_on           DATE            NOT NULL,
    exited_on           DATE                NULL,
    exit_reason         VARCHAR(255)        NULL,
    status              ENUM('active','exited','suspended') NOT NULL DEFAULT 'active',
    PRIMARY KEY (id),
    UNIQUE KEY uq_group_member_active (group_id, member_id, joined_on),
    KEY idx_gm_member (member_id, status),
    CONSTRAINT fk_gm_group  FOREIGN KEY (group_id)  REFERENCES member_groups (id) ON DELETE CASCADE,
    CONSTRAINT fk_gm_member FOREIGN KEY (member_id) REFERENCES members (id)       ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 04. LOAN PRODUCTS
-- ---------------------------------------------------------------------
--  A product is a *rule pack*, not a row of loose columns: everything
--  the origination and servicing engines need to behave differently is
--  declared here so no product logic is ever hard-coded in PHP.
-- =====================================================================

CREATE TABLE loan_products (
    id                      INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    org_id                  INT UNSIGNED    NOT NULL,
    code                    VARCHAR(24)     NOT NULL,
    name                    VARCHAR(150)    NOT NULL,
    -- Which regulatory rule-pack governs this product
    regulatory_class        ENUM('nbfc_retail','nbfc_business','nidhi_gold','nidhi_property','nidhi_deposit_backed',
                                 'nidhi_term','mfi_jlg','mfi_shg','mfi_individual','gold','vehicle','msme','personal')
                                            NOT NULL,
    loan_category           ENUM('individual','group','joint') NOT NULL DEFAULT 'individual',
    -- ---- Interest ---------------------------------------------------
    interest_method         ENUM('reducing_balance','flat','rule_78','simple_daily','bullet') NOT NULL DEFAULT 'reducing_balance',
    interest_rate_bps       INT UNSIGNED    NOT NULL COMMENT 'Annual nominal rate in basis points. 1800 = 18.00% p.a.',
    min_rate_bps            INT UNSIGNED        NULL COMMENT 'Risk-based pricing floor',
    max_rate_bps            INT UNSIGNED        NULL COMMENT 'Risk-based pricing ceiling',
    rate_type               ENUM('fixed','floating') NOT NULL DEFAULT 'fixed',
    benchmark_code          VARCHAR(24)         NULL COMMENT 'Repo / MCLR / internal PLR for floating products',
    spread_bps              INT                 NULL,
    day_count_convention    ENUM('actual_365','actual_360','30_360','actual_actual') NOT NULL DEFAULT 'actual_365',
    -- ---- Amount & tenure -------------------------------------------
    min_principal           BIGINT UNSIGNED NOT NULL COMMENT 'Paise',
    max_principal           BIGINT UNSIGNED NOT NULL COMMENT 'Paise',
    principal_multiple_of   BIGINT UNSIGNED NOT NULL DEFAULT 100000 COMMENT 'Paise. 100000 = amounts must be multiples of Rs 1000',
    min_tenure              SMALLINT UNSIGNED NOT NULL COMMENT 'In repayment_frequency units',
    max_tenure              SMALLINT UNSIGNED NOT NULL,
    repayment_frequency     ENUM('daily','weekly','fortnightly','monthly','quarterly','half_yearly','yearly','bullet')
                                            NOT NULL DEFAULT 'monthly',
    moratorium_months       TINYINT UNSIGNED NOT NULL DEFAULT 0,
    moratorium_type         ENUM('none','principal_only','full_emi_holiday') NOT NULL DEFAULT 'none',
    grace_days              TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Days after due date before an instalment turns overdue',
    -- ---- Charges (RBI 2023: "penal CHARGES", not penal interest;
    --      they must not be capitalised and no further interest may be
    --      computed on them) ---------------------------------------
    processing_fee_bps      INT UNSIGNED    NOT NULL DEFAULT 0,
    processing_fee_flat     BIGINT UNSIGNED NOT NULL DEFAULT 0,
    processing_fee_min      BIGINT UNSIGNED NOT NULL DEFAULT 0,
    processing_fee_max      BIGINT UNSIGNED NOT NULL DEFAULT 0,
    documentation_fee       BIGINT UNSIGNED NOT NULL DEFAULT 0,
    insurance_required      TINYINT(1)      NOT NULL DEFAULT 0,
    insurance_premium_bps   INT UNSIGNED    NOT NULL DEFAULT 0,
    gst_applicable_on_fees  TINYINT(1)      NOT NULL DEFAULT 1,
    gst_rate_bps            INT UNSIGNED    NOT NULL DEFAULT 1800 COMMENT '18% GST on fee income',
    penal_charge_mode       ENUM('flat_per_instalment','percent_of_overdue','percent_per_annum','none') NOT NULL DEFAULT 'percent_per_annum',
    penal_charge_value      INT UNSIGNED    NOT NULL DEFAULT 0 COMMENT 'bps when percent-based, paise when flat',
    penal_charge_cap        BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Paise. 0 = uncapped',
    penal_compounding       TINYINT(1)      NOT NULL DEFAULT 0 COMMENT 'Must remain 0 for RBI-regulated retail lending',
    foreclosure_charge_bps  INT UNSIGNED    NOT NULL DEFAULT 0
                            COMMENT 'Must be 0 for floating-rate individual loans and all microfinance loans',
    part_payment_allowed    TINYINT(1)      NOT NULL DEFAULT 1,
    -- ---- Security / collateral --------------------------------------
    collateral_required     TINYINT(1)      NOT NULL DEFAULT 0,
    collateral_type         ENUM('none','gold','property','vehicle','deposit','shares','stock','guarantor') NOT NULL DEFAULT 'none',
    max_ltv_bps             INT UNSIGNED        NULL COMMENT 'RBI cap for gold loans by NBFCs: 7500 bps (75%)',
    guarantors_required     TINYINT UNSIGNED NOT NULL DEFAULT 0,
    -- ---- Eligibility / compliance gates -----------------------------
    min_age                 TINYINT UNSIGNED NOT NULL DEFAULT 21,
    max_age_at_maturity     TINYINT UNSIGNED NOT NULL DEFAULT 65,
    min_cibil_score         SMALLINT UNSIGNED   NULL,
    bureau_check_mandatory  TINYINT(1)      NOT NULL DEFAULT 1,
    max_household_income    BIGINT UNSIGNED     NULL COMMENT 'Paise. 30000000 (Rs 3L) for RBI-qualifying microfinance',
    max_flioi_bps           INT UNSIGNED        NULL
                            COMMENT 'Fixed Loan Instalment to Income ratio cap. RBI MFI 2022: 5000 bps (50% of monthly household income)',
    max_lender_count        TINYINT UNSIGNED    NULL COMMENT 'MFI multiple-lending guardrail',
    requires_nidhi_member   TINYINT(1)      NOT NULL DEFAULT 0 COMMENT 'Nidhi Rules: lending only to members',
    min_shares_required     INT UNSIGNED    NOT NULL DEFAULT 0,
    -- ---- Accounting mapping -----------------------------------------
    gl_principal_id         INT UNSIGNED        NULL,
    gl_interest_income_id   INT UNSIGNED        NULL,
    gl_interest_receivable_id INT UNSIGNED      NULL,
    gl_fee_income_id        INT UNSIGNED        NULL,
    gl_penal_income_id      INT UNSIGNED        NULL,
    gl_provision_id         INT UNSIGNED        NULL,
    -- ---- Workflow ----------------------------------------------------
    approval_levels         TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Maker-checker depth for sanction',
    auto_disburse           TINYINT(1)      NOT NULL DEFAULT 0,
    nach_mandatory          TINYINT(1)      NOT NULL DEFAULT 0,
    effective_from          DATE            NOT NULL,
    effective_to            DATE                NULL,
    is_active               TINYINT(1)      NOT NULL DEFAULT 1,
    created_by              INT UNSIGNED        NULL,
    created_at              DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at              DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_product_code (org_id, code),
    KEY idx_product_active (org_id, is_active, effective_from),
    KEY idx_product_class (org_id, regulatory_class),
    CONSTRAINT fk_product_org FOREIGN KEY (org_id) REFERENCES organisations (id) ON DELETE RESTRICT,
    CONSTRAINT chk_product_principal CHECK (max_principal >= min_principal),
    CONSTRAINT chk_product_tenure    CHECK (max_tenure >= min_tenure),
    CONSTRAINT chk_product_ltv       CHECK (max_ltv_bps IS NULL OR max_ltv_bps <= 10000)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Branch-level availability + local overrides on rate and ticket size
CREATE TABLE branch_product_config (
    branch_id           INT UNSIGNED    NOT NULL,
    product_id          INT UNSIGNED    NOT NULL,
    is_enabled          TINYINT(1)      NOT NULL DEFAULT 1,
    rate_override_bps   INT UNSIGNED        NULL,
    max_principal_override BIGINT UNSIGNED  NULL,
    PRIMARY KEY (branch_id, product_id),
    CONSTRAINT fk_bpc_branch  FOREIGN KEY (branch_id)  REFERENCES branches (id)      ON DELETE CASCADE,
    CONSTRAINT fk_bpc_product FOREIGN KEY (product_id) REFERENCES loan_products (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 05. LOAN ORIGINATION (LOS)
-- =====================================================================

CREATE TABLE loan_applications (
    id                      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id                  INT UNSIGNED    NOT NULL,
    branch_id               INT UNSIGNED    NOT NULL,
    application_no          VARCHAR(32)     NOT NULL,
    member_id               BIGINT UNSIGNED NOT NULL,
    group_id                BIGINT UNSIGNED     NULL COMMENT 'Set for JLG/SHG applications',
    product_id              INT UNSIGNED    NOT NULL,
    -- ---- Requested terms --------------------------------------------
    requested_principal     BIGINT UNSIGNED NOT NULL COMMENT 'Paise',
    requested_tenure        SMALLINT UNSIGNED NOT NULL,
    loan_purpose            VARCHAR(255)        NULL,
    purpose_category        ENUM('income_generation','consumption','education','medical','housing','refinance','other')
                                            NOT NULL DEFAULT 'income_generation'
                            COMMENT 'RBI MFI: at least 50% of the microfinance book must be income-generation',
    -- ---- Sanctioned terms (populated at approval) -------------------
    approved_principal      BIGINT UNSIGNED     NULL,
    approved_tenure         SMALLINT UNSIGNED   NULL,
    approved_rate_bps       INT UNSIGNED        NULL,
    approved_emi            BIGINT UNSIGNED     NULL,
    -- ---- Credit decisioning ------------------------------------------
    credit_score_internal   SMALLINT UNSIGNED   NULL COMMENT '0-1000 internal scorecard output',
    credit_grade            ENUM('A+','A','B','C','D','E')  NULL,
    bureau_score            SMALLINT UNSIGNED   NULL,
    bureau_name             VARCHAR(24)         NULL,
    existing_emi_obligation BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Paise/month, from bureau + declared',
    computed_flioi_bps      INT UNSIGNED        NULL COMMENT 'Fixed obligation to income ratio, computed at underwriting',
    decision_engine_json    JSON                NULL COMMENT 'Full rule-by-rule trace: which gates passed/failed and why',
    -- ---- Workflow ----------------------------------------------------
    status                  ENUM('draft','submitted','kyc_pending','bureau_pending','under_review','field_verification',
                                 'recommended','approved','rejected','cancelled','expired','disbursed','withdrawn')
                                            NOT NULL DEFAULT 'draft',
    current_approval_level  TINYINT UNSIGNED NOT NULL DEFAULT 0,
    rejection_reason_code   VARCHAR(48)         NULL,
    rejection_remarks       VARCHAR(500)        NULL,
    sourced_by              INT UNSIGNED        NULL COMMENT 'Loan officer who sourced the file',
    submitted_at            DATETIME(6)         NULL,
    approved_by             INT UNSIGNED        NULL,
    approved_at             DATETIME(6)         NULL,
    sanction_valid_until    DATE                NULL,
    -- ---- Field verification -------------------------------------------
    fi_status               ENUM('not_required','pending','positive','negative','refer') NOT NULL DEFAULT 'pending',
    fi_done_by              INT UNSIGNED        NULL,
    fi_done_at              DATETIME(6)         NULL,
    fi_latitude             DECIMAL(10,7)       NULL,
    fi_longitude            DECIMAL(10,7)       NULL,
    fi_remarks              VARCHAR(500)        NULL,
    -- ---- Source ---------------------------------------------------------
    channel                 ENUM('branch','field_app','web_portal','dsa','api','bc_agent') NOT NULL DEFAULT 'branch',
    created_by              INT UNSIGNED        NULL,
    created_at              DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at              DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_appl_no (org_id, application_no),
    KEY idx_appl_member (member_id, status),
    KEY idx_appl_branch_status (branch_id, status, created_at),
    KEY idx_appl_product (product_id, status),
    KEY idx_appl_group (group_id),
    KEY idx_appl_officer (sourced_by, status),
    CONSTRAINT fk_appl_org     FOREIGN KEY (org_id)     REFERENCES organisations (id) ON DELETE RESTRICT,
    CONSTRAINT fk_appl_branch  FOREIGN KEY (branch_id)  REFERENCES branches (id)      ON DELETE RESTRICT,
    CONSTRAINT fk_appl_member  FOREIGN KEY (member_id)  REFERENCES members (id)       ON DELETE RESTRICT,
    CONSTRAINT fk_appl_group   FOREIGN KEY (group_id)   REFERENCES member_groups (id) ON DELETE SET NULL,
    CONSTRAINT fk_appl_product FOREIGN KEY (product_id) REFERENCES loan_products (id) ON DELETE RESTRICT,
    CONSTRAINT chk_appl_amount CHECK (requested_principal > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Maker-checker trail. Every state transition of an application is a row
-- here; the application row only ever holds the *current* state.
CREATE TABLE loan_approvals (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    application_id      BIGINT UNSIGNED NOT NULL,
    approval_level      TINYINT UNSIGNED NOT NULL,
    actor_id            INT UNSIGNED    NOT NULL,
    actor_role_slug     VARCHAR(48)     NOT NULL,
    action              ENUM('recommend','approve','reject','refer_back','cancel','override') NOT NULL,
    from_status         VARCHAR(32)     NOT NULL,
    to_status           VARCHAR(32)     NOT NULL,
    sanctioned_amount   BIGINT UNSIGNED     NULL,
    sanctioned_rate_bps INT UNSIGNED        NULL,
    conditions          TEXT                NULL COMMENT 'Sanction conditions precedent',
    remarks             VARCHAR(1000)       NULL,
    ip_address          VARBINARY(16)       NULL,
    acted_at            DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    KEY idx_appr_appl (application_id, approval_level),
    KEY idx_appr_actor (actor_id, acted_at),
    CONSTRAINT fk_appr_appl  FOREIGN KEY (application_id) REFERENCES loan_applications (id) ON DELETE CASCADE,
    CONSTRAINT fk_appr_actor FOREIGN KEY (actor_id)       REFERENCES users (id)             ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 06. CREDIT BUREAU
-- ---------------------------------------------------------------------
--  RBI requires membership of and reporting to ALL FOUR CICs. Reports
--  are cached: re-pulling the same PAN/Aadhaar within BUREAU_CACHE_DAYS
--  returns the stored copy, which both saves the per-hit fee and keeps
--  the enquiry count on the customer's own report low.
-- =====================================================================

CREATE TABLE bureau_checks (
    id                      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id                  INT UNSIGNED    NOT NULL,
    member_id               BIGINT UNSIGNED NOT NULL,
    application_id          BIGINT UNSIGNED     NULL,
    bureau                  ENUM('cibil','crif_highmark','equifax','experian') NOT NULL,
    enquiry_purpose         ENUM('new_loan','review','collection','pre_approved','portfolio_monitoring') NOT NULL DEFAULT 'new_loan',
    enquiry_type            ENUM('individual','microfinance','commercial') NOT NULL DEFAULT 'individual',
    request_reference       VARCHAR(64)     NOT NULL COMMENT 'Our idempotency key sent to the bureau',
    bureau_reference        VARCHAR(64)         NULL COMMENT 'Control number returned by the bureau',
    -- ---- Outcome -------------------------------------------------------
    status                  ENUM('queued','sent','success','no_hit','failed','timeout','cached') NOT NULL DEFAULT 'queued',
    score                   SMALLINT UNSIGNED   NULL COMMENT 'CIBIL 300-900; -1/NA mapped to NULL',
    score_band              ENUM('no_history','poor','fair','good','very_good','excellent') NULL,
    score_reason_codes      VARCHAR(255)        NULL,
    -- ---- Derived aggregates used by the scorecard ---------------------
    total_accounts          SMALLINT UNSIGNED   NULL,
    active_accounts         SMALLINT UNSIGNED   NULL,
    total_outstanding       BIGINT UNSIGNED     NULL COMMENT 'Paise',
    total_emi_obligation    BIGINT UNSIGNED     NULL COMMENT 'Paise/month',
    overdue_accounts        SMALLINT UNSIGNED   NULL,
    total_overdue_amount    BIGINT UNSIGNED     NULL,
    max_dpd_24m             SMALLINT UNSIGNED   NULL,
    write_off_count         SMALLINT UNSIGNED   NULL,
    settled_count           SMALLINT UNSIGNED   NULL,
    suit_filed_count        SMALLINT UNSIGNED   NULL,
    enquiries_last_90d      SMALLINT UNSIGNED   NULL,
    -- MFI-specific aggregates (CRIF High Mark microfinance enquiry)
    mfi_lender_count        TINYINT UNSIGNED    NULL COMMENT 'Distinct NBFC-MFIs currently lending to this borrower',
    mfi_total_outstanding   BIGINT UNSIGNED     NULL,
    -- ---- Raw payloads (encrypted — a bureau report is regulated PII) --
    request_payload_enc     BLOB                NULL,
    response_payload_enc    LONGBLOB            NULL,
    response_hash           CHAR(64)            NULL,
    pdf_path                VARCHAR(255)        NULL,
    -- ---- Ops ------------------------------------------------------------
    http_status             SMALLINT UNSIGNED   NULL,
    error_code              VARCHAR(48)         NULL,
    error_message           VARCHAR(500)        NULL,
    latency_ms              INT UNSIGNED        NULL,
    cost_paise              INT UNSIGNED    NOT NULL DEFAULT 0 COMMENT 'Per-hit price, for vendor cost reconciliation',
    valid_until             DATETIME(6)         NULL COMMENT 'Cache horizon; a hit inside this window is reused',
    requested_by            INT UNSIGNED        NULL,
    requested_at            DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    responded_at            DATETIME(6)         NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_bureau_reqref (org_id, bureau, request_reference),
    KEY idx_bureau_member (member_id, bureau, requested_at),
    KEY idx_bureau_appl (application_id, status),
    KEY idx_bureau_cache (member_id, bureau, valid_until),
    CONSTRAINT fk_bureau_member FOREIGN KEY (member_id)      REFERENCES members (id)           ON DELETE CASCADE,
    CONSTRAINT fk_bureau_appl   FOREIGN KEY (application_id) REFERENCES loan_applications (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Internal scorecard: rules are data, so credit policy changes without
-- a code release and every past decision remains explainable.
CREATE TABLE credit_scorecards (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED    NOT NULL,
    product_id          INT UNSIGNED        NULL COMMENT 'NULL = applies to all products',
    version             VARCHAR(16)     NOT NULL,
    name                VARCHAR(120)    NOT NULL,
    rules_json          JSON            NOT NULL COMMENT '[{code,label,source,operator,value,weight,knockout}]',
    cutoff_approve      SMALLINT UNSIGNED NOT NULL,
    cutoff_refer        SMALLINT UNSIGNED NOT NULL,
    effective_from      DATE            NOT NULL,
    effective_to        DATE                NULL,
    is_active           TINYINT(1)      NOT NULL DEFAULT 1,
    created_by          INT UNSIGNED        NULL,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_scorecard (org_id, name, version),
    KEY idx_scorecard_active (org_id, is_active, effective_from)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE credit_assessments (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    application_id      BIGINT UNSIGNED NOT NULL,
    scorecard_id        INT UNSIGNED        NULL,
    scorecard_version   VARCHAR(16)         NULL,
    total_score         SMALLINT UNSIGNED NOT NULL,
    grade               ENUM('A+','A','B','C','D','E') NOT NULL,
    recommendation      ENUM('approve','refer','decline') NOT NULL,
    knockout_triggered  VARCHAR(64)         NULL COMMENT 'Rule code that hard-declined the file, if any',
    rule_results_json   JSON            NOT NULL COMMENT 'Per-rule score contribution — the audit answer to "why was I declined?"',
    -- Bank statement analyser output
    bsa_provider        VARCHAR(32)         NULL,
    bsa_avg_balance     BIGINT              NULL COMMENT 'Paise; signed — overdrawn accounts go negative',
    bsa_total_credits   BIGINT UNSIGNED     NULL,
    bsa_total_debits    BIGINT UNSIGNED     NULL,
    bsa_bounce_count    SMALLINT UNSIGNED   NULL,
    bsa_salary_detected TINYINT(1)          NULL,
    bsa_months_analysed TINYINT UNSIGNED    NULL,
    bsa_raw_json        JSON                NULL,
    assessed_at         DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    KEY idx_ca_appl (application_id, assessed_at),
    CONSTRAINT fk_ca_appl FOREIGN KEY (application_id) REFERENCES loan_applications (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 07. LOAN MANAGEMENT (LMS) — ACCOUNTS, SCHEDULE, REPAYMENTS
-- =====================================================================

CREATE TABLE loan_accounts (
    id                      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id                  INT UNSIGNED    NOT NULL,
    branch_id               INT UNSIGNED    NOT NULL,
    loan_account_no         VARCHAR(32)     NOT NULL COMMENT '{BRANCH}/{PRODUCT}/{YY}/{SEQ}',
    application_id          BIGINT UNSIGNED NOT NULL,
    member_id               BIGINT UNSIGNED NOT NULL,
    group_id                BIGINT UNSIGNED     NULL,
    product_id              INT UNSIGNED    NOT NULL,
    -- ---- Frozen terms: the product may change later, this loan may not
    principal               BIGINT UNSIGNED NOT NULL COMMENT 'Paise. Sanctioned amount',
    interest_rate_bps       INT UNSIGNED    NOT NULL,
    interest_method         ENUM('reducing_balance','flat','rule_78','simple_daily','bullet') NOT NULL,
    tenure                  SMALLINT UNSIGNED NOT NULL,
    repayment_frequency     ENUM('daily','weekly','fortnightly','monthly','quarterly','half_yearly','yearly','bullet') NOT NULL,
    emi_amount              BIGINT UNSIGNED NOT NULL COMMENT 'Paise. Level instalment; last one absorbs rounding',
    day_count_convention    ENUM('actual_365','actual_360','30_360','actual_actual') NOT NULL DEFAULT 'actual_365',
    grace_days              TINYINT UNSIGNED NOT NULL DEFAULT 0,
    penal_charge_mode       ENUM('flat_per_instalment','percent_of_overdue','percent_per_annum','none') NOT NULL DEFAULT 'percent_per_annum',
    penal_charge_value      INT UNSIGNED    NOT NULL DEFAULT 0,
    -- ---- Dates -----------------------------------------------------------
    sanction_date           DATE            NOT NULL,
    disbursement_date       DATE                NULL,
    first_due_date          DATE                NULL,
    maturity_date           DATE                NULL,
    closure_date            DATE                NULL,
    -- ---- Running balances. Denormalised on purpose: recomputing these
    --      from the ledger on every list screen does not survive a real
    --      portfolio. They are re-proved against the ledger nightly by
    --      the EOD reconciliation job.
    disbursed_amount        BIGINT UNSIGNED NOT NULL DEFAULT 0,
    principal_outstanding   BIGINT UNSIGNED NOT NULL DEFAULT 0,
    principal_paid          BIGINT UNSIGNED NOT NULL DEFAULT 0,
    interest_accrued        BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Accrual basis: earned but not yet received',
    interest_paid           BIGINT UNSIGNED NOT NULL DEFAULT 0,
    interest_waived         BIGINT UNSIGNED NOT NULL DEFAULT 0,
    penal_charged           BIGINT UNSIGNED NOT NULL DEFAULT 0,
    penal_paid              BIGINT UNSIGNED NOT NULL DEFAULT 0,
    penal_waived            BIGINT UNSIGNED NOT NULL DEFAULT 0,
    fees_charged            BIGINT UNSIGNED NOT NULL DEFAULT 0,
    fees_paid               BIGINT UNSIGNED NOT NULL DEFAULT 0,
    total_repaid            BIGINT UNSIGNED NOT NULL DEFAULT 0,
    excess_credit           BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Unapplied advance sitting on the account',
    -- ---- Delinquency / asset classification ------------------------------
    overdue_principal       BIGINT UNSIGNED NOT NULL DEFAULT 0,
    overdue_interest        BIGINT UNSIGNED NOT NULL DEFAULT 0,
    overdue_instalments     SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    dpd                     SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Days past due of the OLDEST unpaid instalment',
    max_dpd_ever            SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    oldest_overdue_date     DATE                NULL,
    asset_classification    ENUM('standard','smaA1','smaA2','sma1','sma2','substandard','doubtful1','doubtful2','doubtful3','loss')
                                            NOT NULL DEFAULT 'standard',
    npa_flag                TINYINT(1)      NOT NULL DEFAULT 0,
    npa_date                DATE                NULL,
    npa_reason              VARCHAR(120)        NULL,
    provision_rate_bps      INT UNSIGNED    NOT NULL DEFAULT 0,
    provision_amount        BIGINT UNSIGNED NOT NULL DEFAULT 0,
    -- RBI 12 Nov 2021 clarification: an NPA may be upgraded to standard
    -- only when the ENTIRE arrears of interest AND principal are cleared.
    upgraded_from_npa_on    DATE                NULL,
    restructured_flag       TINYINT(1)      NOT NULL DEFAULT 0,
    restructure_count       TINYINT UNSIGNED NOT NULL DEFAULT 0,
    write_off_date          DATE                NULL,
    write_off_amount        BIGINT UNSIGNED NOT NULL DEFAULT 0,
    -- ---- Servicing --------------------------------------------------------
    collection_mode         ENUM('cash','nach','upi_autopay','standing_instruction','cheque','pdc') NOT NULL DEFAULT 'cash',
    nach_mandate_id         BIGINT UNSIGNED     NULL,
    collection_officer_id   INT UNSIGNED        NULL,
    centre_id               BIGINT UNSIGNED     NULL,
    status                  ENUM('pending_disbursal','active','overdue','npa','closed','foreclosed','written_off','cancelled')
                                            NOT NULL DEFAULT 'pending_disbursal',
    -- ---- Accounting linkage -----------------------------------------------
    last_accrual_date       DATE                NULL COMMENT 'Guards against double-accruing interest on re-run',
    created_by              INT UNSIGNED        NULL,
    created_at              DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at              DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    version                 INT UNSIGNED    NOT NULL DEFAULT 0 COMMENT 'Optimistic-lock counter for concurrent posting',
    PRIMARY KEY (id),
    UNIQUE KEY uq_loan_no (org_id, loan_account_no),
    UNIQUE KEY uq_loan_appl (application_id),
    KEY idx_loan_member (member_id, status),
    KEY idx_loan_branch_status (branch_id, status, dpd),
    KEY idx_loan_npa (org_id, npa_flag, asset_classification),
    KEY idx_loan_officer (collection_officer_id, status),
    KEY idx_loan_centre (centre_id, status),
    KEY idx_loan_dpd (org_id, dpd, principal_outstanding),
    KEY idx_loan_accrual (org_id, status, last_accrual_date),
    CONSTRAINT fk_loan_org     FOREIGN KEY (org_id)         REFERENCES organisations (id)     ON DELETE RESTRICT,
    CONSTRAINT fk_loan_branch  FOREIGN KEY (branch_id)      REFERENCES branches (id)          ON DELETE RESTRICT,
    CONSTRAINT fk_loan_appl    FOREIGN KEY (application_id) REFERENCES loan_applications (id) ON DELETE RESTRICT,
    CONSTRAINT fk_loan_member  FOREIGN KEY (member_id)      REFERENCES members (id)           ON DELETE RESTRICT,
    CONSTRAINT fk_loan_product FOREIGN KEY (product_id)     REFERENCES loan_products (id)     ON DELETE RESTRICT,
    CONSTRAINT fk_loan_centre  FOREIGN KEY (centre_id)      REFERENCES centres (id)           ON DELETE SET NULL,
    CONSTRAINT chk_loan_principal CHECK (principal > 0),
    CONSTRAINT chk_loan_paid      CHECK (principal_paid <= principal + 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE loan_disbursements (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    loan_account_id     BIGINT UNSIGNED NOT NULL,
    tranche_no          TINYINT UNSIGNED NOT NULL DEFAULT 1,
    gross_amount        BIGINT UNSIGNED NOT NULL COMMENT 'Paise. Sanctioned tranche',
    deductions_json     JSON                NULL COMMENT '[{code,label,amount}] — PF, insurance, GST, first EMI, old-loan closure',
    total_deductions    BIGINT UNSIGNED NOT NULL DEFAULT 0,
    net_amount          BIGINT UNSIGNED NOT NULL COMMENT 'Actually paid out to the borrower',
    disbursement_date   DATE            NOT NULL,
    mode                ENUM('neft','rtgs','imps','upi','cheque','dd','cash','wallet') NOT NULL,
    bank_account_id     BIGINT UNSIGNED     NULL,
    utr_number          VARCHAR(40)         NULL,
    cheque_no           VARCHAR(20)         NULL,
    status              ENUM('pending','processing','success','failed','reversed') NOT NULL DEFAULT 'pending',
    failure_reason      VARCHAR(255)        NULL,
    voucher_id          BIGINT UNSIGNED     NULL,
    authorised_by       INT UNSIGNED        NULL,
    disbursed_by        INT UNSIGNED        NULL,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_disb_tranche (loan_account_id, tranche_no),
    KEY idx_disb_date (disbursement_date, status),
    KEY idx_disb_utr (utr_number),
    CONSTRAINT fk_disb_loan FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id) ON DELETE RESTRICT,
    CONSTRAINT chk_disb_net CHECK (net_amount <= gross_amount)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- The amortisation schedule. One row per instalment. Generated once at
-- disbursal and regenerated (with a new version) only on restructure,
-- part-prepayment or rate reset — never edited in place.
-- ---------------------------------------------------------------------
CREATE TABLE loan_schedules (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    loan_account_id     BIGINT UNSIGNED NOT NULL,
    schedule_version    SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    instalment_no       SMALLINT UNSIGNED NOT NULL,
    due_date            DATE            NOT NULL,
    -- ---- Demand -------------------------------------------------------
    opening_principal   BIGINT UNSIGNED NOT NULL,
    principal_due       BIGINT UNSIGNED NOT NULL DEFAULT 0,
    interest_due        BIGINT UNSIGNED NOT NULL DEFAULT 0,
    fee_due             BIGINT UNSIGNED NOT NULL DEFAULT 0,
    insurance_due       BIGINT UNSIGNED NOT NULL DEFAULT 0,
    total_due           BIGINT UNSIGNED NOT NULL,
    closing_principal   BIGINT UNSIGNED NOT NULL,
    -- ---- Collection ----------------------------------------------------
    principal_paid      BIGINT UNSIGNED NOT NULL DEFAULT 0,
    interest_paid       BIGINT UNSIGNED NOT NULL DEFAULT 0,
    fee_paid            BIGINT UNSIGNED NOT NULL DEFAULT 0,
    insurance_paid      BIGINT UNSIGNED NOT NULL DEFAULT 0,
    penal_due           BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Accrued penal charges on THIS instalment',
    penal_paid          BIGINT UNSIGNED NOT NULL DEFAULT 0,
    waived_amount       BIGINT UNSIGNED NOT NULL DEFAULT 0,
    total_paid          BIGINT UNSIGNED NOT NULL DEFAULT 0,
    last_paid_date      DATE                NULL,
    penal_calc_upto     DATE                NULL COMMENT 'Idempotency guard for the daily penal-accrual job',
    -- ---- State ----------------------------------------------------------
    status              ENUM('pending','partial','paid','overdue','waived','rescheduled','written_off') NOT NULL DEFAULT 'pending',
    days_past_due       SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    is_moratorium       TINYINT(1)      NOT NULL DEFAULT 0,
    is_active_version   TINYINT(1)      NOT NULL DEFAULT 1,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_sched (loan_account_id, schedule_version, instalment_no),
    -- The workhorse index: "give me every unpaid instalment due on/before
    -- date X, oldest first" is the hot path for allocation, DPD, NPA,
    -- demand sheets and reminder jobs alike.
    KEY idx_sched_due (loan_account_id, is_active_version, status, due_date),
    KEY idx_sched_duedate_global (due_date, status),
    CONSTRAINT fk_sched_loan FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id) ON DELETE CASCADE,
    CONSTRAINT chk_sched_total CHECK (total_due = principal_due + interest_due + fee_due + insurance_due)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Money actually received. APPEND-ONLY: a mistake is corrected by a
-- reversal row that points at the original, never by an UPDATE.
-- ---------------------------------------------------------------------
CREATE TABLE loan_repayments (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED    NOT NULL,
    branch_id           INT UNSIGNED    NOT NULL,
    loan_account_id     BIGINT UNSIGNED NOT NULL,
    receipt_no          VARCHAR(32)     NOT NULL COMMENT 'Pre-printed/sequential; shown on the borrower receipt',
    -- Idempotency key. A retried field-app sync, a duplicated NACH file
    -- or a double-clicked cashier form all collapse onto one row.
    idempotency_key     VARCHAR(64)     NOT NULL,
    value_date          DATE            NOT NULL COMMENT 'Date the money is treated as received (drives interest)',
    posting_date        DATE            NOT NULL COMMENT 'Business day it hit the books — may differ from value_date',
    amount              BIGINT UNSIGNED NOT NULL COMMENT 'Paise. Gross amount received',
    -- ---- Component split, denormalised from repayment_allocations for
    --      fast receipts and reporting. Guaranteed to sum to `amount`.
    principal_component BIGINT UNSIGNED NOT NULL DEFAULT 0,
    interest_component  BIGINT UNSIGNED NOT NULL DEFAULT 0,
    penal_component     BIGINT UNSIGNED NOT NULL DEFAULT 0,
    fee_component       BIGINT UNSIGNED NOT NULL DEFAULT 0,
    insurance_component BIGINT UNSIGNED NOT NULL DEFAULT 0,
    advance_component   BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Surplus parked as excess_credit',
    -- ---- Instrument -------------------------------------------------------
    payment_mode        ENUM('cash','upi','neft','imps','rtgs','nach','cheque','dd','card','wallet','adjustment','write_off')
                                        NOT NULL,
    transaction_type    ENUM('regular','advance','part_prepayment','foreclosure','settlement','recovery_post_writeoff','reversal')
                                        NOT NULL DEFAULT 'regular',
    reference_no        VARCHAR(64)         NULL COMMENT 'UTR / UPI RRN / cheque no / NACH item ref',
    bank_name           VARCHAR(120)        NULL,
    cheque_date         DATE                NULL,
    -- ---- Field collection provenance ---------------------------------------
    collected_by        INT UNSIGNED        NULL,
    collection_sheet_id BIGINT UNSIGNED     NULL,
    channel             ENUM('branch_counter','field_app','nach_auto','payment_gateway','portal','api','bulk_upload') NOT NULL DEFAULT 'branch_counter',
    latitude            DECIMAL(10,7)       NULL,
    longitude           DECIMAL(10,7)       NULL,
    device_id           VARCHAR(64)         NULL,
    -- ---- Reversal linkage ----------------------------------------------------
    status              ENUM('pending','cleared','bounced','reversed','cancelled') NOT NULL DEFAULT 'cleared',
    reversal_of_id      BIGINT UNSIGNED     NULL COMMENT 'Set on the contra row; points at the row being undone',
    reversed_by_id      BIGINT UNSIGNED     NULL COMMENT 'Set on the original; points at its contra row',
    reversal_reason     VARCHAR(255)        NULL,
    bounce_reason       VARCHAR(255)        NULL,
    bounce_charge       BIGINT UNSIGNED NOT NULL DEFAULT 0,
    -- ---- Accounting ----------------------------------------------------------
    voucher_id          BIGINT UNSIGNED     NULL COMMENT 'Journal voucher created by this posting',
    receipt_pdf_path    VARCHAR(255)        NULL,
    remarks             VARCHAR(500)        NULL,
    created_by          INT UNSIGNED        NULL,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_repay_receipt (org_id, receipt_no),
    UNIQUE KEY uq_repay_idem (org_id, idempotency_key),
    KEY idx_repay_loan (loan_account_id, value_date, status),
    KEY idx_repay_branch_date (branch_id, posting_date, payment_mode),
    KEY idx_repay_collector (collected_by, posting_date),
    KEY idx_repay_sheet (collection_sheet_id),
    KEY idx_repay_ref (reference_no),
    KEY idx_repay_reversal (reversal_of_id),
    CONSTRAINT fk_repay_loan      FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id) ON DELETE RESTRICT,
    CONSTRAINT fk_repay_branch    FOREIGN KEY (branch_id)       REFERENCES branches (id)      ON DELETE RESTRICT,
    CONSTRAINT fk_repay_collector FOREIGN KEY (collected_by)    REFERENCES users (id)         ON DELETE SET NULL,
    CONSTRAINT fk_repay_reversal  FOREIGN KEY (reversal_of_id)  REFERENCES loan_repayments (id) ON DELETE RESTRICT,
    CONSTRAINT chk_repay_amount   CHECK (amount > 0),
    CONSTRAINT chk_repay_split    CHECK (amount = principal_component + interest_component + penal_component
                                                + fee_component + insurance_component + advance_component)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Which receipt paid which instalment, component by component. This is
-- what makes "explain this borrower's ledger line by line" answerable.
CREATE TABLE repayment_allocations (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    repayment_id        BIGINT UNSIGNED NOT NULL,
    schedule_id         BIGINT UNSIGNED     NULL COMMENT 'NULL when the money went to advance/excess credit',
    loan_account_id     BIGINT UNSIGNED NOT NULL,
    instalment_no       SMALLINT UNSIGNED   NULL,
    component           ENUM('penal','fee','insurance','interest','principal','advance') NOT NULL,
    amount              BIGINT UNSIGNED NOT NULL,
    allocation_order    TINYINT UNSIGNED NOT NULL COMMENT 'Waterfall sequence actually applied',
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    KEY idx_alloc_repay (repayment_id),
    KEY idx_alloc_sched (schedule_id, component),
    KEY idx_alloc_loan (loan_account_id, created_at),
    CONSTRAINT fk_alloc_repay FOREIGN KEY (repayment_id) REFERENCES loan_repayments (id) ON DELETE RESTRICT,
    CONSTRAINT fk_alloc_sched FOREIGN KEY (schedule_id)  REFERENCES loan_schedules (id)  ON DELETE RESTRICT,
    CONSTRAINT chk_alloc_amount CHECK (amount > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Non-instalment charges levied on a loan (bounce, legal, valuation...)
CREATE TABLE loan_charges (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    loan_account_id     BIGINT UNSIGNED NOT NULL,
    charge_code         VARCHAR(32)     NOT NULL,
    charge_label        VARCHAR(150)    NOT NULL,
    charge_type         ENUM('processing','documentation','insurance','penal','bounce','legal','valuation','foreclosure','other') NOT NULL,
    base_amount         BIGINT UNSIGNED NOT NULL,
    gst_amount          BIGINT UNSIGNED NOT NULL DEFAULT 0,
    total_amount        BIGINT UNSIGNED NOT NULL,
    paid_amount         BIGINT UNSIGNED NOT NULL DEFAULT 0,
    waived_amount       BIGINT UNSIGNED NOT NULL DEFAULT 0,
    charge_date         DATE            NOT NULL,
    status              ENUM('pending','paid','partial','waived','reversed') NOT NULL DEFAULT 'pending',
    waived_by           INT UNSIGNED        NULL,
    waiver_reason       VARCHAR(255)        NULL,
    voucher_id          BIGINT UNSIGNED     NULL,
    created_by          INT UNSIGNED        NULL,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    KEY idx_charge_loan (loan_account_id, status),
    KEY idx_charge_date (charge_date, charge_type),
    CONSTRAINT fk_charge_loan FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Asset-classification history — the auditor's first question is always
-- "when did this slip, and who moved it back?"
CREATE TABLE npa_history (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    loan_account_id     BIGINT UNSIGNED NOT NULL,
    as_on_date          DATE            NOT NULL,
    from_classification VARCHAR(16)     NOT NULL,
    to_classification   VARCHAR(16)     NOT NULL,
    dpd                 SMALLINT UNSIGNED NOT NULL,
    principal_outstanding BIGINT UNSIGNED NOT NULL,
    provision_rate_bps  INT UNSIGNED    NOT NULL,
    provision_amount    BIGINT UNSIGNED NOT NULL,
    movement            ENUM('downgrade','upgrade','no_change','write_off','recovery') NOT NULL,
    triggered_by        ENUM('eod_job','manual','restructure','settlement') NOT NULL DEFAULT 'eod_job',
    actioned_by         INT UNSIGNED        NULL,
    remarks             VARCHAR(500)        NULL,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_npa_daily (loan_account_id, as_on_date),
    KEY idx_npa_date (as_on_date, to_classification),
    CONSTRAINT fk_npa_loan FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE loan_collaterals (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    loan_account_id     BIGINT UNSIGNED     NULL,
    application_id      BIGINT UNSIGNED     NULL,
    collateral_type     ENUM('gold','property','vehicle','deposit','shares','stock','machinery','other') NOT NULL,
    description         VARCHAR(500)    NOT NULL,
    -- Gold specifics (Nidhi / gold-loan NBFCs)
    gold_gross_weight_mg INT UNSIGNED       NULL COMMENT 'Milligrams — integers only, no float drift',
    gold_net_weight_mg  INT UNSIGNED        NULL,
    gold_purity_carat   DECIMAL(4,2)        NULL,
    gold_item_count     TINYINT UNSIGNED    NULL,
    gold_rate_per_gram  BIGINT UNSIGNED     NULL COMMENT 'Paise. Board-approved rate on the appraisal date',
    appraiser_name      VARCHAR(150)        NULL,
    -- Property / vehicle specifics
    property_survey_no  VARCHAR(64)         NULL,
    vehicle_reg_no      VARCHAR(20)         NULL,
    vehicle_engine_no   VARCHAR(40)         NULL,
    vehicle_chassis_no  VARCHAR(40)         NULL,
    -- Valuation
    market_value        BIGINT UNSIGNED NOT NULL,
    realisable_value    BIGINT UNSIGNED NOT NULL,
    ltv_bps             INT UNSIGNED        NULL COMMENT 'Computed: principal / realisable_value',
    valuation_date      DATE            NOT NULL,
    valued_by           VARCHAR(150)        NULL,
    next_revaluation_due DATE               NULL,
    -- Custody
    storage_location    VARCHAR(150)        NULL,
    packet_no           VARCHAR(40)         NULL,
    insurance_policy_no VARCHAR(64)         NULL,
    insurance_expiry    DATE                NULL,
    cersai_asset_id     VARCHAR(40)         NULL COMMENT 'CERSAI security-interest registration',
    status              ENUM('pledged','partially_released','released','auctioned','seized','lost') NOT NULL DEFAULT 'pledged',
    released_on         DATE                NULL,
    released_by         INT UNSIGNED        NULL,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    KEY idx_coll_loan (loan_account_id, status),
    KEY idx_coll_appl (application_id),
    KEY idx_coll_reval (next_revaluation_due, status),
    CONSTRAINT fk_coll_loan FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE loan_guarantors (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    loan_account_id     BIGINT UNSIGNED     NULL,
    application_id      BIGINT UNSIGNED     NULL,
    guarantor_member_id BIGINT UNSIGNED     NULL COMMENT 'Set when the guarantor is also a member',
    name                VARCHAR(160)    NOT NULL,
    relationship        VARCHAR(60)         NULL,
    mobile              VARCHAR(15)         NULL,
    pan_last4           CHAR(4)             NULL,
    address             VARCHAR(400)        NULL,
    guaranteed_amount   BIGINT UNSIGNED NOT NULL DEFAULT 0,
    liability_type      ENUM('joint_and_several','limited') NOT NULL DEFAULT 'joint_and_several',
    consent_captured_at DATETIME(6)         NULL,
    status              ENUM('active','released','invoked') NOT NULL DEFAULT 'active',
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    KEY idx_guar_loan (loan_account_id, status),
    KEY idx_guar_member (guarantor_member_id),
    CONSTRAINT fk_guar_loan FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 08. COLLECTIONS — FIELD SHEETS, NACH / e-MANDATE
-- =====================================================================

CREATE TABLE collection_sheets (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED    NOT NULL,
    branch_id           INT UNSIGNED    NOT NULL,
    sheet_no            VARCHAR(32)     NOT NULL,
    collection_date     DATE            NOT NULL,
    centre_id           BIGINT UNSIGNED     NULL,
    officer_id          INT UNSIGNED    NOT NULL,
    sheet_type          ENUM('centre_meeting','door_to_door','branch_counter','recovery_drive') NOT NULL DEFAULT 'centre_meeting',
    -- Demand (frozen when the sheet is generated, so the field copy and
    -- the office copy can never silently diverge)
    expected_accounts   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    expected_amount     BIGINT UNSIGNED NOT NULL DEFAULT 0,
    collected_accounts  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    collected_amount    BIGINT UNSIGNED NOT NULL DEFAULT 0,
    cash_amount         BIGINT UNSIGNED NOT NULL DEFAULT 0,
    digital_amount      BIGINT UNSIGNED NOT NULL DEFAULT 0,
    -- Cash accountability
    cash_remitted       BIGINT UNSIGNED NOT NULL DEFAULT 0,
    remittance_ref      VARCHAR(64)         NULL,
    remitted_at         DATETIME(6)         NULL,
    variance_amount     BIGINT          NOT NULL DEFAULT 0 COMMENT 'Signed. collected_cash - remitted; must be 0 to close',
    -- Meeting proof
    attendance_count    SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    meeting_photo_path  VARCHAR(255)        NULL,
    start_latitude      DECIMAL(10,7)       NULL,
    start_longitude     DECIMAL(10,7)       NULL,
    geofence_ok         TINYINT(1)          NULL,
    status              ENUM('generated','downloaded','in_progress','submitted','reconciled','closed','disputed') NOT NULL DEFAULT 'generated',
    synced_at           DATETIME(6)         NULL COMMENT 'When the offline field app pushed it back',
    reconciled_by       INT UNSIGNED        NULL,
    reconciled_at       DATETIME(6)         NULL,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_sheet_no (org_id, sheet_no),
    KEY idx_sheet_officer_date (officer_id, collection_date, status),
    KEY idx_sheet_branch_date (branch_id, collection_date),
    KEY idx_sheet_centre (centre_id, collection_date),
    CONSTRAINT fk_sheet_branch  FOREIGN KEY (branch_id)  REFERENCES branches (id) ON DELETE RESTRICT,
    CONSTRAINT fk_sheet_officer FOREIGN KEY (officer_id) REFERENCES users (id)    ON DELETE RESTRICT,
    CONSTRAINT fk_sheet_centre  FOREIGN KEY (centre_id)  REFERENCES centres (id)  ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE collection_sheet_lines (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    sheet_id            BIGINT UNSIGNED NOT NULL,
    loan_account_id     BIGINT UNSIGNED NOT NULL,
    member_id           BIGINT UNSIGNED NOT NULL,
    demand_amount       BIGINT UNSIGNED NOT NULL,
    overdue_amount      BIGINT UNSIGNED NOT NULL DEFAULT 0,
    penal_amount        BIGINT UNSIGNED NOT NULL DEFAULT 0,
    collected_amount    BIGINT UNSIGNED NOT NULL DEFAULT 0,
    repayment_id        BIGINT UNSIGNED     NULL,
    visit_status        ENUM('pending','paid','partial','not_paid','absent','refused','shifted','ptp') NOT NULL DEFAULT 'pending',
    ptp_date            DATE                NULL COMMENT 'Promise-to-pay captured in the field',
    non_payment_reason  VARCHAR(160)        NULL,
    remarks             VARCHAR(500)        NULL,
    captured_at         DATETIME(6)         NULL,
    latitude            DECIMAL(10,7)       NULL,
    longitude           DECIMAL(10,7)       NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_sheet_line (sheet_id, loan_account_id),
    KEY idx_csl_loan (loan_account_id),
    KEY idx_csl_ptp (ptp_date, visit_status),
    CONSTRAINT fk_csl_sheet FOREIGN KEY (sheet_id)        REFERENCES collection_sheets (id) ON DELETE CASCADE,
    CONSTRAINT fk_csl_loan  FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id)     ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE nach_mandates (
    id                      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id                  INT UNSIGNED    NOT NULL,
    member_id               BIGINT UNSIGNED NOT NULL,
    loan_account_id         BIGINT UNSIGNED     NULL,
    umrn                    VARCHAR(20)         NULL COMMENT 'Unique Mandate Reference Number issued by NPCI',
    mandate_reference       VARCHAR(40)     NOT NULL COMMENT 'Our own reference sent to the sponsor bank',
    mandate_type            ENUM('nach_debit','esign_nach','enach_netbanking','enach_debit_card','upi_autopay','si') NOT NULL,
    -- Bank details (encrypted)
    bank_account_id         BIGINT UNSIGNED     NULL,
    account_no_enc          VARBINARY(255)      NULL,
    account_no_last4        CHAR(4)             NULL,
    ifsc                    CHAR(11)        NOT NULL,
    bank_name               VARCHAR(120)    NOT NULL,
    account_type            ENUM('savings','current','cc','od','other') NOT NULL DEFAULT 'savings',
    -- Mandate terms
    max_amount              BIGINT UNSIGNED NOT NULL COMMENT 'Paise. Debit cap per presentation',
    frequency               ENUM('monthly','quarterly','half_yearly','yearly','as_and_when','weekly','daily') NOT NULL DEFAULT 'monthly',
    debit_day               TINYINT UNSIGNED    NULL COMMENT 'Day of month the sponsor bank presents',
    first_collection_date   DATE                NULL,
    valid_from              DATE            NOT NULL,
    valid_upto              DATE                NULL,
    until_cancelled         TINYINT(1)      NOT NULL DEFAULT 0,
    -- Lifecycle
    status                  ENUM('draft','pending_auth','submitted','active','rejected','cancelled','expired','suspended') NOT NULL DEFAULT 'draft',
    registration_date       DATE                NULL,
    rejection_code          VARCHAR(16)         NULL,
    rejection_reason        VARCHAR(255)        NULL,
    cancelled_at            DATETIME(6)         NULL,
    provider                VARCHAR(32)         NULL,
    provider_mandate_id     VARCHAR(64)         NULL,
    esign_doc_path          VARCHAR(255)        NULL,
    consent_ip              VARBINARY(16)       NULL,
    consent_at              DATETIME(6)         NULL,
    created_by              INT UNSIGNED        NULL,
    created_at              DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at              DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_nach_ref (org_id, mandate_reference),
    UNIQUE KEY uq_nach_umrn (umrn),
    KEY idx_nach_loan (loan_account_id, status),
    KEY idx_nach_member (member_id, status),
    KEY idx_nach_debitday (status, debit_day),
    CONSTRAINT fk_nach_member FOREIGN KEY (member_id)       REFERENCES members (id)       ON DELETE RESTRICT,
    CONSTRAINT fk_nach_loan   FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- One NPCI presentation batch (ACH DEBIT file) per sponsor bank per date
CREATE TABLE nach_batches (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED    NOT NULL,
    batch_reference     VARCHAR(40)     NOT NULL,
    presentation_date   DATE            NOT NULL COMMENT 'Settlement date at NPCI',
    generated_on        DATE            NOT NULL,
    sponsor_bank_ifsc   CHAR(11)        NOT NULL,
    utility_code        VARCHAR(20)     NOT NULL,
    total_items         INT UNSIGNED    NOT NULL DEFAULT 0,
    total_amount        BIGINT UNSIGNED NOT NULL DEFAULT 0,
    success_items       INT UNSIGNED    NOT NULL DEFAULT 0,
    success_amount      BIGINT UNSIGNED NOT NULL DEFAULT 0,
    failed_items        INT UNSIGNED    NOT NULL DEFAULT 0,
    failed_amount       BIGINT UNSIGNED NOT NULL DEFAULT 0,
    file_path           VARCHAR(255)        NULL,
    file_hash           CHAR(64)            NULL,
    response_file_path  VARCHAR(255)        NULL,
    status              ENUM('draft','generated','uploaded','presented','partially_settled','settled','failed') NOT NULL DEFAULT 'draft',
    generated_by        INT UNSIGNED        NULL,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_nach_batch (org_id, batch_reference),
    KEY idx_nachbatch_date (presentation_date, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE nach_presentations (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    batch_id            BIGINT UNSIGNED NOT NULL,
    mandate_id          BIGINT UNSIGNED NOT NULL,
    loan_account_id     BIGINT UNSIGNED NOT NULL,
    item_reference      VARCHAR(40)     NOT NULL,
    presentation_date   DATE            NOT NULL,
    amount              BIGINT UNSIGNED NOT NULL,
    status              ENUM('queued','presented','success','returned','cancelled') NOT NULL DEFAULT 'queued',
    settlement_date     DATE                NULL,
    return_code         VARCHAR(16)         NULL COMMENT 'NPCI return reason code, e.g. 02 = insufficient funds',
    return_reason       VARCHAR(255)        NULL,
    repayment_id        BIGINT UNSIGNED     NULL COMMENT 'Set once the credit is posted to the loan',
    bounce_charge_id    BIGINT UNSIGNED     NULL,
    retry_count         TINYINT UNSIGNED NOT NULL DEFAULT 0,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_nach_item (batch_id, item_reference),
    KEY idx_np_mandate (mandate_id, presentation_date),
    KEY idx_np_loan (loan_account_id, status),
    KEY idx_np_status (status, presentation_date),
    CONSTRAINT fk_np_batch   FOREIGN KEY (batch_id)        REFERENCES nach_batches (id)  ON DELETE CASCADE,
    CONSTRAINT fk_np_mandate FOREIGN KEY (mandate_id)      REFERENCES nach_mandates (id) ON DELETE RESTRICT,
    CONSTRAINT fk_np_loan    FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 09. NIDHI DEPOSITS & SHARE CAPITAL
-- ---------------------------------------------------------------------
--  Nidhi Rules 2014 constraints the engine enforces:
--   r.6(d)  no current accounts;  r.14  deposits <= 20x Net Owned Funds
--   r.13    FD 6-60 months, RD 12-60 months
--   r.15    interest on FD not > the max rate SCBs may pay
--   r.14(6) unencumbered term deposits >= 10% of outstanding deposits
-- =====================================================================

CREATE TABLE deposit_products (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED    NOT NULL,
    code                VARCHAR(24)     NOT NULL,
    name                VARCHAR(150)    NOT NULL,
    deposit_type        ENUM('savings','fixed','recurring','cumulative_fd','mis','pigmy','daily') NOT NULL,
    interest_rate_bps   INT UNSIGNED    NOT NULL,
    senior_citizen_bonus_bps INT UNSIGNED NOT NULL DEFAULT 0,
    compounding         ENUM('none','monthly','quarterly','half_yearly','yearly','on_maturity') NOT NULL DEFAULT 'quarterly',
    payout_frequency    ENUM('on_maturity','monthly','quarterly','half_yearly','yearly') NOT NULL DEFAULT 'on_maturity',
    min_amount          BIGINT UNSIGNED NOT NULL,
    max_amount          BIGINT UNSIGNED     NULL,
    min_tenure_months   SMALLINT UNSIGNED NOT NULL,
    max_tenure_months   SMALLINT UNSIGNED NOT NULL,
    premature_allowed   TINYINT(1)      NOT NULL DEFAULT 1,
    premature_penalty_bps INT UNSIGNED  NOT NULL DEFAULT 100,
    premature_lock_months TINYINT UNSIGNED NOT NULL DEFAULT 3,
    tds_applicable      TINYINT(1)      NOT NULL DEFAULT 1,
    tds_threshold       BIGINT UNSIGNED NOT NULL DEFAULT 4000000 COMMENT 'Paise. Sec 194A threshold',
    tds_rate_bps        INT UNSIGNED    NOT NULL DEFAULT 1000,
    loan_against_allowed TINYINT(1)     NOT NULL DEFAULT 1,
    max_loan_ltv_bps    INT UNSIGNED    NOT NULL DEFAULT 8000,
    gl_liability_id     INT UNSIGNED        NULL,
    gl_interest_expense_id INT UNSIGNED     NULL,
    is_active           TINYINT(1)      NOT NULL DEFAULT 1,
    effective_from      DATE            NOT NULL,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_depprod_code (org_id, code),
    KEY idx_depprod_active (org_id, is_active),
    CONSTRAINT chk_dep_tenure CHECK (max_tenure_months >= min_tenure_months)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE deposit_accounts (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED    NOT NULL,
    branch_id           INT UNSIGNED    NOT NULL,
    account_no          VARCHAR(32)     NOT NULL,
    member_id           BIGINT UNSIGNED NOT NULL,
    product_id          INT UNSIGNED    NOT NULL,
    deposit_type        ENUM('savings','fixed','recurring','cumulative_fd','mis','pigmy','daily') NOT NULL,
    principal_amount    BIGINT UNSIGNED NOT NULL DEFAULT 0,
    instalment_amount   BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'RD/pigmy periodic deposit',
    interest_rate_bps   INT UNSIGNED    NOT NULL,
    tenure_months       SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    open_date           DATE            NOT NULL,
    maturity_date       DATE                NULL,
    maturity_amount     BIGINT UNSIGNED NOT NULL DEFAULT 0,
    current_balance     BIGINT UNSIGNED NOT NULL DEFAULT 0,
    interest_accrued    BIGINT UNSIGNED NOT NULL DEFAULT 0,
    interest_paid       BIGINT UNSIGNED NOT NULL DEFAULT 0,
    tds_deducted        BIGINT UNSIGNED NOT NULL DEFAULT 0,
    last_interest_date  DATE                NULL,
    auto_renew          TINYINT(1)      NOT NULL DEFAULT 0,
    lien_amount         BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Marked when used as loan collateral',
    lien_loan_id        BIGINT UNSIGNED     NULL,
    nominee_id          BIGINT UNSIGNED     NULL,
    form15g_15h_on_file TINYINT(1)      NOT NULL DEFAULT 0,
    status              ENUM('active','matured','closed','prematurely_closed','dormant','lien_marked') NOT NULL DEFAULT 'active',
    closure_date        DATE                NULL,
    created_by          INT UNSIGNED        NULL,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_dep_acct (org_id, account_no),
    KEY idx_dep_member (member_id, status),
    KEY idx_dep_maturity (org_id, maturity_date, status),
    KEY idx_dep_branch (branch_id, deposit_type, status),
    CONSTRAINT fk_dep_member  FOREIGN KEY (member_id)  REFERENCES members (id)           ON DELETE RESTRICT,
    CONSTRAINT fk_dep_product FOREIGN KEY (product_id) REFERENCES deposit_products (id)  ON DELETE RESTRICT,
    CONSTRAINT fk_dep_branch  FOREIGN KEY (branch_id)  REFERENCES branches (id)          ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE deposit_transactions (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    deposit_account_id  BIGINT UNSIGNED NOT NULL,
    receipt_no          VARCHAR(32)     NOT NULL,
    idempotency_key     VARCHAR(64)     NOT NULL,
    txn_type            ENUM('deposit','withdrawal','interest_credit','tds_debit','penalty','maturity_payout','premature_closure','transfer_in','transfer_out','reversal') NOT NULL,
    amount              BIGINT UNSIGNED NOT NULL,
    balance_after       BIGINT UNSIGNED NOT NULL,
    value_date          DATE            NOT NULL,
    payment_mode        ENUM('cash','upi','neft','imps','rtgs','cheque','transfer','adjustment') NOT NULL,
    reference_no        VARCHAR(64)         NULL,
    voucher_id          BIGINT UNSIGNED     NULL,
    reversal_of_id      BIGINT UNSIGNED     NULL,
    remarks             VARCHAR(255)        NULL,
    created_by          INT UNSIGNED        NULL,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_deptxn_idem (idempotency_key),
    KEY idx_deptxn_acct (deposit_account_id, value_date),
    CONSTRAINT fk_deptxn_acct FOREIGN KEY (deposit_account_id) REFERENCES deposit_accounts (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE share_transactions (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED    NOT NULL,
    member_id           BIGINT UNSIGNED NOT NULL,
    txn_type            ENUM('allotment','transfer_in','transfer_out','surrender','forfeiture') NOT NULL,
    share_count         INT UNSIGNED    NOT NULL,
    face_value          BIGINT UNSIGNED NOT NULL COMMENT 'Paise per share. Nidhi Rules: Rs 10 nominal, min 10 shares',
    total_amount        BIGINT UNSIGNED NOT NULL,
    certificate_no      VARCHAR(32)         NULL,
    distinctive_from    BIGINT UNSIGNED     NULL,
    distinctive_to      BIGINT UNSIGNED     NULL,
    txn_date            DATE            NOT NULL,
    voucher_id          BIGINT UNSIGNED     NULL,
    created_by          INT UNSIGNED        NULL,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    KEY idx_share_member (member_id, txn_date),
    CONSTRAINT fk_share_member FOREIGN KEY (member_id) REFERENCES members (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 10. DOUBLE-ENTRY ACCOUNTING
-- ---------------------------------------------------------------------
--  acc_chart_of_accounts  -> the GL tree
--  acc_vouchers           -> journal header (one business event)
--  transactions           -> journal LINES (the double-entry legs)
--
--  Invariant: SUM(debit) = SUM(credit) for every voucher_id. Enforced
--  in FinLend\Domain\Accounting\Ledger::post() inside the same DB
--  transaction that writes the business row, and re-proved nightly.
-- =====================================================================

CREATE TABLE acc_chart_of_accounts (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED    NOT NULL,
    code                VARCHAR(20)     NOT NULL COMMENT 'Hierarchical, e.g. 1000 / 1100 / 1101',
    name                VARCHAR(150)    NOT NULL,
    parent_id           INT UNSIGNED        NULL,
    account_type        ENUM('asset','liability','equity','income','expense') NOT NULL,
    account_subtype     VARCHAR(48)         NULL COMMENT 'cash, bank, loan_asset, deposit_liability, interest_income...',
    normal_balance      ENUM('debit','credit') NOT NULL,
    is_group            TINYINT(1)      NOT NULL DEFAULT 0 COMMENT 'Group accounts cannot be posted to directly',
    is_bank_account     TINYINT(1)      NOT NULL DEFAULT 0,
    is_cash_account     TINYINT(1)      NOT NULL DEFAULT 0,
    branch_id           INT UNSIGNED        NULL COMMENT 'Set for branch-specific control accounts',
    -- Schedule III (Companies Act) grouping, for statutory financials
    schedule3_head      VARCHAR(120)        NULL,
    -- RBI return mapping so NBS-9 / DNBS-13 line items are auto-derived
    rbi_return_code     VARCHAR(24)         NULL,
    opening_balance     BIGINT          NOT NULL DEFAULT 0 COMMENT 'Signed paise, as at fy start',
    is_active           TINYINT(1)      NOT NULL DEFAULT 1,
    is_system           TINYINT(1)      NOT NULL DEFAULT 0,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_coa_code (org_id, code),
    KEY idx_coa_parent (parent_id),
    KEY idx_coa_type (org_id, account_type, is_active),
    CONSTRAINT fk_coa_org    FOREIGN KEY (org_id)    REFERENCES organisations (id)        ON DELETE CASCADE,
    CONSTRAINT fk_coa_parent FOREIGN KEY (parent_id) REFERENCES acc_chart_of_accounts (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE acc_vouchers (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED    NOT NULL,
    branch_id           INT UNSIGNED    NOT NULL,
    voucher_no          VARCHAR(32)     NOT NULL,
    voucher_type        ENUM('receipt','payment','journal','contra','disbursement','accrual','provision',
                             'depreciation','write_off','opening','closing','reversal') NOT NULL,
    voucher_date        DATE            NOT NULL,
    posting_date        DATE            NOT NULL COMMENT 'Business day; must reference an OPEN business_days row',
    fy_year             SMALLINT UNSIGNED NOT NULL COMMENT 'e.g. 2026 for FY 2026-27',
    narration           VARCHAR(500)    NOT NULL,
    total_debit         BIGINT UNSIGNED NOT NULL,
    total_credit        BIGINT UNSIGNED NOT NULL,
    -- Polymorphic link back to the business event that caused the entry
    source_module       VARCHAR(32)     NOT NULL COMMENT 'loan_repayment, disbursement, deposit, charge, eod_accrual...',
    source_id           BIGINT UNSIGNED     NULL,
    idempotency_key     VARCHAR(64)     NOT NULL,
    basis               ENUM('cash','accrual') NOT NULL DEFAULT 'accrual',
    status              ENUM('draft','posted','reversed') NOT NULL DEFAULT 'posted',
    reversal_of_id      BIGINT UNSIGNED     NULL,
    reversed_by_id      BIGINT UNSIGNED     NULL,
    approved_by         INT UNSIGNED        NULL,
    created_by          INT UNSIGNED        NULL,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_voucher_no (org_id, voucher_no),
    UNIQUE KEY uq_voucher_idem (org_id, idempotency_key),
    KEY idx_voucher_date (org_id, posting_date, voucher_type),
    KEY idx_voucher_branch (branch_id, posting_date),
    KEY idx_voucher_source (source_module, source_id),
    CONSTRAINT fk_voucher_branch FOREIGN KEY (branch_id) REFERENCES branches (id) ON DELETE RESTRICT,
    CONSTRAINT chk_voucher_balanced CHECK (total_debit = total_credit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- The general ledger. APPEND-ONLY (enforced by triggers below).
CREATE TABLE transactions (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED    NOT NULL,
    branch_id           INT UNSIGNED    NOT NULL,
    voucher_id          BIGINT UNSIGNED NOT NULL,
    line_no             SMALLINT UNSIGNED NOT NULL,
    account_id          INT UNSIGNED    NOT NULL COMMENT 'FK to acc_chart_of_accounts',
    debit               BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Paise',
    credit              BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Paise',
    -- Sub-ledger dimensions, so a GL line can always be traced to a party
    member_id           BIGINT UNSIGNED     NULL,
    loan_account_id     BIGINT UNSIGNED     NULL,
    deposit_account_id  BIGINT UNSIGNED     NULL,
    cost_centre_id      INT UNSIGNED        NULL,
    posting_date        DATE            NOT NULL,
    value_date          DATE            NOT NULL,
    fy_year             SMALLINT UNSIGNED NOT NULL,
    narration           VARCHAR(500)        NULL,
    instrument_type     ENUM('cash','bank','transfer','adjustment','notional') NOT NULL DEFAULT 'transfer',
    reference_no        VARCHAR(64)         NULL,
    created_by          INT UNSIGNED        NULL,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_txn_line (voucher_id, line_no),
    -- Trial balance / ledger extraction path
    KEY idx_txn_account_date (org_id, account_id, posting_date),
    KEY idx_txn_branch_date (branch_id, posting_date),
    KEY idx_txn_loan (loan_account_id, posting_date),
    KEY idx_txn_member (member_id, posting_date),
    KEY idx_txn_deposit (deposit_account_id, posting_date),
    KEY idx_txn_fy (org_id, fy_year, account_id),
    CONSTRAINT fk_txn_voucher FOREIGN KEY (voucher_id)      REFERENCES acc_vouchers (id)          ON DELETE RESTRICT,
    CONSTRAINT fk_txn_account FOREIGN KEY (account_id)      REFERENCES acc_chart_of_accounts (id) ON DELETE RESTRICT,
    CONSTRAINT fk_txn_branch  FOREIGN KEY (branch_id)       REFERENCES branches (id)              ON DELETE RESTRICT,
    CONSTRAINT fk_txn_loan    FOREIGN KEY (loan_account_id) REFERENCES loan_accounts (id)         ON DELETE RESTRICT,
    -- Exactly one side of every line must carry a value
    CONSTRAINT chk_txn_one_side CHECK ((debit = 0) <> (credit = 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pre-aggregated closing balances per account per day. Turns a trial
-- balance from a full-journal scan into a single indexed lookup.
CREATE TABLE acc_daily_balances (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED    NOT NULL,
    branch_id           INT UNSIGNED    NOT NULL,
    account_id          INT UNSIGNED    NOT NULL,
    balance_date        DATE            NOT NULL,
    opening_balance     BIGINT          NOT NULL DEFAULT 0 COMMENT 'Signed',
    total_debit         BIGINT UNSIGNED NOT NULL DEFAULT 0,
    total_credit        BIGINT UNSIGNED NOT NULL DEFAULT 0,
    closing_balance     BIGINT          NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE KEY uq_adb (org_id, branch_id, account_id, balance_date),
    KEY idx_adb_date (balance_date, account_id),
    CONSTRAINT fk_adb_account FOREIGN KEY (account_id) REFERENCES acc_chart_of_accounts (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 11. IMMUTABLE AUDIT TRAIL
-- ---------------------------------------------------------------------
--  Every row carries the SHA-256 of the previous row for the same org
--  (`prev_hash` -> `row_hash`). Deleting or editing any historic row
--  breaks the chain, and the nightly verifier reports exactly where.
--  This satisfies the Companies (Accounts) Rules audit-trail mandate
--  and gives an RBI inspection a tamper-evident record.
-- =====================================================================

CREATE TABLE audit_logs (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED    NOT NULL,
    branch_id           INT UNSIGNED        NULL,
    -- Who
    user_id             INT UNSIGNED        NULL,
    username            VARCHAR(64)         NULL COMMENT 'Denormalised — survives a user rename',
    role_slug           VARCHAR(48)         NULL,
    impersonated_by     INT UNSIGNED        NULL,
    -- What
    event_type          ENUM('create','update','delete','view','login','logout','login_failed','export',
                             'approve','reject','reverse','waive','disburse','post','config_change','api_call','permission_change')
                                        NOT NULL,
    entity_type         VARCHAR(64)     NOT NULL COMMENT 'members, loan_accounts, transactions...',
    entity_id           BIGINT UNSIGNED     NULL,
    action              VARCHAR(120)    NOT NULL COMMENT 'Human-readable: "Reversed receipt RCP/2026/00412"',
    -- Before/after. PII-bearing fields are redacted by the Audit writer
    -- before serialisation; only changed keys are stored.
    old_values          JSON                NULL,
    new_values          JSON                NULL,
    changed_fields      VARCHAR(500)        NULL,
    amount_involved     BIGINT              NULL COMMENT 'Signed paise, for money-movement events',
    -- Where / how
    ip_address          VARBINARY(16)       NULL,
    user_agent          VARCHAR(255)        NULL,
    request_method      VARCHAR(8)          NULL,
    request_uri         VARCHAR(255)        NULL,
    request_id          CHAR(36)            NULL COMMENT 'Correlates every log line of one HTTP request',
    session_id          CHAR(64)            NULL,
    channel             ENUM('web','mobile_app','api','system','cron') NOT NULL DEFAULT 'web',
    -- Risk
    severity            ENUM('info','notice','warning','critical') NOT NULL DEFAULT 'info',
    is_sensitive        TINYINT(1)      NOT NULL DEFAULT 0,
    -- Tamper evidence
    prev_hash           CHAR(64)            NULL,
    row_hash            CHAR(64)        NOT NULL,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    KEY idx_audit_entity (entity_type, entity_id, created_at),
    KEY idx_audit_user (user_id, created_at),
    KEY idx_audit_org_time (org_id, created_at),
    KEY idx_audit_event (event_type, created_at),
    KEY idx_audit_severity (org_id, severity, created_at),
    KEY idx_audit_request (request_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 12. INTEGRATIONS, MESSAGING, JOBS & SETTINGS
-- =====================================================================

CREATE TABLE integration_logs (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED        NULL,
    provider            VARCHAR(48)     NOT NULL COMMENT 'cibil, crif_highmark, karza, gupshup, digio, razorpay...',
    category            ENUM('bureau','kyc','sms','whatsapp','email','nach','payment','banking','esign','other') NOT NULL,
    operation           VARCHAR(64)     NOT NULL,
    direction           ENUM('outbound','inbound_webhook') NOT NULL DEFAULT 'outbound',
    entity_type         VARCHAR(48)         NULL,
    entity_id           BIGINT UNSIGNED     NULL,
    request_id          CHAR(36)            NULL,
    endpoint            VARCHAR(255)        NULL,
    http_method         VARCHAR(8)          NULL,
    request_headers     JSON                NULL COMMENT 'Auth headers redacted at write time',
    request_body_enc    BLOB                NULL,
    response_code       SMALLINT UNSIGNED   NULL,
    response_body_enc   LONGBLOB            NULL,
    status              ENUM('pending','success','failed','timeout','retrying') NOT NULL DEFAULT 'pending',
    error_message       VARCHAR(500)        NULL,
    attempt_no          TINYINT UNSIGNED NOT NULL DEFAULT 1,
    latency_ms          INT UNSIGNED        NULL,
    cost_paise          INT UNSIGNED    NOT NULL DEFAULT 0,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    KEY idx_intlog_provider (provider, created_at),
    KEY idx_intlog_entity (entity_type, entity_id),
    KEY idx_intlog_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE message_templates (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED    NOT NULL,
    code                VARCHAR(48)     NOT NULL COMMENT 'emi_reminder_t3, receipt_ack, overdue_d7, otp_login...',
    channel             ENUM('sms','whatsapp','email','push','ivr') NOT NULL,
    language            VARCHAR(8)      NOT NULL DEFAULT 'en',
    subject             VARCHAR(200)        NULL,
    body                TEXT            NOT NULL COMMENT 'Placeholders: {{member_name}}, {{amount}}, {{due_date}}...',
    -- TRAI DLT compliance for Indian SMS
    dlt_template_id     VARCHAR(32)         NULL,
    dlt_entity_id       VARCHAR(32)         NULL,
    dlt_status          ENUM('not_registered','pending','approved','rejected') NOT NULL DEFAULT 'not_registered',
    -- Meta WhatsApp Business template
    wa_template_name    VARCHAR(64)         NULL,
    wa_template_status  ENUM('not_submitted','pending','approved','rejected','paused') NOT NULL DEFAULT 'not_submitted',
    wa_category         ENUM('utility','authentication','marketing') NOT NULL DEFAULT 'utility',
    is_active           TINYINT(1)      NOT NULL DEFAULT 1,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_msgtpl (org_id, code, channel, language),
    KEY idx_msgtpl_active (org_id, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE message_queue (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED    NOT NULL,
    template_id         INT UNSIGNED        NULL,
    channel             ENUM('sms','whatsapp','email','push','ivr') NOT NULL,
    recipient           VARCHAR(160)    NOT NULL,
    member_id           BIGINT UNSIGNED     NULL,
    loan_account_id     BIGINT UNSIGNED     NULL,
    subject             VARCHAR(200)        NULL,
    body                TEXT            NOT NULL,
    payload_json        JSON                NULL COMMENT 'Rendered template variables, for resend/debug',
    -- RBI Fair Practices / recovery-agent norms: no contact before 08:00
    -- or after 19:00. The dispatcher refuses to send outside this window.
    send_after          DATETIME(6)         NULL,
    priority            TINYINT UNSIGNED NOT NULL DEFAULT 5,
    status              ENUM('queued','sending','sent','delivered','read','failed','cancelled','suppressed') NOT NULL DEFAULT 'queued',
    provider            VARCHAR(32)         NULL,
    provider_message_id VARCHAR(96)         NULL,
    attempts            TINYINT UNSIGNED NOT NULL DEFAULT 0,
    max_attempts        TINYINT UNSIGNED NOT NULL DEFAULT 3,
    last_error          VARCHAR(500)        NULL,
    cost_paise          INT UNSIGNED    NOT NULL DEFAULT 0,
    queued_at           DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    sent_at             DATETIME(6)         NULL,
    delivered_at        DATETIME(6)         NULL,
    PRIMARY KEY (id),
    KEY idx_mq_dispatch (status, priority, send_after),
    KEY idx_mq_member (member_id, channel, queued_at),
    KEY idx_mq_provider_msg (provider_message_id),
    KEY idx_mq_loan (loan_account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE job_queue (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    queue               VARCHAR(48)     NOT NULL DEFAULT 'default',
    job_class           VARCHAR(150)    NOT NULL,
    payload             JSON            NOT NULL,
    idempotency_key     VARCHAR(64)         NULL,
    available_at        DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    reserved_at         DATETIME(6)         NULL,
    reserved_by         VARCHAR(64)         NULL COMMENT 'Worker id — prevents two workers claiming one job',
    attempts            TINYINT UNSIGNED NOT NULL DEFAULT 0,
    max_attempts        TINYINT UNSIGNED NOT NULL DEFAULT 3,
    status              ENUM('pending','reserved','processing','completed','failed','dead') NOT NULL DEFAULT 'pending',
    last_error          TEXT                NULL,
    completed_at        DATETIME(6)         NULL,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_job_idem (idempotency_key),
    KEY idx_job_claim (queue, status, available_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE settings (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED        NULL COMMENT 'NULL = platform-wide default',
    branch_id           INT UNSIGNED        NULL COMMENT 'Most specific match wins: branch > org > platform',
    setting_key         VARCHAR(96)     NOT NULL,
    setting_value       TEXT                NULL,
    value_type          ENUM('string','int','bool','json','date','encrypted') NOT NULL DEFAULT 'string',
    category            VARCHAR(48)     NOT NULL DEFAULT 'general',
    description         VARCHAR(255)        NULL,
    is_editable         TINYINT(1)      NOT NULL DEFAULT 1,
    updated_by          INT UNSIGNED        NULL,
    updated_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_setting (org_id, branch_id, setting_key),
    KEY idx_setting_cat (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Atomic, gap-free document numbering. Claimed with SELECT ... FOR UPDATE
-- so two cashiers can never mint the same receipt number.
CREATE TABLE number_sequences (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED    NOT NULL,
    branch_id           INT UNSIGNED        NULL,
    sequence_key        VARCHAR(48)     NOT NULL COMMENT 'member_no, loan_no, receipt_no, voucher_no...',
    prefix              VARCHAR(24)     NOT NULL DEFAULT '',
    fy_year             SMALLINT UNSIGNED   NULL COMMENT 'Set when the series resets every financial year',
    current_value       BIGINT UNSIGNED NOT NULL DEFAULT 0,
    padding             TINYINT UNSIGNED NOT NULL DEFAULT 5,
    updated_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_seq (org_id, branch_id, sequence_key, fy_year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Generated statutory returns, kept with their file hash as filing proof
CREATE TABLE compliance_reports (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id              INT UNSIGNED    NOT NULL,
    report_code         VARCHAR(32)     NOT NULL COMMENT 'NDH-1, NDH-3, DNBS-13, NBS-9, CIC_MONTHLY, CKYC_UPLOAD, FIU_CTR, FIU_STR',
    report_name         VARCHAR(150)    NOT NULL,
    regulator           ENUM('rbi','mca','fiu_ind','cersai','cic','gst','income_tax','internal') NOT NULL,
    period_type         ENUM('daily','monthly','quarterly','half_yearly','annual','adhoc') NOT NULL,
    period_start        DATE            NOT NULL,
    period_end          DATE            NOT NULL,
    due_date            DATE                NULL,
    file_path           VARCHAR(255)        NULL,
    file_format         ENUM('xml','csv','txt','xlsx','pdf','json') NOT NULL DEFAULT 'csv',
    file_hash           CHAR(64)            NULL,
    record_count        INT UNSIGNED    NOT NULL DEFAULT 0,
    total_amount        BIGINT UNSIGNED NOT NULL DEFAULT 0,
    status              ENUM('pending','generating','generated','submitted','acknowledged','rejected','revised') NOT NULL DEFAULT 'pending',
    submitted_at        DATETIME(6)         NULL,
    acknowledgement_no  VARCHAR(64)         NULL,
    rejection_reason    VARCHAR(500)        NULL,
    generated_by        INT UNSIGNED        NULL,
    created_at          DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uq_compliance (org_id, report_code, period_start, period_end),
    KEY idx_comp_due (org_id, status, due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 13. DEFERRED FOREIGN KEYS
--     (added last because they point at tables created further down)
-- =====================================================================

ALTER TABLE branches
    ADD CONSTRAINT fk_branch_cash_gl FOREIGN KEY (cash_gl_id) REFERENCES acc_chart_of_accounts (id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_branch_bank_gl FOREIGN KEY (bank_gl_id) REFERENCES acc_chart_of_accounts (id) ON DELETE SET NULL;

ALTER TABLE members
    ADD CONSTRAINT fk_member_primary_bank FOREIGN KEY (primary_bank_account_id) REFERENCES member_bank_accounts (id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_member_portal_user  FOREIGN KEY (portal_user_id)          REFERENCES users (id)                ON DELETE SET NULL;

ALTER TABLE member_groups
    ADD CONSTRAINT fk_group_leader    FOREIGN KEY (leader_member_id)    REFERENCES members (id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_group_secretary FOREIGN KEY (secretary_member_id) REFERENCES members (id) ON DELETE SET NULL;

ALTER TABLE loan_products
    ADD CONSTRAINT fk_prod_gl_principal  FOREIGN KEY (gl_principal_id)         REFERENCES acc_chart_of_accounts (id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_prod_gl_int_income FOREIGN KEY (gl_interest_income_id)   REFERENCES acc_chart_of_accounts (id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_prod_gl_int_recv   FOREIGN KEY (gl_interest_receivable_id) REFERENCES acc_chart_of_accounts (id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_prod_gl_fee        FOREIGN KEY (gl_fee_income_id)        REFERENCES acc_chart_of_accounts (id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_prod_gl_penal      FOREIGN KEY (gl_penal_income_id)      REFERENCES acc_chart_of_accounts (id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_prod_gl_provision  FOREIGN KEY (gl_provision_id)         REFERENCES acc_chart_of_accounts (id) ON DELETE SET NULL;

ALTER TABLE loan_accounts
    ADD CONSTRAINT fk_loan_mandate  FOREIGN KEY (nach_mandate_id)       REFERENCES nach_mandates (id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_loan_collofcr FOREIGN KEY (collection_officer_id) REFERENCES users (id)         ON DELETE SET NULL;

ALTER TABLE loan_repayments
    ADD CONSTRAINT fk_repay_voucher FOREIGN KEY (voucher_id)          REFERENCES acc_vouchers (id)      ON DELETE RESTRICT,
    ADD CONSTRAINT fk_repay_sheet   FOREIGN KEY (collection_sheet_id) REFERENCES collection_sheets (id) ON DELETE SET NULL;

ALTER TABLE loan_disbursements
    ADD CONSTRAINT fk_disb_voucher FOREIGN KEY (voucher_id) REFERENCES acc_vouchers (id) ON DELETE RESTRICT;

ALTER TABLE nach_presentations
    ADD CONSTRAINT fk_np_repayment FOREIGN KEY (repayment_id) REFERENCES loan_repayments (id) ON DELETE SET NULL;

ALTER TABLE collection_sheet_lines
    ADD CONSTRAINT fk_csl_repayment FOREIGN KEY (repayment_id) REFERENCES loan_repayments (id) ON DELETE SET NULL;

-- =====================================================================
-- 14. IMMUTABILITY TRIGGERS
-- ---------------------------------------------------------------------
--  Defence in depth. Even if the application is compromised, the ledger
--  and the audit trail cannot be rewritten through the app's DB grant.
--  Corrections must be posted as reversal rows.
-- =====================================================================

DELIMITER $$

CREATE TRIGGER trg_transactions_no_update
BEFORE UPDATE ON transactions FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'transactions is append-only: post a reversal voucher instead of updating a ledger line';
END$$

CREATE TRIGGER trg_transactions_no_delete
BEFORE DELETE ON transactions FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'transactions is append-only: ledger lines cannot be deleted';
END$$

CREATE TRIGGER trg_audit_no_update
BEFORE UPDATE ON audit_logs FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'audit_logs is immutable';
END$$

CREATE TRIGGER trg_audit_no_delete
BEFORE DELETE ON audit_logs FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'audit_logs is immutable';
END$$

CREATE TRIGGER trg_repayment_no_delete
BEFORE DELETE ON loan_repayments FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'loan_repayments is append-only: reverse the receipt instead of deleting it';
END$$

-- A receipt may only ever move forward through its lifecycle, and its
-- money columns are frozen the moment it is written.
CREATE TRIGGER trg_repayment_guard_update
BEFORE UPDATE ON loan_repayments FOR EACH ROW
BEGIN
    IF NEW.amount <> OLD.amount
       OR NEW.loan_account_id   <> OLD.loan_account_id
       OR NEW.value_date        <> OLD.value_date
       OR NEW.principal_component <> OLD.principal_component
       OR NEW.interest_component  <> OLD.interest_component
       OR NEW.penal_component     <> OLD.penal_component THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Financial fields of a posted receipt are immutable; post a reversal';
    END IF;
    IF OLD.status IN ('reversed','cancelled') AND NEW.status <> OLD.status THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'A reversed or cancelled receipt cannot be re-opened';
    END IF;
END$$

-- Nothing may post into a closed business day.
CREATE TRIGGER trg_voucher_day_open
BEFORE INSERT ON acc_vouchers FOR EACH ROW
BEGIN
    DECLARE v_status VARCHAR(16);
    SELECT status INTO v_status
      FROM business_days
     WHERE org_id = NEW.org_id
       AND (branch_id = NEW.branch_id OR branch_id IS NULL)
       AND business_date = NEW.posting_date
     ORDER BY branch_id IS NULL
     LIMIT 1;
    IF v_status IS NOT NULL AND v_status = 'closed' THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Cannot post: the business day is closed. Re-open it or post to the current day.';
    END IF;
END$$

DELIMITER ;

-- =====================================================================
-- 15. REPORTING VIEWS
--     Amounts are converted to rupees here, once, at the edge.
-- =====================================================================

CREATE OR REPLACE VIEW v_trial_balance AS
SELECT  t.org_id,
        t.branch_id,
        b.name                                      AS branch_name,
        t.fy_year,
        coa.code                                    AS account_code,
        coa.name                                    AS account_name,
        coa.account_type,
        ROUND(SUM(t.debit)  / 100, 2)               AS total_debit,
        ROUND(SUM(t.credit) / 100, 2)               AS total_credit,
        ROUND((SUM(t.debit) - SUM(t.credit)) / 100, 2) AS net_balance
FROM        transactions t
JOIN        acc_chart_of_accounts coa ON coa.id = t.account_id
JOIN        branches b                ON b.id   = t.branch_id
GROUP BY    t.org_id, t.branch_id, b.name, t.fy_year, coa.code, coa.name, coa.account_type;

CREATE OR REPLACE VIEW v_loan_portfolio AS
SELECT  la.org_id,
        la.branch_id,
        b.name                                      AS branch_name,
        lp.code                                     AS product_code,
        lp.name                                     AS product_name,
        lp.regulatory_class,
        la.asset_classification,
        COUNT(*)                                    AS account_count,
        ROUND(SUM(la.principal)              / 100, 2) AS sanctioned_amount,
        ROUND(SUM(la.disbursed_amount)       / 100, 2) AS disbursed_amount,
        ROUND(SUM(la.principal_outstanding)  / 100, 2) AS principal_outstanding,
        ROUND(SUM(la.overdue_principal + la.overdue_interest) / 100, 2) AS total_overdue,
        ROUND(SUM(la.provision_amount)       / 100, 2) AS provision_held,
        ROUND(AVG(la.dpd), 1)                       AS avg_dpd
FROM        loan_accounts la
JOIN        loan_products lp ON lp.id = la.product_id
JOIN        branches      b  ON b.id  = la.branch_id
WHERE       la.status IN ('active','overdue','npa')
GROUP BY    la.org_id, la.branch_id, b.name, lp.code, lp.name, lp.regulatory_class, la.asset_classification;

-- Portfolio at Risk — the single number an MFI board asks for first.
CREATE OR REPLACE VIEW v_par_summary AS
SELECT  org_id,
        branch_id,
        ROUND(SUM(principal_outstanding) / 100, 2) AS gross_loan_portfolio,
        ROUND(SUM(CASE WHEN dpd >  0 THEN principal_outstanding ELSE 0 END) / 100, 2) AS par_1,
        ROUND(SUM(CASE WHEN dpd > 30 THEN principal_outstanding ELSE 0 END) / 100, 2) AS par_30,
        ROUND(SUM(CASE WHEN dpd > 60 THEN principal_outstanding ELSE 0 END) / 100, 2) AS par_60,
        ROUND(SUM(CASE WHEN dpd > 90 THEN principal_outstanding ELSE 0 END) / 100, 2) AS par_90,
        ROUND(100 * SUM(CASE WHEN dpd > 30 THEN principal_outstanding ELSE 0 END)
                  / NULLIF(SUM(principal_outstanding), 0), 2)                        AS par_30_pct,
        ROUND(100 * SUM(CASE WHEN npa_flag = 1 THEN principal_outstanding ELSE 0 END)
                  / NULLIF(SUM(principal_outstanding), 0), 2)                        AS gnpa_pct
FROM        loan_accounts
WHERE       status IN ('active','overdue','npa')
GROUP BY    org_id, branch_id;

CREATE OR REPLACE VIEW v_collection_efficiency AS
SELECT  s.org_id,
        s.branch_id,
        s.collection_date,
        s.officer_id,
        u.full_name                                 AS officer_name,
        COUNT(DISTINCT s.id)                        AS sheets,
        SUM(s.expected_accounts)                    AS expected_accounts,
        SUM(s.collected_accounts)                   AS collected_accounts,
        ROUND(SUM(s.expected_amount)  / 100, 2)     AS demand,
        ROUND(SUM(s.collected_amount) / 100, 2)     AS collected,
        ROUND(100 * SUM(s.collected_amount) / NULLIF(SUM(s.expected_amount), 0), 2) AS efficiency_pct
FROM        collection_sheets s
JOIN        users u ON u.id = s.officer_id
GROUP BY    s.org_id, s.branch_id, s.collection_date, s.officer_id, u.full_name;


-- =====================================================================
-- END OF SCHEMA
-- =====================================================================
