-- migrate:up

CREATE TABLE wallet_accounts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  owner_type ENUM('user', 'system') NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  system_key VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NULL,
  account_type ENUM('available', 'held', 'reward_pool', 'referral_pool', 'mystery_pool', 'payout', 'adjustment') NOT NULL,
  currency_id BIGINT UNSIGNED NOT NULL,
  status ENUM('active', 'frozen', 'closed') NOT NULL DEFAULT 'active',
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_wallet_accounts_public_id (public_id),
  UNIQUE KEY uq_wallet_accounts_user_type (user_id, account_type, currency_id),
  UNIQUE KEY uq_wallet_accounts_system_type (system_key, account_type, currency_id),
  KEY idx_wallet_accounts_owner (owner_type, user_id, status),
  CONSTRAINT fk_wallet_accounts_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT,
  CONSTRAINT fk_wallet_accounts_currency FOREIGN KEY (currency_id) REFERENCES currencies (id) ON DELETE RESTRICT,
  CONSTRAINT chk_wallet_accounts_owner CHECK (
    (owner_type = 'user' AND user_id IS NOT NULL AND system_key IS NULL) OR
    (owner_type = 'system' AND user_id IS NULL AND system_key IS NOT NULL)
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE ledger_transactions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  transaction_type ENUM('task_reward', 'referral_reward', 'mystery_reward', 'withdrawal_hold', 'withdrawal_payout', 'withdrawal_refund', 'manual_adjustment', 'reversal') NOT NULL,
  status ENUM('pending', 'posted', 'reversed') NOT NULL DEFAULT 'pending',
  currency_id BIGINT UNSIGNED NOT NULL,
  reference_type VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  reference_id VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  idempotency_key VARCHAR(191) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  reversal_of_transaction_id BIGINT UNSIGNED NULL,
  description VARCHAR(500) NOT NULL,
  metadata JSON NULL,
  created_by_admin_id BIGINT UNSIGNED NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  posted_at DATETIME(6) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ledger_transactions_public_id (public_id),
  UNIQUE KEY uq_ledger_transactions_idempotency (idempotency_key),
  UNIQUE KEY uq_ledger_transactions_reversal (reversal_of_transaction_id),
  KEY idx_ledger_transactions_reference (reference_type, reference_id),
  KEY idx_ledger_transactions_status_time (status, created_at),
  CONSTRAINT fk_ledger_transactions_currency FOREIGN KEY (currency_id) REFERENCES currencies (id) ON DELETE RESTRICT,
  CONSTRAINT fk_ledger_transactions_reversal FOREIGN KEY (reversal_of_transaction_id) REFERENCES ledger_transactions (id) ON DELETE RESTRICT,
  CONSTRAINT fk_ledger_transactions_admin FOREIGN KEY (created_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE ledger_entries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  transaction_id BIGINT UNSIGNED NOT NULL,
  account_id BIGINT UNSIGNED NOT NULL,
  entry_side ENUM('debit', 'credit') NOT NULL,
  amount_minor BIGINT UNSIGNED NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_ledger_entries_transaction_account_side (transaction_id, account_id, entry_side),
  KEY idx_ledger_entries_account_time (account_id, created_at, id),
  CONSTRAINT fk_ledger_entries_transaction FOREIGN KEY (transaction_id) REFERENCES ledger_transactions (id) ON DELETE RESTRICT,
  CONSTRAINT fk_ledger_entries_account FOREIGN KEY (account_id) REFERENCES wallet_accounts (id) ON DELETE RESTRICT,
  CONSTRAINT chk_ledger_entries_amount CHECK (amount_minor BETWEEN 1 AND 9000000000000000000)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE ledger_transaction_approvals (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  transaction_id BIGINT UNSIGNED NOT NULL,
  admin_id BIGINT UNSIGNED NOT NULL,
  decision ENUM('approve', 'reject') NOT NULL,
  reason VARCHAR(1000) NOT NULL,
  decided_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_ledger_transaction_approvals_actor (transaction_id, admin_id),
  CONSTRAINT fk_ledger_transaction_approvals_tx FOREIGN KEY (transaction_id) REFERENCES ledger_transactions (id) ON DELETE RESTRICT,
  CONSTRAINT fk_ledger_transaction_approvals_admin FOREIGN KEY (admin_id) REFERENCES admins (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE idempotency_keys (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  scope VARCHAR(80) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  actor_type ENUM('user', 'admin', 'provider', 'system') NOT NULL,
  actor_reference VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  key_hash BINARY(32) NOT NULL,
  request_hash BINARY(32) NOT NULL,
  status ENUM('processing', 'completed', 'failed') NOT NULL DEFAULT 'processing',
  response_code SMALLINT UNSIGNED NULL,
  response_body JSON NULL,
  locked_until DATETIME(6) NULL,
  expires_at DATETIME(6) NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_idempotency_scope_actor_key (scope, actor_type, actor_reference, key_hash),
  KEY idx_idempotency_expiry (expires_at, status),
  CONSTRAINT chk_idempotency_expiry CHECK (expires_at > created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE wallet_balance_snapshots (
  user_id BIGINT UNSIGNED NOT NULL,
  currency_id BIGINT UNSIGNED NOT NULL,
  available_minor BIGINT NOT NULL DEFAULT 0,
  held_minor BIGINT NOT NULL DEFAULT 0,
  lifetime_earned_minor BIGINT NOT NULL DEFAULT 0,
  last_ledger_entry_id BIGINT UNSIGNED NULL,
  rebuilt_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (user_id, currency_id),
  CONSTRAINT fk_wallet_balance_snapshots_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT,
  CONSTRAINT fk_wallet_balance_snapshots_currency FOREIGN KEY (currency_id) REFERENCES currencies (id) ON DELETE RESTRICT,
  CONSTRAINT fk_wallet_balance_snapshots_entry FOREIGN KEY (last_ledger_entry_id) REFERENCES ledger_entries (id) ON DELETE RESTRICT,
  CONSTRAINT chk_wallet_balance_snapshots_nonnegative CHECK (
    available_minor >= 0 AND held_minor >= 0 AND lifetime_earned_minor >= 0
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- migrate:down

DROP TABLE IF EXISTS wallet_balance_snapshots;
DROP TABLE IF EXISTS idempotency_keys;
DROP TABLE IF EXISTS ledger_transaction_approvals;
DROP TABLE IF EXISTS ledger_entries;
DROP TABLE IF EXISTS ledger_transactions;
DROP TABLE IF EXISTS wallet_accounts;