CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  usd_balance DECIMAL(18,2) DEFAULT 0,
  btc_balance DECIMAL(18,8) DEFAULT 0,
  referral_code VARCHAR(30),
  referred_by INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  type ENUM('deposit','withdraw','buy','sell','refund','escrow') NOT NULL,
  amount_usd DECIMAL(18,2) DEFAULT 0,
  amount_btc DECIMAL(18,8) DEFAULT 0,
  note VARCHAR(255) DEFAULT '',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE referrals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  referrer_id INT NOT NULL,
  referee_id INT NOT NULL,
  bonus_usd DECIMAL(18,2) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(10) NOT NULL,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  product_id INT NOT NULL,
  amount_usd DECIMAL(18,2) NOT NULL,
  amount_btc DECIMAL(18,8) NOT NULL,
  fee_usd DECIMAL(18,2) DEFAULT 0,
  status ENUM('pending','fulfilled','cancelled') DEFAULT 'pending',
  external_ref VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE INDEX idx_orders_user ON orders(user_id);

-- insert product BTC
INSERT INTO products (code, name, description) VALUES ('BTC', 'Bitcoin', 'Bitcoin product for purchase via platform');
