-- migrate:up

CREATE TABLE admin_control_locks (
  id TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT chk_admin_control_lock_singleton CHECK (id = 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO admin_control_locks (id) VALUES (1);

ALTER TABLE admins
  ADD COLUMN version INT UNSIGNED NOT NULL DEFAULT 1,
  ADD COLUMN invitation_hash BINARY(32) NULL,
  ADD COLUMN invitation_expires_at DATETIME(6) NULL,
  ADD COLUMN enrollment_hash BINARY(32) NULL,
  ADD COLUMN enrollment_expires_at DATETIME(6) NULL,
  ADD COLUMN enrollment_failures TINYINT UNSIGNED NOT NULL DEFAULT 0,
  ADD UNIQUE KEY uq_admins_invitation_hash (invitation_hash),
  ADD UNIQUE KEY uq_admins_enrollment_hash (enrollment_hash);
ALTER TABLE admin_roles ADD COLUMN version INT UNSIGNED NOT NULL DEFAULT 1;
ALTER TABLE users ADD COLUMN control_version INT UNSIGNED NOT NULL DEFAULT 1;
ALTER TABLE fraud_flags ADD COLUMN version INT UNSIGNED NOT NULL DEFAULT 1;
ALTER TABLE failed_jobs
  ADD COLUMN version INT UNSIGNED NOT NULL DEFAULT 1,
  ADD COLUMN outbox_message_id BIGINT UNSIGNED NULL,
  ADD UNIQUE KEY uq_failed_jobs_outbox_attempt (outbox_message_id, attempt_count),
  ADD CONSTRAINT fk_failed_jobs_outbox FOREIGN KEY (outbox_message_id) REFERENCES outbox_messages (id) ON DELETE RESTRICT;

CREATE TABLE wallet_adjustment_requests (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  currency_id BIGINT UNSIGNED NOT NULL,
  request_key CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  direction ENUM('credit', 'debit') NOT NULL,
  amount_minor BIGINT UNSIGNED NOT NULL,
  reason VARCHAR(500) NOT NULL,
  status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
  version INT UNSIGNED NOT NULL DEFAULT 1,
  requested_by_admin_id BIGINT UNSIGNED NOT NULL,
  reviewed_by_admin_id BIGINT UNSIGNED NULL,
  review_note VARCHAR(500) NULL,
  ledger_transaction_id BIGINT UNSIGNED NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  reviewed_at DATETIME(6) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_wallet_adjustment_request_key (request_key),
  UNIQUE KEY uq_wallet_adjustment_public_id (public_id),
  UNIQUE KEY uq_wallet_adjustment_ledger (ledger_transaction_id),
  KEY idx_wallet_adjustment_queue (status, created_at, id),
  CONSTRAINT fk_wallet_adjustment_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT,
  CONSTRAINT fk_wallet_adjustment_currency FOREIGN KEY (currency_id) REFERENCES currencies (id) ON DELETE RESTRICT,
  CONSTRAINT fk_wallet_adjustment_requester FOREIGN KEY (requested_by_admin_id) REFERENCES admins (id) ON DELETE RESTRICT,
  CONSTRAINT fk_wallet_adjustment_reviewer FOREIGN KEY (reviewed_by_admin_id) REFERENCES admins (id) ON DELETE RESTRICT,
  CONSTRAINT fk_wallet_adjustment_ledger FOREIGN KEY (ledger_transaction_id) REFERENCES ledger_transactions (id) ON DELETE RESTRICT,
  CONSTRAINT chk_wallet_adjustment_amount CHECK (amount_minor > 0 AND amount_minor <= 9000000000000000000),
  CONSTRAINT chk_wallet_adjustment_dual_control CHECK (reviewed_by_admin_id IS NULL OR reviewed_by_admin_id <> requested_by_admin_id),
  CONSTRAINT chk_wallet_adjustment_state CHECK (
    (status = 'pending' AND reviewed_by_admin_id IS NULL AND reviewed_at IS NULL AND ledger_transaction_id IS NULL) OR
    (status = 'approved' AND reviewed_by_admin_id IS NOT NULL AND reviewed_at IS NOT NULL AND ledger_transaction_id IS NOT NULL) OR
    (status = 'rejected' AND reviewed_by_admin_id IS NOT NULL AND reviewed_at IS NOT NULL AND ledger_transaction_id IS NULL)
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO permissions (code, permission_group, description) VALUES
  ('wallet.adjust.approve', 'wallet', 'Approve or reject another administrator''s wallet adjustment.'),
  ('jobs.retry', 'administration', 'Requeue eligible failed Telegram deliveries with an audit reason.')
ON DUPLICATE KEY UPDATE description = VALUES(description);
INSERT IGNORE INTO admin_role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM admin_roles r CROSS JOIN permissions p
WHERE r.code = 'super_admin' AND p.code IN ('wallet.adjust.approve', 'jobs.retry');

INSERT IGNORE INTO failed_jobs (public_id, queue_name, job_name, job_reference, error_code, attempt_count, outbox_message_id)
SELECT o.public_id, 'outbox', o.topic, o.public_id, o.last_error_code, o.attempt_count, o.id
FROM outbox_messages o WHERE o.status = 'failed';

-- migrate:down

DROP TABLE IF EXISTS wallet_adjustment_requests;
ALTER TABLE failed_jobs DROP FOREIGN KEY fk_failed_jobs_outbox,
  DROP INDEX uq_failed_jobs_outbox_attempt, DROP COLUMN outbox_message_id, DROP COLUMN version;
ALTER TABLE fraud_flags DROP COLUMN version;
ALTER TABLE users DROP COLUMN control_version;
ALTER TABLE admin_roles DROP COLUMN version;
ALTER TABLE admins DROP INDEX uq_admins_invitation_hash, DROP INDEX uq_admins_enrollment_hash,
  DROP COLUMN invitation_hash, DROP COLUMN invitation_expires_at,
  DROP COLUMN enrollment_hash, DROP COLUMN enrollment_expires_at,
  DROP COLUMN enrollment_failures, DROP COLUMN version;
DROP TABLE IF EXISTS admin_control_locks;
DELETE FROM permissions WHERE code IN ('wallet.adjust.approve', 'jobs.retry');