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

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  phone VARCHAR(20) NOT NULL UNIQUE,
  email VARCHAR(150) NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  balance DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('active','blocked') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE admins (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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
);

CREATE TABLE api_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE settings (
  `key` VARCHAR(80) PRIMARY KEY,
  `value` TEXT NULL
);

INSERT INTO settings (`key`,`value`) VALUES
('app_name','ZA'),
('cashout_fee_percent','1.85'),
('send_money_fee','0'),
('recharge_fee','0');

-- Demo admin password: ChangeMe123!
INSERT INTO admins (name,email,password_hash)
VALUES ('ZA Super Admin','admin@za.local',
'$2y$10$7j8u9JxJ9rRjJ2vVv3m4uO7Q2Gf0e8dVv9jQmXk0eK0VvJq7vXk1m');