-- ═══════════════════════════════════════════════════════════════════════════
-- rsflow.dspera · Unified Cash-Out layer
-- ONE lpos table with lpo_type discriminator (SUPPLIER | PETTY_CASH | IMPREST
-- | CONTINGENCY | STAFF_ADVANCE | DISPOSAL_REFUND) → ONE payables pipeline
-- (payment_requests → payment_vouchers → payments) → ONE queue for Accountant
-- + FM.
-- The client's rule: "any cash request is an LPO."
-- ═══════════════════════════════════════════════════════════════════════════

DROP TABLE IF EXISTS payment_allocations;
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS payment_vouchers;
DROP TABLE IF EXISTS payment_requests;
DROP TABLE IF EXISTS lpo_items;
DROP TABLE IF EXISTS lpos;

CREATE TABLE lpos (
  c0d                    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  lpo_no                 VARCHAR(30) NOT NULL,
  project_id             INT NOT NULL DEFAULT 0,          -- 0 = central (e.g. HQ petty cash)
  source_module          VARCHAR(20) NOT NULL DEFAULT 'central', -- goods|services|works|central
  lpo_type               VARCHAR(30) NOT NULL,            -- SUPPLIER|PETTY_CASH|IMPREST|CONTINGENCY|STAFF_ADVANCE|DISPOSAL_REFUND
  -- Payee (unified across vendor/employee/customer) ------------------------
  payee_type             VARCHAR(20) NOT NULL DEFAULT 'vendor', -- vendor|employee|customer
  payee_id               INT NOT NULL DEFAULT 0,
  payee_name             VARCHAR(200) NOT NULL DEFAULT '',
  -- Amount ----------------------------------------------------------------
  amount                 DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  currency               VARCHAR(10) NOT NULL DEFAULT 'TZS',
  purpose                TEXT,
  budget_category_id     INT NOT NULL DEFAULT 0,          -- link to budget_item_categories
  -- Origination -----------------------------------------------------------
  requested_by           INT NOT NULL DEFAULT 0,
  requested_by_name      VARCHAR(120) NOT NULL DEFAULT '',
  requested_on           TIMESTAMP NULL DEFAULT NULL,
  -- Approval + payables state --------------------------------------------
  approval_chain_key     VARCHAR(60) NOT NULL DEFAULT '',
  approval_id            INT NOT NULL DEFAULT 0,          -- FK to fin_approvals
  cash_out_status        VARCHAR(30) NOT NULL DEFAULT 'DRAFT', -- DRAFT|SUBMITTED|APPROVED|ISSUED|PAID|RETIRED|CLOSED|DECLINED
  next_check_status      VARCHAR(60) NOT NULL DEFAULT '',
  next_check_by_id       INT NOT NULL DEFAULT 0,
  -- Retirement (for IMPREST/PETTY_CASH) ----------------------------------
  retired_amount         DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  return_to_float_amount DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  retired_on             TIMESTAMP NULL DEFAULT NULL,
  -- Audit -----------------------------------------------------------------
  created_by             INT NOT NULL DEFAULT 0,
  created_on             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited_by              INT NOT NULL DEFAULT 0,
  edited_on              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted                TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_lpo_no (lpo_no),
  KEY ix_project (project_id),
  KEY ix_type (lpo_type),
  KEY ix_source (source_module),
  KEY ix_cash_status (cash_out_status),
  KEY ix_next_check (next_check_status),
  KEY ix_approval (approval_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE lpo_items (
  c0d              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  lpo_id           INT NOT NULL,
  item_no          INT NOT NULL DEFAULT 1,
  description      VARCHAR(255) NOT NULL,
  qty              DECIMAL(20,4) NOT NULL DEFAULT 1.0000,
  unit             VARCHAR(20) NOT NULL DEFAULT '',
  unit_price       DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  line_total       DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  budget_category_id INT NOT NULL DEFAULT 0,
  notes            TEXT,
  created_on       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_lpo (lpo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE payment_requests (
  c0d                INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  pr_no              VARCHAR(30) NOT NULL,
  lpo_id             INT NOT NULL,
  pr_stage           VARCHAR(10) NOT NULL DEFAULT 'PR1', -- PR1 (field/PC raise) | PR2 (accountant finalise)
  requested_amount   DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  currency           VARCHAR(10) NOT NULL DEFAULT 'TZS',
  status             VARCHAR(30) NOT NULL DEFAULT 'DRAFT', -- DRAFT|SUBMITTED|CONFIRMED|APPROVED|INVOICED|CANCELLED
  next_check_status  VARCHAR(60) NOT NULL DEFAULT '',
  next_check_by_id   INT NOT NULL DEFAULT 0,
  raised_by          INT NOT NULL DEFAULT 0,
  raised_by_name     VARCHAR(120) NOT NULL DEFAULT '',
  raised_on          TIMESTAMP NULL DEFAULT NULL,
  notes              TEXT,
  created_by         INT NOT NULL DEFAULT 0,
  created_on         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited_by          INT NOT NULL DEFAULT 0,
  edited_on          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted            TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_pr_no (pr_no),
  KEY ix_lpo (lpo_id),
  KEY ix_status (status),
  KEY ix_stage (pr_stage)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE payment_vouchers (
  c0d                INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  pv_no              VARCHAR(30) NOT NULL,
  lpo_id             INT NOT NULL,
  payment_request_id INT NOT NULL DEFAULT 0,
  payee_type         VARCHAR(20) NOT NULL DEFAULT 'vendor',
  payee_id           INT NOT NULL DEFAULT 0,
  payee_name         VARCHAR(200) NOT NULL DEFAULT '',
  amount             DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  currency           VARCHAR(10) NOT NULL DEFAULT 'TZS',
  bank_account_id    INT NOT NULL DEFAULT 0,
  status             VARCHAR(30) NOT NULL DEFAULT 'DRAFT', -- DRAFT|SCHEDULED|PAID|CANCELLED
  scheduled_on       DATE DEFAULT NULL,
  paid_on            DATE DEFAULT NULL,
  reference_no       VARCHAR(60) NOT NULL DEFAULT '', -- cheque no / TT ref
  prepared_by        INT NOT NULL DEFAULT 0,
  prepared_by_name   VARCHAR(120) NOT NULL DEFAULT '',
  prepared_on        TIMESTAMP NULL DEFAULT NULL,
  approved_by        INT NOT NULL DEFAULT 0,
  approved_by_name   VARCHAR(120) NOT NULL DEFAULT '',
  approved_on        TIMESTAMP NULL DEFAULT NULL,
  notes              TEXT,
  created_by         INT NOT NULL DEFAULT 0,
  created_on         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited_by          INT NOT NULL DEFAULT 0,
  edited_on          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted            TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_pv_no (pv_no),
  KEY ix_lpo (lpo_id),
  KEY ix_status (status),
  KEY ix_scheduled (scheduled_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE payments (
  c0d              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  payment_no       VARCHAR(30) NOT NULL,
  payment_voucher_id INT NOT NULL,
  bank_account_id  INT NOT NULL DEFAULT 0,
  amount           DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  currency         VARCHAR(10) NOT NULL DEFAULT 'TZS',
  payment_method   VARCHAR(20) NOT NULL DEFAULT 'BANK_TRANSFER', -- BANK_TRANSFER|CHEQUE|CASH|MOBILE_MONEY
  reference_no     VARCHAR(60) NOT NULL DEFAULT '',
  payment_date     DATE NOT NULL,
  received_by      VARCHAR(120) NOT NULL DEFAULT '',
  notes            TEXT,
  created_by       INT NOT NULL DEFAULT 0,
  created_on       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited_by        INT NOT NULL DEFAULT 0,
  edited_on        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted          TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_payment_no (payment_no),
  KEY ix_pv (payment_voucher_id),
  KEY ix_date (payment_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE payment_allocations (
  c0d              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  payment_id       INT NOT NULL,
  lpo_id           INT NOT NULL,
  amount           DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  allocation_type  VARCHAR(20) NOT NULL DEFAULT 'PARTIAL', -- FULL|PARTIAL|ADVANCE|BALANCE
  notes            TEXT,
  created_on       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_payment (payment_id),
  KEY ix_lpo (lpo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
