-- SuperPOS Master Data Seeding Script (Fixed)
-- Run this after migration.sql to populate test data
-- Date: 2026-01-10

USE superpos;

-- ============================================
-- 1. CATEGORIES (Extended)
-- ============================================
DELETE FROM `categories` WHERE id > 4;
-- Keep only default 4 from migration

INSERT INTO
    `categories` (
        `name`,
        `description`,
        `icon`,
        `is_active`
    )
VALUES (
        'Kesehatan & Kecantikan',
        'Produk perawatan',
        'fa-heart',
        1
    ),
    (
        'Rumah Tangga',
        'Peralatan rumah tangga',
        'fa-home',
        1
    ),
    (
        'Olahraga',
        'Peralatan olahraga',
        'fa-running',
        1
    ),
    (
        'Buku & Alat Tulis',
        'Produk edukasi',
        'fa-book',
        1
    )
ON DUPLICATE KEY UPDATE
    name = VALUES(name);

-- ============================================
-- 2. PRODUCTS (Realistic Examples)
-- ============================================
INSERT INTO
    `products` (
        `sku`,
        `name`,
        `category_id`,
        `product_type`,
        `unit`,
        `description`,
        `base_buy_price`,
        `base_sell_price`,
        `stock_current`,
        `min_stock_alert`,
        `is_active`
    )
VALUES
    -- Elektronik (Category 1)
    (
        'ELK001',
        'Samsung Galaxy A54 5G',
        1,
        'Barang',
        'Pcs',
        'Smartphone Android terbaru',
        3500000,
        4200000,
        15,
        3,
        1
    ),
    (
        'ELK002',
        'iPhone 13 128GB',
        1,
        'Barang',
        'Pcs',
        'iPhone generasi 13',
        8500000,
        10500000,
        8,
        2,
        1
    ),
    (
        'ELK003',
        'Xiaomi Redmi Note 12',
        1,
        'Barang',
        'Pcs',
        'HP budget terbaik',
        2000000,
        2500000,
        25,
        5,
        1
    ),
    (
        'ELK004',
        'Charger Type-C 20W',
        1,
        'Barang',
        'Pcs',
        'Fast charging adapter',
        50000,
        85000,
        100,
        20,
        1
    ),
    (
        'ELK005',
        'Earphone TWS',
        1,
        'Barang',
        'Pcs',
        'Wireless earphone bluetooth',
        150000,
        250000,
        50,
        10,
        1
    ),

-- Fashion (Category 2)
(
    'FSH001',
    'Kaos Polos Hitam',
    2,
    'Barang',
    'Pcs',
    'Cotton combed 30s',
    25000,
    50000,
    200,
    30,
    1
),
(
    'FSH002',
    'Kaos Polos Putih',
    2,
    'Barang',
    'Pcs',
    'Cotton combed 30s',
    25000,
    50000,
    180,
    30,
    1
),
(
    'FSH003',
    'Celana Jeans Pria',
    2,
    'Barang',
    'Pcs',
    'Denim premium',
    120000,
    200000,
    50,
    10,
    1
),
(
    'FSH004',
    'Kemeja Batik',
    2,
    'Barang',
    'Pcs',
    'Batik printing premium',
    80000,
    150000,
    40,
    8,
    1
),
(
    'FSH005',
    'Sepatu Sneakers',
    2,
    'Barang',
    'Pasang',
    'Sepatu casual import',
    200000,
    350000,
    30,
    5,
    1
),

-- Makanan & Minuman (Category 3)
(
    'MKN001',
    'Kopi Arabica 100gr',
    3,
    'Barang',
    'Pack',
    'Kopi premium single origin',
    35000,
    60000,
    80,
    15,
    1
),
(
    'MKN002',
    'Teh Hijau Premium',
    3,
    'Barang',
    'Box',
    'Green tea import',
    25000,
    45000,
    60,
    10,
    1
),
(
    'MKN003',
    'Snack Keripik',
    3,
    'Barang',
    'Pack',
    'Keripik singkong pedas',
    8000,
    15000,
    150,
    30,
    1
),
(
    'MKN004',
    'Air Mineral 600ml',
    3,
    'Barang',
    'Botol',
    'Air mineral kemasan',
    2000,
    4000,
    500,
    100,
    1
),

