-- migrate:up

CREATE TABLE withdrawal_methods (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  currency_id BIGINT UNSIGNED NOT NULL,
  code VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  name VARCHAR(120) NOT NULL,
  icon_reference VARCHAR(255) NULL,
  instructions TEXT NULL,
  processing_mode ENUM('manual', 'automatic') NOT NULL DEFAULT 'manual',
  fee_type ENUM('fixed', 'percentage', 'fixed_plus_percentage') NOT NULL DEFAULT 'fixed',
  fixed_fee_minor BIGINT UNSIGNED NOT NULL DEFAULT 0,
  percentage_fee_bps SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  minimum_minor BIGINT UNSIGNED NOT NULL,
  maximum_minor BIGINT UNSIGNED NOT NULL,
  daily_request_limit SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  daily_amount_limit_minor BIGINT UNSIGNED NULL,
  cooldown_seconds INT UNSIGNED NOT NULL DEFAULT 0,
  minimum_account_age_seconds BIGINT UNSIGNED NOT NULL DEFAULT 0,
  minimum_completed_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  required_active_referrals INT UNSIGNED NOT NULL DEFAULT 0,
  display_order INT NOT NULL DEFAULT 0,
  status ENUM('draft', 'active', 'inactive') NOT NULL DEFAULT 'draft',
  provider_code VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NULL,
  created_by_admin_id BIGINT UNSIGNED 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_withdrawal_methods_public_id (public_id),
  UNIQUE KEY uq_withdrawal_methods_code (code),
  KEY idx_withdrawal_methods_listing (status, display_order),
  CONSTRAINT fk_withdrawal_methods_currency FOREIGN KEY (currency_id) REFERENCES currencies (id) ON DELETE RESTRICT,
  CONSTRAINT fk_withdrawal_methods_creator FOREIGN KEY (created_by_admin_id) REFERENCES admins (id) ON DELETE RESTRICT,
  CONSTRAINT chk_withdrawal_methods_range CHECK (minimum_minor > 0 AND maximum_minor >= minimum_minor),
  CONSTRAINT chk_withdrawal_methods_bps CHECK (percentage_fee_bps <= 10000),
  CONSTRAINT chk_withdrawal_methods_daily CHECK (daily_request_limit > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE withdrawal_method_fields (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  withdrawal_method_id BIGINT UNSIGNED NOT NULL,
  field_key VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  label VARCHAR(120) NOT NULL,
  field_type ENUM('text', 'tel', 'email', 'select') NOT NULL,
  validation_rules JSON NOT NULL,
  options_json JSON NULL,
  is_required TINYINT(1) NOT NULL DEFAULT 1,
  is_sensitive TINYINT(1) NOT NULL DEFAULT 1,
  display_order INT NOT NULL DEFAULT 0,
  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_withdrawal_method_fields_key (withdrawal_method_id, field_key),
  KEY idx_withdrawal_method_fields_order (withdrawal_method_id, display_order),
  CONSTRAINT fk_withdrawal_method_fields_method FOREIGN KEY (withdrawal_method_id) REFERENCES withdrawal_methods (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE withdrawal_quotes (
  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,
  withdrawal_method_id BIGINT UNSIGNED NOT NULL,
  currency_id BIGINT UNSIGNED NOT NULL,
  gross_amount_minor BIGINT UNSIGNED NOT NULL,
  fee_amount_minor BIGINT UNSIGNED NOT NULL,
  net_amount_minor BIGINT UNSIGNED NOT NULL,
  rule_snapshot JSON NOT NULL,
  expires_at DATETIME(6) NOT NULL,
  consumed_at DATETIME(6) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_withdrawal_quotes_public_id (public_id),
  KEY idx_withdrawal_quotes_user_expiry (user_id, expires_at, consumed_at),
  CONSTRAINT fk_withdrawal_quotes_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT,
  CONSTRAINT fk_withdrawal_quotes_method FOREIGN KEY (withdrawal_method_id) REFERENCES withdrawal_methods (id) ON DELETE RESTRICT,
  CONSTRAINT fk_withdrawal_quotes_currency FOREIGN KEY (currency_id) REFERENCES currencies (id) ON DELETE RESTRICT,
  CONSTRAINT chk_withdrawal_quotes_amounts CHECK (
    gross_amount_minor > 0 AND fee_amount_minor < gross_amount_minor AND net_amount_minor = gross_amount_minor - fee_amount_minor
  ),
  CONSTRAINT chk_withdrawal_quotes_expiry CHECK (expires_at > created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE withdrawal_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,
  withdrawal_method_id BIGINT UNSIGNED NOT NULL,
  quote_id BIGINT UNSIGNED NOT NULL,
  currency_id BIGINT UNSIGNED NOT NULL,
  gross_amount_minor BIGINT UNSIGNED NOT NULL,
  fee_amount_minor BIGINT UNSIGNED NOT NULL,
  net_amount_minor BIGINT UNSIGNED NOT NULL,
  account_details_encrypted VARBINARY(4096) NOT NULL,
  account_details_key_version VARCHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  account_display_mask VARCHAR(191) NOT NULL,
  idempotency_key_hash BINARY(32) NOT NULL,
  status ENUM('requested', 'under_review', 'held', 'approved', 'processing', 'paid', 'rejected', 'cancelled', 'failed') NOT NULL DEFAULT 'requested',
  hold_ledger_transaction_id BIGINT UNSIGNED NOT NULL,
  payout_ledger_transaction_id BIGINT UNSIGNED NULL,
  refund_ledger_transaction_id BIGINT UNSIGNED NULL,
  risk_snapshot JSON NULL,
  decision_note VARCHAR(1000) NULL,
  requested_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  status_changed_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  completed_at DATETIME(6) 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_withdrawal_requests_public_id (public_id),
  UNIQUE KEY uq_withdrawal_requests_quote (quote_id),
  UNIQUE KEY uq_withdrawal_requests_user_idempotency (user_id, idempotency_key_hash),
  UNIQUE KEY uq_withdrawal_requests_hold_tx (hold_ledger_transaction_id),
  UNIQUE KEY uq_withdrawal_requests_payout_tx (payout_ledger_transaction_id),
  UNIQUE KEY uq_withdrawal_requests_refund_tx (refund_ledger_transaction_id),
  KEY idx_withdrawal_requests_user_time (user_id, requested_at, id),
  KEY idx_withdrawal_requests_queue (status, requested_at, id),
  CONSTRAINT fk_withdrawal_requests_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT,
  CONSTRAINT fk_withdrawal_requests_method FOREIGN KEY (withdrawal_method_id) REFERENCES withdrawal_methods (id) ON DELETE RESTRICT,
  CONSTRAINT fk_withdrawal_requests_quote FOREIGN KEY (quote_id) REFERENCES withdrawal_quotes (id) ON DELETE RESTRICT,
  CONSTRAINT fk_withdrawal_requests_currency FOREIGN KEY (currency_id) REFERENCES currencies (id) ON DELETE RESTRICT,
  CONSTRAINT fk_withdrawal_requests_hold_tx FOREIGN KEY (hold_ledger_transaction_id) REFERENCES ledger_transactions (id) ON DELETE RESTRICT,
  CONSTRAINT fk_withdrawal_requests_payout_tx FOREIGN KEY (payout_ledger_transaction_id) REFERENCES ledger_transactions (id) ON DELETE RESTRICT,
  CONSTRAINT fk_withdrawal_requests_refund_tx FOREIGN KEY (refund_ledger_transaction_id) REFERENCES ledger_transactions (id) ON DELETE RESTRICT,
  CONSTRAINT chk_withdrawal_requests_amounts CHECK (
    gross_amount_minor > 0 AND fee_amount_minor < gross_amount_minor AND net_amount_minor = gross_amount_minor - fee_amount_minor
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE withdrawal_status_history (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  withdrawal_request_id BIGINT UNSIGNED NOT NULL,
  from_status VARCHAR(32) CHARACTER SET ascii COLLATE ascii_bin NULL,
  to_status VARCHAR(32) 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,
  reason_code VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NULL,
  note VARCHAR(1000) NULL,
  metadata JSON NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  KEY idx_withdrawal_status_history_request (withdrawal_request_id, created_at, id),
  CONSTRAINT fk_withdrawal_status_history_request FOREIGN KEY (withdrawal_request_id) REFERENCES withdrawal_requests (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE withdrawal_approvals (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  withdrawal_request_id BIGINT UNSIGNED NOT NULL,
  admin_id BIGINT UNSIGNED NOT NULL,
  decision ENUM('approve', 'reject', 'hold') NOT NULL,
  note VARCHAR(1000) NOT NULL,
  decided_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_withdrawal_approvals_actor (withdrawal_request_id, admin_id),
  CONSTRAINT fk_withdrawal_approvals_request FOREIGN KEY (withdrawal_request_id) REFERENCES withdrawal_requests (id) ON DELETE RESTRICT,
  CONSTRAINT fk_withdrawal_approvals_admin FOREIGN KEY (admin_id) REFERENCES admins (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE payout_transactions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  withdrawal_request_id BIGINT UNSIGNED NOT NULL,
  provider_code VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  provider_reference VARCHAR(191) CHARACTER SET ascii COLLATE ascii_bin NULL,
  amount_minor BIGINT UNSIGNED NOT NULL,
  currency_code CHAR(3) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  status ENUM('created', 'submitted', 'confirmed', 'failed', 'reversed') NOT NULL DEFAULT 'created',
  request_fingerprint BINARY(32) NOT NULL,
  response_summary JSON NULL,
  submitted_at DATETIME(6) NULL,
  confirmed_at DATETIME(6) 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_payout_transactions_public_id (public_id),
  UNIQUE KEY uq_payout_transactions_provider_reference (provider_code, provider_reference),
  UNIQUE KEY uq_payout_transactions_request_fingerprint (request_fingerprint),
  KEY idx_payout_transactions_withdrawal (withdrawal_request_id, created_at),
  CONSTRAINT fk_payout_transactions_request FOREIGN KEY (withdrawal_request_id) REFERENCES withdrawal_requests (id) ON DELETE RESTRICT,
  CONSTRAINT chk_payout_transactions_amount CHECK (amount_minor > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE payout_provider_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  provider_code VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  provider_event_key VARCHAR(191) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  payout_transaction_id BIGINT UNSIGNED NULL,
  payload_hash BINARY(32) NOT NULL,
  signature_valid TINYINT(1) NOT NULL DEFAULT 0,
  processing_status ENUM('received', 'processed', 'ignored', 'failed') NOT NULL DEFAULT 'received',
  payload_summary JSON NULL,
  received_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  processed_at DATETIME(6) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_payout_provider_events_event (provider_code, provider_event_key),
  KEY idx_payout_provider_events_status (processing_status, received_at),
  CONSTRAINT fk_payout_provider_events_transaction FOREIGN KEY (payout_transaction_id) REFERENCES payout_transactions (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- migrate:down

DROP TABLE IF EXISTS payout_provider_events;
DROP TABLE IF EXISTS payout_transactions;
DROP TABLE IF EXISTS withdrawal_approvals;
DROP TABLE IF EXISTS withdrawal_status_history;
DROP TABLE IF EXISTS withdrawal_requests;
DROP TABLE IF EXISTS withdrawal_quotes;
DROP TABLE IF EXISTS withdrawal_method_fields;
DROP TABLE IF EXISTS withdrawal_methods;