-- Locker key-deposit ledger and locker-change financial links.
-- Import after 13-update_balance_amount_in_invoice.sql.

CREATE TABLE IF NOT EXISTS `locker_deposit_transactions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `client_id` BIGINT UNSIGNED NOT NULL,
    `locker_id` BIGINT UNSIGNED NULL,
    `registration_id` BIGINT UNSIGNED NULL,
    `registration_item_id` BIGINT UNSIGNED NULL,
    `related_item_id` BIGINT UNSIGNED NULL,
    `transaction_type` VARCHAR(30) NOT NULL,
    `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `status` VARCHAR(20) NOT NULL DEFAULT 'completed',
    `source_type` VARCHAR(40) NOT NULL,
    `source_id` BIGINT UNSIGNED NULL,
    `invoice_id` BIGINT UNSIGNED NULL,
    `refund_id` BIGINT UNSIGNED NULL,
    `idempotency_key` VARCHAR(150) NOT NULL,
    `notes` VARCHAR(500) NULL,
    `transacted_at` DATETIME NOT NULL,
    `created_by` BIGINT UNSIGNED NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `locker_deposit_transactions_idempotency_unique` (`idempotency_key`),
    KEY `locker_deposit_transactions_item_status_index` (`registration_item_id`, `status`),
    KEY `locker_deposit_transactions_client_date_index` (`client_id`, `transacted_at`),
    KEY `locker_deposit_transactions_invoice_index` (`invoice_id`),
    KEY `locker_deposit_transactions_refund_index` (`refund_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `locker_changes`
    ADD COLUMN IF NOT EXISTS `old_item_id` BIGINT UNSIGNED NULL AFTER `client_id`,
    ADD COLUMN IF NOT EXISTS `new_item_id` BIGINT UNSIGNED NULL AFTER `old_item_id`,
    ADD COLUMN IF NOT EXISTS `invoice_id` BIGINT UNSIGNED NULL AFTER `new_item_id`,
    ADD COLUMN IF NOT EXISTS `refund_id` BIGINT UNSIGNED NULL AFTER `invoice_id`,
    ADD COLUMN IF NOT EXISTS `key_returned` TINYINT(1) NOT NULL DEFAULT 1 AFTER `refund_id`,
    ADD COLUMN IF NOT EXISTS `new_key_deposit` DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER `old_key_deposit`,
    ADD COLUMN IF NOT EXISTS `deposit_transferred` DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER `new_key_deposit`,
    ADD COLUMN IF NOT EXISTS `deposit_adjustment` DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER `deposit_transferred`,
    ADD COLUMN IF NOT EXISTS `deposit_settlement` VARCHAR(30) NULL AFTER `deposit_adjustment`;

-- Deterministic legacy backfill: when a registration has exactly one active
-- locker, its registration deposit belongs entirely to that locker.
UPDATE `locker_registration_items` item
INNER JOIN `locker_registrations` registration ON registration.id = item.registration_id
INNER JOIN (
    SELECT registration_id
    FROM `locker_registration_items`
    WHERE LOWER(TRIM(status)) = 'active'
    GROUP BY registration_id
    HAVING COUNT(*) = 1
) single_item ON single_item.registration_id = item.registration_id
SET item.key_deposit = registration.key_deposit
WHERE LOWER(TRIM(item.status)) = 'active'
  AND COALESCE(item.key_deposit, 0) = 0
  AND COALESCE(registration.key_deposit, 0) > 0;

UPDATE `locker_registration_items` item
INNER JOIN `locker_registrations` registration ON registration.id = item.registration_id
INNER JOIN (
    SELECT registration_id
    FROM `locker_registration_items`
    WHERE LOWER(TRIM(status)) = 'active'
    GROUP BY registration_id
    HAVING COUNT(*) = 1
) single_item ON single_item.registration_id = item.registration_id
SET item.stamping_fee = CASE
        WHEN COALESCE(item.stamping_fee, 0) = 0 THEN COALESCE(registration.stamping_fee, 0)
        ELSE item.stamping_fee END,
    item.insurance_charges = CASE
        WHEN COALESCE(item.insurance_charges, 0) = 0 THEN COALESCE(registration.insurance_charges, 0)
        ELSE item.insurance_charges END,
    item.discount_amount = CASE
        WHEN COALESCE(item.discount_amount, 0) = 0 THEN COALESCE(registration.discount_amount, 0)
        ELSE item.discount_amount END
WHERE LOWER(TRIM(item.status)) = 'active'
  AND (
      (COALESCE(item.stamping_fee, 0) = 0 AND COALESCE(registration.stamping_fee, 0) > 0)
      OR (COALESCE(item.insurance_charges, 0) = 0 AND COALESCE(registration.insurance_charges, 0) > 0)
      OR (COALESCE(item.discount_amount, 0) = 0 AND COALESCE(registration.discount_amount, 0) > 0)
  );