-- Admin-managed table: one row per customer to receive a balance confirmation letter.
-- Admin adds/updates this table before each run.
-- balance_amount is used only when mt_master balance_letter_balance_manual = YES.
-- When NO, balance is computed from mt_ar_trans up to the as_on_date passed at send time.
CREATE TABLE IF NOT EXISTS `balance_letter_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL COMMENT 'customer code from mt_business_partner',
  `balance_amount` decimal(15,2) DEFAULT 0.00 COMMENT 'used in MANUAL mode only',
  `pdf_delivery` enum('LINK','ATTACHMENT') NOT NULL DEFAULT 'LINK'
    COMMENT 'LINK=PDF URL button in email, ATTACHMENT=PDF attached to email',
  `created_date` datetime DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- mt_master row to control MANUAL vs AUTO mode (insert once, update as needed):
-- INSERT INTO mt_master(page, code, value) VALUES('balance-letter', 'balance_letter_balance_manual', 'YES');
-- Set value='YES' for MANUAL (use balance_letter_data.balance_amount)
-- Set value='NO'  for AUTO  (compute from mt_ar_trans up to as_on_date)
