-- CY.CASH V7 SANDBOX OPERATION TABLES
-- Import ONLY into the separate development database (for example fcaglobal_dev).
-- These tables do not alter the live GoldCoders schema.

CREATE TABLE IF NOT EXISTS cy_funding_requests (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  method VARCHAR(30) NOT NULL,
  amount DECIMAL(20,10) NOT NULL,
  reference_code VARCHAR(40) NOT NULL,
  destination_address VARCHAR(255) NOT NULL,
  status ENUM('pending','processing','confirmed','cancelled','failed') NOT NULL DEFAULT 'pending',
  history_id BIGINT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  confirmed_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_reference_code(reference_code),
  KEY user_status(user_id,status),
  KEY created_at(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cy_investment_orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  plan_id BIGINT NOT NULL,
  type_id BIGINT NOT NULL,
  amount DECIMAL(20,10) NOT NULL,
  status ENUM('processing','active','failed') NOT NULL DEFAULT 'processing',
  deposit_id BIGINT NULL,
  history_id BIGINT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY user_status(user_id,status),
  KEY created_at(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cy_withdrawal_requests (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  method VARCHAR(30) NOT NULL,
  wallet_address VARCHAR(255) NOT NULL,
  amount DECIMAL(20,10) NOT NULL,
  reference_code VARCHAR(40) NOT NULL,
  status ENUM('pending','processed','cancelled','failed') NOT NULL DEFAULT 'pending',
  history_id BIGINT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  processed_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_withdraw_reference(reference_code),
  KEY user_status(user_id,status),
  KEY created_at(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;