-- CY.CASH V7.3 BITCOIN SIMPLE
-- Import ONLY into fcaglobal_dev while testing.

ALTER TABLE cy_funding_requests
  ADD COLUMN expected_crypto DECIMAL(28,12) NULL AFTER amount,
  ADD COLUMN actual_crypto DECIMAL(28,12) NULL AFTER expected_crypto,
  ADD COLUMN crypto_currency VARCHAR(16) NULL AFTER actual_crypto,
  ADD COLUMN rate_usd DECIMAL(24,8) NULL AFTER crypto_currency,
  ADD COLUMN provider VARCHAR(40) NULL AFTER rate_usd,
  ADD COLUMN txid VARCHAR(128) NULL AFTER destination_address,
  ADD COLUMN confirmations INT NOT NULL DEFAULT 0 AFTER txid,
  ADD KEY idx_funding_txid (txid);

CREATE TABLE IF NOT EXISTS cy_crypto_addresses (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  method VARCHAR(30) NOT NULL,
  address VARCHAR(255) NOT NULL,
  provider VARCHAR(40) NOT NULL,
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  UNIQUE KEY uq_user_method(user_id,method),
  UNIQUE KEY uq_method_address(method,address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cy_crypto_receipts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  funding_request_id BIGINT NULL,
  method VARCHAR(30) NOT NULL,
  chain VARCHAR(30) NOT NULL,
  txid VARCHAR(128) NOT NULL,
  vout INT NOT NULL DEFAULT 0,
  address VARCHAR(255) NOT NULL,
  crypto_amount DECIMAL(28,12) NOT NULL,
  confirmations INT NOT NULL DEFAULT 0,
  credited_usd DECIMAL(20,10) NULL,
  status ENUM('detected','unmatched','amount_mismatch','credited','swept','failed') NOT NULL DEFAULT 'detected',
  sweep_txid VARCHAR(128) NULL,
  first_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  credited_at DATETIME NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  UNIQUE KEY uq_chain_output(chain,txid,vout),
  KEY user_status(user_id,status),
  KEY funding_request_id(funding_request_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cy_crypto_sweeps (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  receipt_id BIGINT UNSIGNED NOT NULL,
  method VARCHAR(30) NOT NULL,
  crypto_amount DECIMAL(28,12) NOT NULL,
  treasury_address VARCHAR(255) NOT NULL,
  txid VARCHAR(128) NULL,
  status ENUM('sent','failed') NOT NULL,
  error_message VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_receipt(receipt_id),
  KEY status(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;