-- Card Categories
CREATE TABLE IF NOT EXISTS card_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    icon VARCHAR(10) DEFAULT '🎴',
    color VARCHAR(20) DEFAULT '#1a3c8f',
    description TEXT,
    image VARCHAR(255) DEFAULT NULL,
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Cards
CREATE TABLE IF NOT EXISTS cards (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    image VARCHAR(255) DEFAULT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0,
    original_price DECIMAL(10,2) DEFAULT NULL,
    validity VARCHAR(100) DEFAULT NULL,
    region VARCHAR(100) DEFAULT NULL,
    stock INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Card PINs/Serials (inventory)
CREATE TABLE IF NOT EXISTS card_pins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    card_id INT NOT NULL,
    pin_code VARCHAR(500) NOT NULL,
    serial_number VARCHAR(200) DEFAULT NULL,
    extra_info TEXT DEFAULT NULL,
    status ENUM('available','sold','reserved') DEFAULT 'available',
    sold_to INT DEFAULT NULL,
    sold_at TIMESTAMP NULL,
    order_id INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Card Orders
CREATE TABLE IF NOT EXISTS card_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    card_id INT NOT NULL,
    pin_id INT DEFAULT NULL,
    quantity INT DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    commission_amount DECIMAL(10,2) DEFAULT 0,
    balance_before DECIMAL(10,2),
    balance_after DECIMAL(10,2),
    reference VARCHAR(50),
    status ENUM('pending','completed','failed','refunded') DEFAULT 'pending',
    note TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Default categories
INSERT IGNORE INTO card_categories (name, icon, color, sort_order) VALUES
('Netflix', '🎬', '#E50914', 1),
('Gaming', '🎮', '#7B2FBE', 2),
('Google Play', '▶️', '#01875F', 3),
('iTunes/Apple', '🍎', '#555555', 4),
('Gift Cards', '🎁', '#F7941D', 5),
('Steam', '🎯', '#1B2838', 6);
