-- Table to store LL Exam applications
-- NOTE: `password` is stored in plaintext (required to resubmit/verify against the
-- upstream if needed) - same tradeoff noted in the other LL_Exam project's docs.
-- Consider encrypting or purging it after the application reaches a terminal
-- status (COMPLETED/REJECTED) if this becomes a concern.

CREATE TABLE IF NOT EXISTS `ll_exam` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `application_no` VARCHAR(100) NOT NULL COMMENT 'this IS the order_id returned by the submit API - no separate local id needed',
  `username` VARCHAR(20) NOT NULL,
  `appno` VARCHAR(50) NOT NULL COMMENT 'the LL exam application number the applicant submitted',
  `password` VARCHAR(50) NOT NULL,
  `dob` VARCHAR(15) DEFAULT NULL,

  `status` VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING, COMPLETED, or REJECTED',
  `remark` VARCHAR(255) DEFAULT NULL COMMENT 'reject reason, only set once status=REJECTED',
  `attachment_url` VARCHAR(255) DEFAULT NULL COMMENT 'result/admit-card attachment, only set once status=COMPLETED',
  `is_refunded` TINYINT(1) NOT NULL DEFAULT 0,
  `refund_txn_id` VARCHAR(100) DEFAULT NULL,
  `refund_date` DATE DEFAULT NULL,
  `last_checked_at` DATETIME DEFAULT NULL COMMENT 'when the cron last polled the status-check API for this order',

  `status_code` TEXT COMMENT 'raw API response from the submit call',
  `fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `old_balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `new_balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `message` VARCHAR(255) DEFAULT NULL,
  `date` DATETIME NOT NULL,

  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_application_no` (`application_no`),
  KEY `idx_username` (`username`),
  KEY `idx_status` (`status`),
  KEY `idx_date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Pricing: rate = 60 as specified
INSERT INTO `pricing` (`service_name`, `price`) VALUES ('ll_exam', 60.00)
ON DUPLICATE KEY UPDATE price = 60.00;