CREATE TABLE IF NOT EXISTS clients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  tax_number VARCHAR(20) NOT NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(40) NULL,
  default_crypto_asset VARCHAR(80) DEFAULT 'USDT Liquid',
  liquid_address VARCHAR(255) NULL,
  pix_key VARCHAR(190) NULL,
  notes TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_clients_tax_number (tax_number),
  INDEX idx_clients_name (name),
  INDEX idx_clients_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE payments ADD COLUMN client_id INT NULL AFTER order_reference;
ALTER TABLE payments ADD INDEX idx_payments_client_id (client_id);
ALTER TABLE payments ADD CONSTRAINT fk_payments_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL;
CREATE TABLE IF NOT EXISTS crypto_receipts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  asset VARCHAR(80) NOT NULL DEFAULT 'USDT Liquid',
  amount DECIMAL(20,8) NULL,
  receiving_address VARCHAR(255) NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'waiting_payment',
  reference VARCHAR(100) NULL,
  blockchain_txid VARCHAR(120) NULL,
  notes TEXT NULL,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_crypto_receipts_client_id (client_id),
  INDEX idx_crypto_receipts_status (status),
  CONSTRAINT fk_crypto_receipts_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  CONSTRAINT fk_crypto_receipts_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS crypto_payouts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  asset VARCHAR(80) NOT NULL DEFAULT 'USDT Liquid',
  amount DECIMAL(20,8) NOT NULL,
  destination_address VARCHAR(255) NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending_manual_send',
  reference VARCHAR(100) NULL,
  blockchain_txid VARCHAR(120) NULL,
  notes TEXT NULL,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_crypto_payouts_client_id (client_id),
  INDEX idx_crypto_payouts_status (status),
  CONSTRAINT fk_crypto_payouts_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  CONSTRAINT fk_crypto_payouts_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;