-- Ghim giao dịch vào đúng tháng đã chọn, lưu snapshot biên lai và chống gửi trùng.
-- Chạy một lần trên đúng database website; có thể chạy lại an toàn.
CREATE TABLE IF NOT EXISTS `tuition_payment_meta_tb` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `payment_id` INT UNSIGNED NOT NULL,
    `request_token` VARCHAR(80) NOT NULL,
    `snapshot_total_fee` DECIMAL(12,0) NOT NULL DEFAULT 0,
    `balance_after` DECIMAL(12,0) NOT NULL DEFAULT 0,
    `content` VARCHAR(500) NULL,
    `date_created` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_tuition_payment_meta_payment` (`payment_id`),
    UNIQUE KEY `uq_tuition_payment_meta_request` (`request_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tuition_payment_allocation_tb` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `payment_id` INT UNSIGNED NOT NULL,
    `class_id` INT UNSIGNED NOT NULL,
    `student_id` INT UNSIGNED NOT NULL,
    `period_due_date` DATE NOT NULL,
    `amount` DECIMAL(12,0) NOT NULL DEFAULT 0,
    `date_created` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_tuition_payment_allocation` (`payment_id`, `period_due_date`),
    KEY `idx_tuition_allocation_student_period` (`class_id`, `student_id`, `period_due_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `student_tuition_period_override_history_tb` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `override_id` INT UNSIGNED NOT NULL,
    `class_id` INT UNSIGNED NOT NULL,
    `student_id` INT UNSIGNED NOT NULL,
    `period_due_date` DATE NOT NULL,
    `old_amount` DECIMAL(12,0) NOT NULL DEFAULT 0,
    `new_amount` DECIMAL(12,0) NOT NULL DEFAULT 0,
    `reason` VARCHAR(500) NOT NULL,
    `user_created` VARCHAR(255) NULL,
    `date_created` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_tuition_override_history` (`class_id`, `student_id`, `period_due_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;