CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    balance DECIMAL(10, 3) DEFAULT 0.000,
    role ENUM('user', 'admin') DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS products (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 3) NOT NULL,
    category_name VARCHAR(255),
    category_img VARCHAR(255),
    available BOOLEAN DEFAULT TRUE,
    min_qty INT,
    max_qty INT,
    product_type VARCHAR(50),
    params_required JSON,
    last_synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    alfaore_order_id VARCHAR(255) UNIQUE,
    order_uuid VARCHAR(36) NOT NULL UNIQUE,
    quantity INT NOT NULL,
    total_price DECIMAL(10, 3) NOT NULL,
    status ENUM('pending', 'accepted', 'rejected', 'waiting', 'completed') DEFAULT 'pending',
    player_id VARCHAR(255),
    additional_params JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('deposit', 'withdrawal', 'purchase', 'refund') NOT NULL,
    amount DECIMAL(10, 3) NOT NULL,
    status ENUM('pending', 'completed', 'failed') DEFAULT 'pending',
    reference_id VARCHAR(255),
    payment_method VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(255) NOT NULL UNIQUE,
    `value` TEXT,
    description TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payment_gateways (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    is_enabled BOOLEAN DEFAULT FALSE,
    credentials JSON,
    type ENUM('paypal', 'shamcash', 'manual') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default settings
INSERT INTO settings (`key`, `value`, `description`) VALUES 
('site_name', 'Alfaore Store', 'اسم الموقع'),
('alfaore_api_token', '', 'توكن API Alfaore'),
('currency_symbol', '$', 'رمز العملة');

-- Insert default payment gateways
INSERT INTO payment_gateways (name, type, is_enabled) VALUES 
('PayPal', 'paypal', 0),
('ShamCash', 'shamcash', 0),
('Manual Payment', 'manual', 1);