-- Jasa (Category 4)
(
    'JSA001',
    'Service HP Ringan',
    4,
    'Jasa',
    'Unit',
    'Perbaikan software, ganti LCD',
    0,
    150000,
    0,
    0,
    1
),
(
    'JSA002',
    'Service HP Berat',
    4,
    'Jasa',
    'Unit',
    'Perbaikan motherboard',
    0,
    350000,
    0,
    0,
    1
),
(
    'JSA003',
    'Instalasi Software',
    4,
    'Jasa',
    'Unit',
    'Install OS dan aplikasi',
    0,
    100000,
    0,
    0,
    1
),
(
    'JSA004',
    'Cuci Sepatu',
    4,
    'Jasa',
    'Pasang',
    'Deep cleaning sepatu',
    0,
    25000,
    0,
    0,
    1
),

-- Kesehatan & Kecantikan (Category 5)
(
    'KSH001',
    'Masker Wajah Korea',
    5,
    'Barang',
    'Box',
    'Sheet mask 10pcs',
    50000,
    85000,
    40,
    8,
    1
),
(
    'KSH002',
    'Vitamin C Serum',
    5,
    'Barang',
    'Botol',
    'Serum wajah brightening',
    80000,
    135000,
    30,
    5,
    1
),
(
    'KSH003',
    'Hand Sanitizer 100ml',
    5,
    'Barang',
    'Botol',
    'Antiseptik tangan',
    15000,
    25000,
    100,
    20,
    1
),

-- Rumah Tangga (Category 6)
(
    'RMH001',
    'Panci Set Stainless',
    6,
    'Barang',
    'Set',
    'Panci 5 pcs premium',
    250000,
    450000,
    20,
    3,
    1
),
(
    'RMH002',
    'Gelas Kaca Set 6',
    6,
    'Barang',
    'Set',
    'Gelas minum kaca',
    40000,
    75000,
    35,
    5,
    1
),
(
    'RMH003',
    'Sapu Lidi',
    6,
    'Barang',
    'Pcs',
    'Sapu tradisional',
    8000,
    15000,
    60,
    10,
    1
),

-- Paket Bundle
(
    'PKT001',
    'Paket HP + Aksesoris',
    1,
    'Paket',
    'Paket',
    'HP + Charger + Earphone',
    0,
    4500000,
    0,
    0,
    1
),
(
    'PKT002',
    'Paket Fashion Casual',
    2,
    'Paket',
    'Paket',
    'Kaos + Celana + Sepatu',
    0,
    550000,
    0,
    0,
    1
)
ON DUPLICATE KEY UPDATE
    name = VALUES(name);

-- ============================================
-- 3. PRODUCT BUNDLES (for Paket items)
-- ============================================
INSERT INTO
    `product_bundles` (
        `parent_product_id`,
        `child_product_id`,
        `quantity_needed`
    )
VALUES
    -- Paket HP + Aksesoris (PKT001)
    (
        (
            SELECT id
            FROM products
            WHERE
                sku = 'PKT001'
        ),
        (
            SELECT id
            FROM products
            WHERE
                sku = 'ELK003'
        ),
        1
    ),
    (
        (
            SELECT id
            FROM products
            WHERE
                sku = 'PKT001'
        ),
        (
            SELECT id
            FROM products
            WHERE
                sku = 'ELK004'
        ),
        1
    ),
    (
        (
            SELECT id
            FROM products
            WHERE
                sku = 'PKT001'
        ),
        (
            SELECT id
            FROM products
            WHERE
                sku = 'ELK005'
        ),
        1
    ),

-- Paket Fashion Casual (PKT002)
(
    (
        SELECT id
        FROM products
        WHERE
            sku = 'PKT002'
    ),
    (
        SELECT id
        FROM products
        WHERE
            sku = 'FSH001'
    ),
    1
),
(
    (
        SELECT id
        FROM products
        WHERE
            sku = 'PKT002'
    ),
    (
        SELECT id
        FROM products
        WHERE
            sku = 'FSH003'
    ),
    1
),
(
    (
        SELECT id
        FROM products
        WHERE
            sku = 'PKT002'
    ),
    (
        SELECT id
        FROM products
        WHERE
            sku = 'FSH005'
    ),
    1
)
ON DUPLICATE KEY UPDATE
    quantity_needed = VALUES(quantity_needed);

