-- ============================================================
--  CareBill — maker / checker approvals.
--  Existing database:  mysql carebill < sql/approvals.sql
-- ============================================================
CREATE TABLE approval_requests (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  kind            ENUM('writeoff','discount','credit_note','bank_change','large_invoice') NOT NULL,
  subject_type    VARCHAR(20),                   -- invoice | brand
  subject_id      INT NULL,
  client_id       INT NULL,
  amount          DECIMAL(14,2) DEFAULT 0,
  summary         VARCHAR(255),
  payload         MEDIUMTEXT,                    -- exactly what will be done on approval
  payload_hash    CHAR(64),                      -- so what is approved is what gets done
  status          ENUM('pending','approved','rejected','executed','failed','withdrawn') DEFAULT 'pending',
  requested_by    INT NOT NULL,
  requested_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  reason          VARCHAR(255),
  decided_by      INT NULL,
  decided_at      DATETIME NULL,
  decision_note   VARCHAR(255),
  executed_at     DATETIME NULL,
  result          VARCHAR(255),
  INDEX ix_appr_status (status, requested_at),
  INDEX ix_appr_subject (subject_type, subject_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO settings (k,v) VALUES
 ('approvals_enabled','1'),
 ('approve_writeoff_above','1000'),
 ('approve_discount_above','5000'),
 ('approve_invoice_above','500000'),
 ('approve_credit_notes','1'),
 ('approve_bank_changes','1')
ON DUPLICATE KEY UPDATE k=k;