
CREATE TABLE payments(
 id INT AUTO_INCREMENT PRIMARY KEY,
 order_id INT,
 gateway VARCHAR(20),
 gateway_ref VARCHAR(100),
 amount DECIMAL(8,2),
 status ENUM('success','failed'),
 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- wallet system
CREATE TABLE IF NOT EXISTS wallets (
  user_id INT PRIMARY KEY,
  balance DECIMAL(10,2) NOT NULL DEFAULT 0,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS wallet_transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  type ENUM('credit','debit'),
  amount DECIMAL(10,2),
  note VARCHAR(255),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
