-- Database Schema for ArbWallet

CREATE DATABASE IF NOT EXISTS `arbwallet`;
USE `arbwallet`;

-- Users Table
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT(11) AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `email` VARCHAR(100) NOT NULL UNIQUE,
    `phone` VARCHAR(20) DEFAULT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `payment_password_hash` VARCHAR(255) DEFAULT NULL,
    `balance` DECIMAL(15,2) DEFAULT 0.00,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Transactions Table
CREATE TABLE IF NOT EXISTS `transactions` (
    `id` INT(11) AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT(11) NOT NULL,
    `type` ENUM('deposit', 'withdrawal', 'buy', 'sell') NOT NULL,
    `amount` DECIMAL(15,2) NOT NULL,
    `status` ENUM('pending', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
);

-- Orders Table (for P2P or quick buys)
CREATE TABLE IF NOT EXISTS `orders` (
    `id` INT(11) AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT(11) NOT NULL,
    `asset_type` VARCHAR(10) NOT NULL DEFAULT 'USDT',
    `fiat_amount` DECIMAL(15,2) NOT NULL,
    `asset_amount` DECIMAL(15,4) NOT NULL,
    `order_type` ENUM('buy', 'sell') NOT NULL,
    `status` ENUM('pending', 'paid', 'completed', 'cancelled') DEFAULT 'pending',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
);

-- Payments Table (Bank/UPI details)
CREATE TABLE IF NOT EXISTS `payment_methods` (
    `id` INT(11) AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT(11) NOT NULL,
    `method_type` ENUM('bank', 'upi') NOT NULL,
    `account_name` VARCHAR(100) NOT NULL,
    `account_number` VARCHAR(100) DEFAULT NULL,
    `ifsc_code` VARCHAR(20) DEFAULT NULL,
    `upi_id` VARCHAR(100) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
);

-- Admin Logs Table
CREATE TABLE IF NOT EXISTS `admin_logs` (
    `id` INT(11) AUTO_INCREMENT PRIMARY KEY,
    `admin_id` INT(11) NOT NULL,
    `action` VARCHAR(255) NOT NULL,
    `ip_address` VARCHAR(45) NOT NULL,
    `timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`admin_id`) REFERENCES `agents`(`id`) ON DELETE CASCADE
);