-- migrate:up

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

ALTER TABLE withdrawal_method_fields
  ADD COLUMN version BIGINT UNSIGNED NOT NULL DEFAULT 1 AFTER display_order,
  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_withdrawal_fields_creator
    FOREIGN KEY (created_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  ADD CONSTRAINT fk_withdrawal_fields_updater
    FOREIGN KEY (updated_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  ADD CONSTRAINT chk_withdrawal_fields_version CHECK (version > 0);

ALTER TABLE withdrawal_quotes
  ADD COLUMN request_fingerprint BINARY(32) NULL AFTER public_id,
  ADD COLUMN method_version_snapshot BIGINT UNSIGNED NOT NULL DEFAULT 1 AFTER rule_snapshot,
  ADD COLUMN currency_code_snapshot CHAR(3) CHARACTER SET ascii COLLATE ascii_bin NULL
    AFTER method_version_snapshot,
  ADD COLUMN currency_decimals_snapshot TINYINT UNSIGNED NULL AFTER currency_code_snapshot,
  ADD KEY idx_withdrawal_quotes_fingerprint (user_id, request_fingerprint),
  ADD CONSTRAINT chk_withdrawal_quotes_method_version CHECK (method_version_snapshot > 0),
  ADD CONSTRAINT chk_withdrawal_quotes_decimals CHECK (
    currency_decimals_snapshot IS NULL OR currency_decimals_snapshot <= 8
  );

ALTER TABLE withdrawal_requests
  ADD COLUMN method_code_snapshot VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NULL
    AFTER currency_id,
  ADD COLUMN method_name_snapshot VARCHAR(120) NULL AFTER method_code_snapshot,
  ADD COLUMN processing_mode_snapshot ENUM('manual', 'automatic') NOT NULL DEFAULT 'manual'
    AFTER method_name_snapshot,
  ADD COLUMN account_details_hash BINARY(32) NULL AFTER account_details_key_version,
  ADD COLUMN request_fingerprint BINARY(32) NULL AFTER idempotency_key_hash,
  ADD COLUMN version BIGINT UNSIGNED NOT NULL DEFAULT 1 AFTER status,
  ADD COLUMN required_approval_count TINYINT UNSIGNED NOT NULL DEFAULT 1 AFTER version,
  ADD COLUMN approval_count TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER required_approval_count,
  ADD COLUMN reviewed_by_admin_id BIGINT UNSIGNED NULL AFTER decision_note,
  ADD COLUMN approved_at DATETIME(6) NULL AFTER status_changed_at,
  ADD KEY idx_withdrawal_requests_user_day (user_id, requested_at, status),
  ADD KEY idx_withdrawal_requests_fingerprint (user_id, request_fingerprint),
  ADD CONSTRAINT fk_withdrawal_requests_reviewer
    FOREIGN KEY (reviewed_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  ADD CONSTRAINT chk_withdrawal_requests_version CHECK (version > 0),
  ADD CONSTRAINT chk_withdrawal_requests_approvals CHECK (
    required_approval_count IN (1, 2) AND approval_count <= required_approval_count
  ),
  ADD CONSTRAINT chk_withdrawal_requests_completion CHECK (
    (status IN ('paid', 'rejected', 'cancelled', 'failed') AND completed_at IS NOT NULL) OR
    (status NOT IN ('paid', 'rejected', 'cancelled', 'failed') AND completed_at IS NULL)
  ),
  ADD CONSTRAINT chk_withdrawal_requests_approved_time CHECK (
    (status IN ('approved', 'processing', 'paid') AND approved_at IS NOT NULL) OR
    status NOT IN ('approved', 'processing', 'paid')
  );

ALTER TABLE payout_transactions
  ADD COLUMN version BIGINT UNSIGNED NOT NULL DEFAULT 1 AFTER status,
  ADD COLUMN confirmed_by_admin_id BIGINT UNSIGNED NULL AFTER response_summary,
  ADD COLUMN failure_reason VARCHAR(1000) NULL AFTER confirmed_by_admin_id,
  ADD UNIQUE KEY uq_payout_transactions_withdrawal (withdrawal_request_id),
  ADD CONSTRAINT fk_payout_transactions_confirmer
    FOREIGN KEY (confirmed_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  ADD CONSTRAINT chk_payout_transactions_version CHECK (version > 0),
  ADD CONSTRAINT chk_payout_transactions_times CHECK (
    (status = 'created' AND submitted_at IS NULL AND confirmed_at IS NULL) OR
    (status = 'submitted' AND submitted_at IS NOT NULL AND confirmed_at IS NULL) OR
    (status IN ('confirmed', 'reversed') AND submitted_at IS NOT NULL AND confirmed_at IS NOT NULL) OR
    (status = 'failed' AND submitted_at IS NOT NULL)
  );

INSERT IGNORE INTO wallet_accounts (
  public_id, owner_type, system_key, account_type, currency_id, status
)
SELECT '01J00000000000000000000206', 'system', 'withdrawal_fees', 'adjustment', id, 'active'
FROM currencies WHERE is_settlement = 1 AND is_active = 1;

INSERT IGNORE INTO permissions (code, permission_group, description) VALUES
  ('withdrawals.account_details.view', 'withdrawals', 'View decrypted withdrawal destination details.'),
  ('withdrawals.payout.confirm', 'withdrawals', 'Confirm externally completed manual payouts.'),
  ('withdrawals.settings.manage', 'withdrawals', 'Manage versioned global withdrawal rules.');

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 (
  'withdrawals.account_details.view',
  'withdrawals.payout.confirm',
  'withdrawals.settings.manage'
)
WHERE role.code IN ('super_admin', 'finance_manager');

CREATE TRIGGER trg_withdrawal_quotes_protected_update
BEFORE UPDATE ON withdrawal_quotes
FOR EACH ROW
SET
  NEW.public_id = OLD.public_id,
  NEW.request_fingerprint = OLD.request_fingerprint,
  NEW.user_id = OLD.user_id,
  NEW.withdrawal_method_id = OLD.withdrawal_method_id,
  NEW.currency_id = OLD.currency_id,
  NEW.gross_amount_minor = OLD.gross_amount_minor,
  NEW.fee_amount_minor = OLD.fee_amount_minor,
  NEW.net_amount_minor = OLD.net_amount_minor,
  NEW.rule_snapshot = OLD.rule_snapshot,
  NEW.method_version_snapshot = OLD.method_version_snapshot,
  NEW.currency_code_snapshot = OLD.currency_code_snapshot,
  NEW.currency_decimals_snapshot = OLD.currency_decimals_snapshot,
  NEW.expires_at = OLD.expires_at,
  NEW.created_at = OLD.created_at;

CREATE TRIGGER trg_withdrawal_quotes_no_delete
BEFORE DELETE ON withdrawal_quotes
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Withdrawal quotes cannot be deleted';

CREATE TRIGGER trg_withdrawal_requests_protected_update
BEFORE UPDATE ON withdrawal_requests
FOR EACH ROW
SET
  NEW.public_id = OLD.public_id,
  NEW.user_id = OLD.user_id,
  NEW.withdrawal_method_id = OLD.withdrawal_method_id,
  NEW.quote_id = OLD.quote_id,
  NEW.currency_id = OLD.currency_id,
  NEW.method_code_snapshot = OLD.method_code_snapshot,
  NEW.method_name_snapshot = OLD.method_name_snapshot,
  NEW.processing_mode_snapshot = OLD.processing_mode_snapshot,
  NEW.gross_amount_minor = OLD.gross_amount_minor,
  NEW.fee_amount_minor = OLD.fee_amount_minor,
  NEW.net_amount_minor = OLD.net_amount_minor,
  NEW.account_details_encrypted = OLD.account_details_encrypted,
  NEW.account_details_key_version = OLD.account_details_key_version,
  NEW.account_details_hash = OLD.account_details_hash,
  NEW.account_display_mask = OLD.account_display_mask,
  NEW.idempotency_key_hash = OLD.idempotency_key_hash,
  NEW.request_fingerprint = OLD.request_fingerprint,
  NEW.hold_ledger_transaction_id = OLD.hold_ledger_transaction_id,
  NEW.requested_at = OLD.requested_at,
  NEW.created_at = OLD.created_at;

CREATE TRIGGER trg_withdrawal_requests_no_delete
BEFORE DELETE ON withdrawal_requests
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Withdrawal requests cannot be deleted';

CREATE TRIGGER trg_withdrawal_history_no_update
BEFORE UPDATE ON withdrawal_status_history
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Withdrawal status history is immutable';

CREATE TRIGGER trg_withdrawal_history_no_delete
BEFORE DELETE ON withdrawal_status_history
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Withdrawal status history cannot be deleted';

CREATE TRIGGER trg_withdrawal_approvals_no_update
BEFORE UPDATE ON withdrawal_approvals
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Withdrawal approvals are immutable';

CREATE TRIGGER trg_withdrawal_approvals_no_delete
BEFORE DELETE ON withdrawal_approvals
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Withdrawal approvals cannot be deleted';

CREATE TRIGGER trg_payout_transactions_protected_update
BEFORE UPDATE ON payout_transactions
FOR EACH ROW
SET
  NEW.public_id = OLD.public_id,
  NEW.withdrawal_request_id = OLD.withdrawal_request_id,
  NEW.provider_code = OLD.provider_code,
  NEW.amount_minor = OLD.amount_minor,
  NEW.currency_code = OLD.currency_code,
  NEW.request_fingerprint = OLD.request_fingerprint,
  NEW.created_at = OLD.created_at;

CREATE TRIGGER trg_payout_transactions_no_delete
BEFORE DELETE ON payout_transactions
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Payout transactions cannot be deleted';

-- migrate:down

DROP TRIGGER IF EXISTS trg_payout_transactions_no_delete;
DROP TRIGGER IF EXISTS trg_payout_transactions_protected_update;
DROP TRIGGER IF EXISTS trg_withdrawal_approvals_no_delete;
DROP TRIGGER IF EXISTS trg_withdrawal_approvals_no_update;
DROP TRIGGER IF EXISTS trg_withdrawal_history_no_delete;
DROP TRIGGER IF EXISTS trg_withdrawal_history_no_update;
DROP TRIGGER IF EXISTS trg_withdrawal_requests_no_delete;
DROP TRIGGER IF EXISTS trg_withdrawal_requests_protected_update;
DROP TRIGGER IF EXISTS trg_withdrawal_quotes_no_delete;
DROP TRIGGER IF EXISTS trg_withdrawal_quotes_protected_update;

DELETE FROM permissions
WHERE code IN (
  'withdrawals.account_details.view',
  'withdrawals.payout.confirm',
  'withdrawals.settings.manage'
);

DELETE account
FROM wallet_accounts AS account
LEFT JOIN ledger_entries AS entry ON entry.account_id = account.id
WHERE account.system_key = 'withdrawal_fees' AND entry.id IS NULL;

ALTER TABLE payout_transactions
  DROP CHECK chk_payout_transactions_times,
  DROP CHECK chk_payout_transactions_version,
  DROP FOREIGN KEY fk_payout_transactions_confirmer,
  DROP INDEX uq_payout_transactions_withdrawal,
  DROP COLUMN failure_reason,
  DROP COLUMN confirmed_by_admin_id,
  DROP COLUMN version;

ALTER TABLE withdrawal_requests
  DROP CHECK chk_withdrawal_requests_approved_time,
  DROP CHECK chk_withdrawal_requests_completion,
  DROP CHECK chk_withdrawal_requests_approvals,
  DROP CHECK chk_withdrawal_requests_version,
  DROP FOREIGN KEY fk_withdrawal_requests_reviewer,
  DROP INDEX idx_withdrawal_requests_fingerprint,
  DROP INDEX idx_withdrawal_requests_user_day,
  DROP COLUMN approved_at,
  DROP COLUMN reviewed_by_admin_id,
  DROP COLUMN approval_count,
  DROP COLUMN required_approval_count,
  DROP COLUMN version,
  DROP COLUMN request_fingerprint,
  DROP COLUMN account_details_hash,
  DROP COLUMN processing_mode_snapshot,
  DROP COLUMN method_name_snapshot,
  DROP COLUMN method_code_snapshot;

ALTER TABLE withdrawal_quotes
  DROP CHECK chk_withdrawal_quotes_decimals,
  DROP CHECK chk_withdrawal_quotes_method_version,
  DROP INDEX idx_withdrawal_quotes_fingerprint,
  DROP COLUMN currency_decimals_snapshot,
  DROP COLUMN currency_code_snapshot,
  DROP COLUMN method_version_snapshot,
  DROP COLUMN request_fingerprint;

ALTER TABLE withdrawal_method_fields
  DROP CHECK chk_withdrawal_fields_version,
  DROP FOREIGN KEY fk_withdrawal_fields_updater,
  DROP FOREIGN KEY fk_withdrawal_fields_creator,
  DROP COLUMN updated_by_admin_id,
  DROP COLUMN created_by_admin_id,
  DROP COLUMN version;

ALTER TABLE withdrawal_methods
  DROP CHECK chk_withdrawal_methods_version,
  DROP FOREIGN KEY fk_withdrawal_methods_updater,
  DROP COLUMN updated_by_admin_id,
  DROP COLUMN version;