-- Individual locker release history and reusable refund ledger.
-- Import after 08-account-refunds.sql.

ALTER TABLE `account_refunds`
    ADD COLUMN IF NOT EXISTS `refund_type` VARCHAR(30) NOT NULL DEFAULT 'account_closure' AFTER `client_id`,
    ADD COLUMN IF NOT EXISTS `source_id` BIGINT UNSIGNED NULL AFTER `refund_type`,
    ADD COLUMN IF NOT EXISTS `locker_number` VARCHAR(100) NULL AFTER `source_id`;

SET @drop_client_unique = IF(
    EXISTS(
        SELECT 1 FROM information_schema.statistics
        WHERE table_schema = DATABASE()
          AND table_name = 'account_refunds'
          AND index_name = 'account_refunds_client_unique'
    ),
    'ALTER TABLE `account_refunds` DROP INDEX `account_refunds_client_unique`',
    'SELECT 1'
);
PREPARE drop_client_unique_stmt FROM @drop_client_unique;
EXECUTE drop_client_unique_stmt;
DEALLOCATE PREPARE drop_client_unique_stmt;

CREATE TABLE IF NOT EXISTS `locker_releases` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `client_id` BIGINT UNSIGNED NOT NULL,
    `registration_id` BIGINT UNSIGNED NOT NULL,
    `registration_item_id` BIGINT UNSIGNED NOT NULL,
    `locker_id` BIGINT UNSIGNED NOT NULL,
    `locker_number` VARCHAR(100) NOT NULL,
    `released_at` DATE NOT NULL,
    `reason` VARCHAR(500) NOT NULL,
    `key_returned` TINYINT(1) NOT NULL DEFAULT 1,
    `deposit_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `deposit_settlement` VARCHAR(30) NOT NULL DEFAULT 'refunded',
    `refund_id` BIGINT UNSIGNED NULL,
    `released_by` BIGINT UNSIGNED NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `locker_releases_item_unique` (`registration_item_id`),
    KEY `locker_releases_client_date_index` (`client_id`, `released_at`),
    KEY `locker_releases_locker_index` (`locker_id`),
    KEY `locker_releases_refund_index` (`refund_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @add_refund_source_unique = IF(
    EXISTS(
        SELECT 1 FROM information_schema.statistics
        WHERE table_schema = DATABASE()
          AND table_name = 'account_refunds'
          AND index_name = 'account_refunds_type_source_unique'
    ),
    'SELECT 1',
    'ALTER TABLE `account_refunds` ADD UNIQUE KEY `account_refunds_type_source_unique` (`refund_type`, `source_id`)'
);
PREPARE add_refund_source_unique_stmt FROM @add_refund_source_unique;
EXECUTE add_refund_source_unique_stmt;
DEALLOCATE PREPARE add_refund_source_unique_stmt;