-- migrate:up

ALTER TABLE task_categories
  ADD COLUMN version BIGINT UNSIGNED NOT NULL DEFAULT 1 AFTER status;

ALTER TABLE tasks
  ADD COLUMN version BIGINT UNSIGNED NOT NULL DEFAULT 1 AFTER status,
  ADD CONSTRAINT chk_tasks_attempt_window CHECK (attempt_expiry_seconds > wait_seconds);

ALTER TABLE task_attempts
  ADD COLUMN currency_id_snapshot BIGINT UNSIGNED NULL AFTER reward_minor_snapshot,
  ADD COLUMN task_type_snapshot ENUM('one_time', 'daily', 'repeatable') NULL AFTER verification_mode,
  ADD COLUMN task_title_snapshot VARCHAR(191) NULL AFTER task_type_snapshot,
  ADD COLUMN destination_url_snapshot VARCHAR(2048) NULL AFTER task_title_snapshot,
  ADD COLUMN browser_mode_snapshot ENUM('telegram', 'external') NULL AFTER destination_url_snapshot,
  ADD COLUMN daily_completion_limit_snapshot SMALLINT UNSIGNED NULL AFTER browser_mode_snapshot,
  ADD COLUMN repeat_cooldown_seconds_snapshot INT UNSIGNED NULL AFTER daily_completion_limit_snapshot,
  ADD COLUMN active_slot TINYINT GENERATED ALWAYS AS (
    CASE WHEN status IN ('started', 'eligible', 'verifying') THEN 1 ELSE NULL END
  ) STORED AFTER status;

UPDATE task_attempts ta
JOIN tasks t ON t.id = ta.task_id
SET
  ta.currency_id_snapshot = t.currency_id,
  ta.task_type_snapshot = t.task_type,
  ta.task_title_snapshot = t.title,
  ta.destination_url_snapshot = t.destination_url,
  ta.browser_mode_snapshot = t.browser_mode,
  ta.daily_completion_limit_snapshot = t.daily_completion_limit,
  ta.repeat_cooldown_seconds_snapshot = t.repeat_cooldown_seconds
WHERE ta.currency_id_snapshot IS NULL
  AND ta.task_type_snapshot IS NULL
  AND ta.task_title_snapshot IS NULL
  AND ta.destination_url_snapshot IS NULL
  AND ta.browser_mode_snapshot IS NULL
  AND ta.daily_completion_limit_snapshot IS NULL;

ALTER TABLE task_attempts
  MODIFY COLUMN verification_mode ENUM('timer', 'manual', 'callback', 'telegram_membership') NOT NULL,
  MODIFY COLUMN currency_id_snapshot BIGINT UNSIGNED NOT NULL,
  MODIFY COLUMN task_type_snapshot ENUM('one_time', 'daily', 'repeatable') NOT NULL,
  MODIFY COLUMN task_title_snapshot VARCHAR(191) NOT NULL,
  MODIFY COLUMN destination_url_snapshot VARCHAR(2048) NOT NULL,
  MODIFY COLUMN browser_mode_snapshot ENUM('telegram', 'external') NOT NULL,
  MODIFY COLUMN daily_completion_limit_snapshot SMALLINT UNSIGNED NOT NULL,
  ADD UNIQUE KEY uq_task_attempts_active (task_id, user_id, active_slot),
  ADD CONSTRAINT fk_task_attempts_currency_snapshot FOREIGN KEY (currency_id_snapshot) REFERENCES currencies (id) ON DELETE RESTRICT,
  ADD CONSTRAINT chk_task_attempts_snapshot_limits CHECK (
    daily_completion_limit_snapshot > 0 AND
    (
      (task_type_snapshot = 'repeatable' AND
        repeat_cooldown_seconds_snapshot IS NOT NULL AND repeat_cooldown_seconds_snapshot > 0) OR
      (task_type_snapshot <> 'repeatable' AND repeat_cooldown_seconds_snapshot IS NULL)
    )
  ),
  ADD CONSTRAINT chk_task_attempts_completion_time CHECK (
    (status = 'completed' AND completed_at IS NOT NULL) OR
    (status <> 'completed' AND completed_at IS NULL)
  );

ALTER TABLE task_completions
  ADD COLUMN claim_idempotency_key_hash BINARY(32) NULL AFTER ledger_transaction_id;

UPDATE task_completions
SET claim_idempotency_key_hash = UNHEX(SHA2(CONCAT('phase11:', public_id), 256))
WHERE claim_idempotency_key_hash IS NULL;

ALTER TABLE task_completions
  MODIFY COLUMN claim_idempotency_key_hash BINARY(32) NOT NULL,
  MODIFY COLUMN ledger_transaction_id BIGINT UNSIGNED NOT NULL,
  ADD UNIQUE KEY uq_task_completions_user_claim_key (user_id, claim_idempotency_key_hash),
  ADD INDEX idx_task_completions_task_user_time (task_id, user_id, completed_at, id);

-- migrate:down

ALTER TABLE task_completions
  DROP INDEX idx_task_completions_task_user_time,
  DROP INDEX uq_task_completions_user_claim_key,
  DROP COLUMN claim_idempotency_key_hash,
  MODIFY COLUMN ledger_transaction_id BIGINT UNSIGNED NULL;

ALTER TABLE task_attempts
  DROP CHECK chk_task_attempts_completion_time,
  DROP CHECK chk_task_attempts_snapshot_limits,
  DROP FOREIGN KEY fk_task_attempts_currency_snapshot,
  DROP INDEX uq_task_attempts_active,
  DROP COLUMN active_slot,
  DROP COLUMN repeat_cooldown_seconds_snapshot,
  DROP COLUMN daily_completion_limit_snapshot,
  DROP COLUMN browser_mode_snapshot,
  DROP COLUMN destination_url_snapshot,
  DROP COLUMN task_title_snapshot,
  DROP COLUMN task_type_snapshot,
  DROP COLUMN currency_id_snapshot,
  MODIFY COLUMN verification_mode VARCHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL;

ALTER TABLE tasks
  DROP CHECK chk_tasks_attempt_window,
  DROP COLUMN version;

ALTER TABLE task_categories
  DROP COLUMN version;