-- ============================================
-- 4. CONTACTS (Customers, Suppliers, Staff, Sales)
-- ============================================
INSERT INTO
    `contacts` (
        `type`,
        `code`,
        `name`,
        `phone`,
        `email`,
        `address`,
        `id_card_number`,
        `receivable_balance`,
        `payable_balance`,
        `credit_limit`,
        `is_active`
    )
VALUES
    -- Customers
    (
        'Customer',
        'CUST001',
        'Budi Santoso',
        '081234567890',
        'budi@email.com',
        'Jl. Merdeka No. 123, Jakarta',
        '3201012345678901',
        0,
        0,
        5000000,
        1
    ),
    (
        'Customer',
        'CUST002',
        'Siti Nurhaliza',
        '081234567891',
        'siti@email.com',
        'Jl. Sudirman No. 45, Bandung',
        '3201012345678902',
        0,
        0,
        3000000,
        1
    ),
    (
        'Customer',
        'CUST003',
        'Ahmad Rizki',
        '081234567892',
        'ahmad@email.com',
        'Jl. Gatot Subroto No. 78, Surabaya',
        '3201012345678903',
        0,
        0,
        2000000,
        1
    ),
    (
        'Customer',
        'CUST004',
        'Dewi Lestari',
        '081234567893',
        'dewi@email.com',
        'Jl. Diponegoro No. 12, Yogyakarta',
        '3201012345678904',
        0,
        0,
        4000000,
        1
    ),
    (
        'Customer',
        'CUST005',
        'Eko Prasetyo',
        '081234567894',
        'eko@email.com',
        'Jl. Ahmad Yani No. 56, Semarang',
        '3201012345678905',
        0,
        0,
        1500000,
        1
    ),

-- Suppliers
(
    'Supplier',
    'SUPP001',
    'PT Elektronik Jaya',
    '02112345678',
    'sales@elektronikjaya.com',
    'Kawasan Industri Jakarta',
    NULL,
    0,
    0,
    0,
    1
),
(
    'Supplier',
    'SUPP002',
    'CV Fashion Indo',
    '02212345679',
    'order@fashionindo.com',
    'Jl. Industri Bandung No. 90',
    NULL,
    0,
    0,
    0,
    1
),
(
    'Supplier',
    'SUPP003',
    'UD Makanan Sehat',
    '02312345680',
    'info@makansehat.com',
    'Pasar Induk Kramat Jati',
    NULL,
    0,
    0,
    0,
    1
),
(
    'Supplier',
    'SUPP004',
    'Toko Grosir Alat Rumah',
    '02412345681',
    'grosir@alattrumah.com',
    'Jl. Perdagangan No. 23',
    NULL,
    0,
    0,
    0,
    1
),

-- Staff
(
    'Staff',
    'STF001',
    'Rina Warehouse',
    '081234567895',
    'rina@superpos.com',
    'Jakarta',
    NULL,
    0,
    0,
    0,
    1
),
(
    'Staff',
    'STF002',
    'Joko Gudang',
    '081234567896',
    'joko@superpos.com',
    'Bandung',
    NULL,
    0,
    0,
    0,
    1
),

-- Sales
(
    'Sales',
    'SLS001',
    'Agus Marketing',
    '081234567897',
    'agus@superpos.com',
    'Jakarta',
    NULL,
    0,
    0,
    0,
    1
),
(
    'Sales',
    'SLS002',
    'Linda Sales',
    '081234567898',
    'linda@superpos.com',
    'Surabaya',
    NULL,
    0,
    0,
    0,
    1
)
ON DUPLICATE KEY UPDATE
    name = VALUES(name);

-- ============================================
-- SUMMARY
-- ============================================
SELECT 'Master Data Seeding Completed!' as Status;

SELECT COUNT(*) as Total_Categories
FROM categories
WHERE
    is_active = 1;

SELECT COUNT(*) as Total_Products FROM products WHERE is_active = 1;

SELECT COUNT(*) as Total_Contacts FROM contacts WHERE is_active = 1;