SQL

-- Optional: tables are also created automatically on resource start via MySQL.ready.
-- Use this file if you prefer to run SQL manually.

CREATE TABLE IF NOT EXISTS `finals_billing_invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `citizenid` varchar(50) NOT NULL,
  `biller_citizenid` varchar(50) NOT NULL,
  `biller_name` varchar(128) DEFAULT NULL,
  `biller_job` varchar(64) DEFAULT NULL,
  `entry_id` int(11) NOT NULL,
  `original_amount` int(11) NOT NULL,
  `remaining_amount` int(11) NOT NULL,
  `reason` varchar(512) DEFAULT '',
  `due_unix` int(11) NOT NULL,
  `created_unix` int(11) NOT NULL,
  `status` varchar(16) NOT NULL DEFAULT 'pending',
  `issue_receipt` tinyint(1) NOT NULL DEFAULT 0,
  `issue_self_receipt` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `citizenid_status` (`citizenid`,`status`),
  KEY `due_status` (`due_unix`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `finals_billing_deferred` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `citizenid` varchar(50) NOT NULL,
  `amount` int(11) NOT NULL,
  `reason` varchar(64) NOT NULL DEFAULT 'billing-income',
  PRIMARY KEY (`id`),
  KEY `citizenid` (`citizenid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;