-- migrate:up

ALTER TABLE mystery_boxes
  ADD COLUMN active_slot TINYINT
    GENERATED ALWAYS AS (CASE WHEN status = 'active' THEN 1 ELSE NULL END) STORED
    AFTER status,
  ADD COLUMN version BIGINT UNSIGNED NOT NULL DEFAULT 1 AFTER active_slot,
  ADD COLUMN updated_by_admin_id BIGINT UNSIGNED NULL AFTER created_by_admin_id,
  ADD UNIQUE KEY uq_mystery_boxes_active_slot (active_slot),
  ADD CONSTRAINT fk_mystery_boxes_updater
    FOREIGN KEY (updated_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  ADD CONSTRAINT chk_mystery_boxes_version CHECK (version > 0);

ALTER TABLE mystery_box_rewards
  ADD COLUMN version BIGINT UNSIGNED NOT NULL DEFAULT 1 AFTER status,
  ADD COLUMN created_by_admin_id BIGINT UNSIGNED NULL AFTER version,
  ADD COLUMN updated_by_admin_id BIGINT UNSIGNED NULL AFTER created_by_admin_id,
  ADD CONSTRAINT fk_mystery_rewards_creator
    FOREIGN KEY (created_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  ADD CONSTRAINT fk_mystery_rewards_updater
    FOREIGN KEY (updated_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  ADD CONSTRAINT chk_mystery_rewards_version CHECK (version > 0);

ALTER TABLE mystery_box_claims
  ADD COLUMN box_version_snapshot BIGINT UNSIGNED NOT NULL DEFAULT 1
    AFTER currency_code_snapshot,
  ADD COLUMN reward_version_snapshot BIGINT UNSIGNED NOT NULL DEFAULT 1
    AFTER box_version_snapshot,
  ADD COLUMN frequency_snapshot ENUM('daily', 'weekly', 'lifetime', 'cooldown')
    NOT NULL DEFAULT 'lifetime' AFTER reward_version_snapshot,
  ADD COLUMN required_tasks_snapshot INT UNSIGNED NOT NULL DEFAULT 0
    AFTER frequency_snapshot,
  ADD COLUMN completed_tasks_snapshot BIGINT UNSIGNED NOT NULL DEFAULT 0
    AFTER required_tasks_snapshot,
  ADD COLUMN opportunity_started_at DATETIME(6) NULL AFTER completed_tasks_snapshot,
  ADD COLUMN opportunity_ends_at DATETIME(6) NULL AFTER opportunity_started_at,
  ADD KEY idx_mystery_claims_box_user_time
    (mystery_box_id, user_id, claimed_at, id),
  ADD CONSTRAINT chk_mystery_claims_versions CHECK (
    box_version_snapshot > 0 AND reward_version_snapshot > 0
  ),
  ADD CONSTRAINT chk_mystery_claims_task_snapshot CHECK (
    completed_tasks_snapshot >= required_tasks_snapshot
  ),
  ADD CONSTRAINT chk_mystery_claims_opportunity_dates CHECK (
    opportunity_ends_at IS NULL OR opportunity_started_at IS NULL OR
    opportunity_ends_at > opportunity_started_at
  );

CREATE TRIGGER trg_mystery_box_claims_no_update
BEFORE UPDATE ON mystery_box_claims
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Mystery Box claims are immutable';

CREATE TRIGGER trg_mystery_box_claims_no_delete
BEFORE DELETE ON mystery_box_claims
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Mystery Box claims cannot be deleted';

INSERT IGNORE INTO permissions (code, permission_group, description) VALUES
  ('mystery_box.publish', 'mystery_box', 'Publish, pause, end, or archive free Mystery Boxes.'),
  ('mystery_box.claims.view', 'mystery_box', 'View free Mystery Box claim evidence.');

INSERT IGNORE INTO admin_role_permissions (role_id, permission_id)
SELECT role.id, permission.id
FROM admin_roles AS role
JOIN permissions AS permission
  ON permission.code IN ('mystery_box.publish', 'mystery_box.claims.view')
WHERE role.code IN ('super_admin', 'operations_manager');

-- migrate:down

DELETE FROM permissions
WHERE code IN ('mystery_box.publish', 'mystery_box.claims.view');
DROP TRIGGER IF EXISTS trg_mystery_box_claims_no_delete;
DROP TRIGGER IF EXISTS trg_mystery_box_claims_no_update;
ALTER TABLE mystery_box_claims
  DROP CHECK chk_mystery_claims_opportunity_dates,
  DROP CHECK chk_mystery_claims_task_snapshot,
  DROP CHECK chk_mystery_claims_versions,
  DROP INDEX idx_mystery_claims_box_user_time,
  DROP COLUMN opportunity_ends_at,
  DROP COLUMN opportunity_started_at,
  DROP COLUMN completed_tasks_snapshot,
  DROP COLUMN required_tasks_snapshot,
  DROP COLUMN frequency_snapshot,
  DROP COLUMN reward_version_snapshot,
  DROP COLUMN box_version_snapshot;
ALTER TABLE mystery_box_rewards
  DROP CHECK chk_mystery_rewards_version,
  DROP FOREIGN KEY fk_mystery_rewards_updater,
  DROP FOREIGN KEY fk_mystery_rewards_creator,
  DROP COLUMN updated_by_admin_id,
  DROP COLUMN created_by_admin_id,
  DROP COLUMN version;
ALTER TABLE mystery_boxes
  DROP CHECK chk_mystery_boxes_version,
  DROP FOREIGN KEY fk_mystery_boxes_updater,
  DROP INDEX uq_mystery_boxes_active_slot,
  DROP COLUMN updated_by_admin_id,
  DROP COLUMN version,
  DROP COLUMN active_slot;