-- Paga Staff Multipurpose Cooperative Society — schema
-- Target: MySQL / MariaDB (shared cPanel hosting)
-- Import via phpMyAdmin, or: mysql -u USER -p DBNAME < schema.sql


-- ---------------------------------------------------------------------
-- members: every person in the cooperative (staff or not). Excos are
-- members with is_exco = TRUE — matches the demo, where the 3 Excos
-- vote as named individuals, not a separate identity system.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS members (
  id                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  member_number         VARCHAR(20) NOT NULL UNIQUE,
  name                  VARCHAR(150) NOT NULL,
  phone                 VARCHAR(30) NULL,
  email                 VARCHAR(150) NULL,
  next_of_kin_name      VARCHAR(150) NULL,
  next_of_kin_phone     VARCHAR(30) NULL,
  balance               DECIMAL(14,2) NOT NULL DEFAULT 0,
  monthly_contribution  DECIMAL(14,2) NOT NULL DEFAULT 0,
  is_exco               TINYINT(1) NOT NULL DEFAULT 0,
  created_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- requests: withdrawal / loan / bnpl. BNPL-specific fields are NULL
-- for the other two types rather than a separate table — matches the
-- demo's single-shape request object and keeps status transitions in
-- one place.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS requests (
  id                        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  member_id                 INT UNSIGNED NOT NULL,
  type                      ENUM('WITHDRAWAL','LOAN','BNPL') NOT NULL,
  amount                    DECIMAL(14,2) NOT NULL,
  term_months               INT UNSIGNED NULL,
  reason                    TEXT NULL,
  status                    ENUM(
                               'PENDING_GUARANTORS',
                               'PENDING_EXCO_APPROVAL',
                               'APPROVED',
                               'DISBURSED',
                               'REJECTED',
                               'DECLINED_BY_GUARANTOR'
                             ) NOT NULL,
  bnpl_item_description     VARCHAR(200) NULL,
  bnpl_disbursement_type    ENUM('VENDOR_PAYMENT','CASH_TO_MEMBER') NULL,
  bnpl_vendor_name          VARCHAR(150) NULL,
  bnpl_installment_months   INT UNSIGNED NULL,
  bnpl_interest_rate        DECIMAL(5,4) NULL,
  bnpl_total_repayable      DECIMAL(14,2) NULL,
  submitted_at              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_requests_member FOREIGN KEY (member_id) REFERENCES members(id),
  INDEX idx_requests_member (member_id),
  INDEX idx_requests_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- guarantors: loan-only. Combined `amount` across a request's rows
-- must equal 50% of the loan (enforced in application code).
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS guarantors (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_id   INT UNSIGNED NOT NULL,
  member_id    INT UNSIGNED NOT NULL,
  amount       DECIMAL(14,2) NOT NULL,
  status       ENUM('PENDING','APPROVED','DECLINED') NOT NULL DEFAULT 'PENDING',
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_guarantors_request FOREIGN KEY (request_id) REFERENCES requests(id) ON DELETE CASCADE,
  CONSTRAINT fk_guarantors_member FOREIGN KEY (member_id) REFERENCES members(id),
  INDEX idx_guarantors_member (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- exco_votes
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS exco_votes (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_id       INT UNSIGNED NOT NULL,
  exco_member_id   INT UNSIGNED NOT NULL,
  vote             ENUM('APPROVE','REJECT') NOT NULL,
  voted_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_votes_request FOREIGN KEY (request_id) REFERENCES requests(id) ON DELETE CASCADE,
  CONSTRAINT fk_votes_exco FOREIGN KEY (exco_member_id) REFERENCES members(id),
  UNIQUE KEY uniq_vote_per_exco (request_id, exco_member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- contribution_requests: standing monthly-contribution change requests
-- (only created when policy requires Exco approval; self-service
-- changes write straight to members.monthly_contribution)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS contribution_requests (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  member_id    INT UNSIGNED NOT NULL,
  amount       DECIMAL(14,2) NOT NULL,
  status       ENUM('PENDING','APPROVED','REJECTED') NOT NULL DEFAULT 'PENDING',
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at  TIMESTAMP NULL,
  CONSTRAINT fk_contribreq_member FOREIGN KEY (member_id) REFERENCES members(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- registration_requests: membership applications (open beyond Paga
-- staff — this is the table an invite gets generated from on approval)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS registration_requests (
  id                          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name                        VARCHAR(150) NOT NULL,
  phone                       VARCHAR(30) NOT NULL,
  email                       VARCHAR(150) NULL,
  next_of_kin_name            VARCHAR(150) NULL,
  next_of_kin_phone           VARCHAR(30) NULL,
  proposed_monthly_contribution DECIMAL(14,2) NOT NULL,
  status                      ENUM('PENDING','APPROVED','REJECTED') NOT NULL DEFAULT 'PENDING',
  submitted_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at                 TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- statements
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS statements (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  member_id    INT UNSIGNED NOT NULL,
  date_from    DATE NOT NULL,
  date_to      DATE NOT NULL,
  purpose      VARCHAR(250) NULL,
  status       ENUM('PENDING','ISSUED') NOT NULL DEFAULT 'PENDING',
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  issued_at    TIMESTAMP NULL,
  CONSTRAINT fk_statements_member FOREIGN KEY (member_id) REFERENCES members(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- notifications: broadcast log
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS notifications (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  message        TEXT NOT NULL,
  audience       ENUM('ALL_MEMBERS') NOT NULL DEFAULT 'ALL_MEMBERS',
  channels       JSON NOT NULL,
  sent_by_member_id INT UNSIGNED NULL,
  sent_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_notifications_sender FOREIGN KEY (sent_by_member_id) REFERENCES members(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- policy_settings: single row, id always 1
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS policy_settings (
  id                              TINYINT UNSIGNED PRIMARY KEY DEFAULT 1,
  interest_type                   ENUM('FLAT','REDUCING') NOT NULL DEFAULT 'REDUCING',
  loan_savings_linkage            ENUM('AUTO','MANUAL') NOT NULL DEFAULT 'AUTO',
  statement_issuance              ENUM('AUTO','SIGNOFF') NOT NULL DEFAULT 'SIGNOFF',
  exco_tie_break                  ENUM('AUTO_REJECT','ESCALATE') NOT NULL DEFAULT 'AUTO_REJECT',
  standing_contribution_approval  ENUM('SELF_SERVICE','EXCO_APPROVAL') NOT NULL DEFAULT 'SELF_SERVICE',
  notification_channels           JSON NOT NULL,
  CONSTRAINT chk_policy_single_row CHECK (id = 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO policy_settings (id, notification_channels)
  VALUES (1, JSON_ARRAY('IN_APP'))
  ON DUPLICATE KEY UPDATE id = id;

-- ---------------------------------------------------------------------
-- audit_log: not in the demo, added because status changes here touch
-- real savings/loan decisions and "who approved what, when" needs to
-- survive beyond the votes/guarantors rows themselves.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_log (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor_member_id  INT UNSIGNED NULL,
  action           VARCHAR(100) NOT NULL,
  entity_type      VARCHAR(50) NOT NULL,
  entity_id        INT UNSIGNED NULL,
  details          JSON NULL,
  created_at       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_actor FOREIGN KEY (actor_member_id) REFERENCES members(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

