-- Late renewal penalty master and immutable invoice penalty snapshot.
-- Import after 09-locker-releases-and-refund-types.sql.

CREATE TABLE IF NOT EXISTS `late_renewal_penalties` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `membership_plan_id` BIGINT UNSIGNED NOT NULL,
    `penalty_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `effective_from` DATE NOT NULL,
    `status` TINYINT(1) NOT NULL DEFAULT 1,
    `created_by` BIGINT UNSIGNED NULL,
    `updated_by` BIGINT UNSIGNED NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `late_renewal_penalties_plan_unique` (`membership_plan_id`),
    KEY `late_renewal_penalties_active_index` (`status`, `effective_from`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `invoices`
    ADD COLUMN IF NOT EXISTS `penalty_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER `discount_amount`,
    ADD COLUMN IF NOT EXISTS `days_overdue` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `penalty_amount`,
    ADD COLUMN IF NOT EXISTS `penalty_rule_id` BIGINT UNSIGNED NULL AFTER `days_overdue`;