-- Per-customer settings: balance_type and pdf_delivery
-- Admin manages this once per customer; persists across all batches
CREATE TABLE IF NOT EXISTS `balance_confirmation_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL COMMENT 'customer code from mt_business_partner',
  `balance_type` enum('MANUAL','AUTO') NOT NULL DEFAULT 'MANUAL'
    COMMENT 'MANUAL=admin enters amount, AUTO=calculated from mt_ar_trans',
  `pdf_delivery` enum('LINK','ATTACHMENT') NOT NULL DEFAULT 'LINK'
    COMMENT 'LINK=URL in email body, 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;

-- One batch per run (e.g. year-end 31-03-2026)
CREATE TABLE IF NOT EXISTS `balance_confirmation_batch` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `as_on_date` date NOT NULL COMMENT 'balance as of this date',
  `label` varchar(100) DEFAULT NULL COMMENT 'e.g. 31-03-2026',
  `job_type` varchar(20) NOT NULL DEFAULT 'ALL' COMMENT 'always ALL — balance covers all job types',
  `created_date` datetime DEFAULT NULL,
  `temp_code` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One row per customer per batch
-- For MANUAL: admin fills balance_amount before running action=send
-- For AUTO:   balance_amount is populated at create time from mt_ar_trans
CREATE TABLE IF NOT EXISTS `balance_confirmation_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `batch_id` int(11) NOT NULL,
  `code` varchar(50) DEFAULT NULL COMMENT 'customer code',
  `name` varchar(200) DEFAULT NULL,
  `salesman` varchar(100) DEFAULT NULL,
  `balance_amount` decimal(15,2) DEFAULT 0.00 COMMENT 'balance payable to Glottis',
  `balance_type` enum('MANUAL','AUTO') DEFAULT 'MANUAL',
  `pdf_delivery` enum('LINK','ATTACHMENT') DEFAULT 'LINK',
  `to_email` text DEFAULT NULL COMMENT 'from mt_bp_contact',
  `sales_email` varchar(200) DEFAULT NULL COMMENT 'CC - salesperson',
  `default_soa_cc` varchar(200) DEFAULT NULL,
  `over_sea_customer` varchar(10) DEFAULT 'NO',
  `success` varchar(5) DEFAULT 'NO',
  `security_key` varchar(100) DEFAULT NULL,
  `aws_ses_msg` text DEFAULT NULL,
  `last_event_date` datetime DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_batch_success` (`batch_id`,`success`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
