CREATE DATABASE IF NOT EXISTS za_wallet CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE za_wallet;

CREATE TABLE transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  type ENUM('send_money','cash_out','recharge','add_money','bill_payment') NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  fee DECIMAL(14,2) NOT NULL DEFAULT 0,
  target VARCHAR(100) NULL,
  status ENUM('pending','success','failed','reversed') NOT NULL DEFAULT 'success',
  reference VARCHAR(80) NOT NULL UNIQUE,
  metadata JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX(user_id), INDEX(created_at)
);

CREATE TABLE payment_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  provider ENUM('bkash','nagad') NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  payment_id VARCHAR(120) NULL,
  status ENUM('pending','paid','failed','cancelled') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
