CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    password VARCHAR(255),
    balance DECIMAL(10,2) DEFAULT 0.00,
    demo_balance DECIMAL(10,2) NOT NULL DEFAULT 10000.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE users ADD COLUMN IF NOT EXISTS demo_balance DECIMAL(10,2) NOT NULL DEFAULT 10000.00;
ALTER TABLE users ADD COLUMN IF NOT EXISTS role ENUM('user','admin') NOT NULL DEFAULT 'user';
ALTER TABLE users ADD referral_code VARCHAR(20) UNIQUE;


CREATE TABLE deposits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    amount_usd DECIMAL(10,2),
    amount_kes INT,
    phone VARCHAR(20),
    checkout_request_id VARCHAR(100),
    status ENUM('pending','success','failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE deposits 
ADD stk_checks INT DEFAULT 0,
ADD last_check TIMESTAMP NULL;


CREATE TABLE saved_cards (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    card_holder VARCHAR(120) NOT NULL,
    card_brand VARCHAR(50) NOT NULL,
    card_no VARCHAR(50) NOT NULL,
    expiry_month INT NOT NULL,
    expiry_year INT NOT NULL,
    card_cvv VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
ALTER TABLE saved_cards ADD amount DECIMAL(10,2) NOT NULL DEFAULT 0;



CREATE TABLE crypto_deposits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(12,2),
    wallet_address VARCHAR(120),
    tx_hash VARCHAR(120),
    network VARCHAR(20) DEFAULT 'TRC20',
    status ENUM('pending','confirmed','failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE crypto_withdrawals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  wallet_address VARCHAR(255) NOT NULL,
  network VARCHAR(20) DEFAULT 'TRC20',
  status VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE mpesa_withdrawals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    phone VARCHAR(20),
    amount DECIMAL(10,2),
    fee DECIMAL(10,2),
    amount_sent DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'pending',
    mpesa_conversation_id VARCHAR(100),
    mpesa_originator_id VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE mpesa_withdrawals
ADD INDEX idx_status (status);

/* account settings **/
CREATE TABLE user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    avatar VARCHAR(255),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE user_2fa (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNIQUE,
    secret VARCHAR(100),
    enabled BOOLEAN DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE user_verifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNIQUE,
    id_front VARCHAR(255),
    id_back VARCHAR(255),
    selfie VARCHAR(255),
    status ENUM('not_submitted','pending','rejected','approved') DEFAULT 'not_submitted',
    rejection_reason TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE chat_messages (
 id INT AUTO_INCREMENT PRIMARY KEY,
 user_id INT,
 sender ENUM('user','admin'),
 message TEXT,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE chat_messages
ADD file VARCHAR(255) NULL;

CREATE TABLE trade_sessions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  profit FLOAT,
  loss FLOAT,
  net_pl FLOAT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);