-- CY.CASH V7.4 MULTI-CHAIN DEPOSIT ENGINE
-- Prerequisite: V7.0 + V7.2 + V7.3 migrations already imported.
-- Import ONLY into fcaglobal_dev while testing.

ALTER TABLE cy_funding_requests
  MODIFY status ENUM('pending','processing','confirmed','expired','cancelled','failed') NOT NULL DEFAULT 'pending',
  MODIFY expected_crypto DECIMAL(36,18) NULL,
  MODIFY actual_crypto DECIMAL(36,18) NULL,
  ADD COLUMN network VARCHAR(40) NULL AFTER provider,
  ADD COLUMN rate_source VARCHAR(60) NULL AFTER network,
  ADD COLUMN expires_at DATETIME NULL AFTER rate_source,
  ADD KEY idx_funding_expiry(status,expires_at);

ALTER TABLE cy_crypto_receipts
  MODIFY crypto_amount DECIMAL(36,18) NOT NULL,
  MODIFY status ENUM('detected','confirming','unmatched','amount_mismatch','credited','sweep_queued','swept','failed') NOT NULL DEFAULT 'detected';

ALTER TABLE cy_crypto_sweeps
  MODIFY crypto_amount DECIMAL(36,18) NOT NULL,
  MODIFY status ENUM('queued','signing','sent','confirmed','failed') NOT NULL DEFAULT 'queued';

CREATE TABLE IF NOT EXISTS cy_crypto_address_pool (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  asset VARCHAR(30) NOT NULL,
  address VARCHAR(255) NOT NULL,
  derivation_path VARCHAR(120) NULL,
  public_key_ref VARCHAR(255) NULL,
  status ENUM('available','assigned','disabled') NOT NULL DEFAULT 'available',
  assigned_user_id BIGINT NULL,
  imported_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  assigned_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_asset_address(asset,address),
  KEY asset_status(asset,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cy_crypto_subscriptions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  provider VARCHAR(30) NOT NULL,
  asset VARCHAR(30) NOT NULL,
  address VARCHAR(255) NOT NULL,
  external_id VARCHAR(255) NULL,
  status ENUM('active','disabled','failed') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  UNIQUE KEY uq_provider_asset_address(provider,asset,address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cy_crypto_prices (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  asset VARCHAR(30) NOT NULL,
  price_usd DECIMAL(30,12) NOT NULL,
  source VARCHAR(60) NOT NULL,
  fetched_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY asset_fetched(asset,fetched_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cy_crypto_webhook_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  provider VARCHAR(30) NOT NULL,
  event_key VARCHAR(191) NOT NULL,
  payload_json LONGTEXT NOT NULL,
  status ENUM('received','processed','failed') NOT NULL DEFAULT 'received',
  error_message VARCHAR(500) NULL,
  received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  processed_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_provider_event(provider,event_key),
  KEY status_received(status,received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;