-- Safe Test Data Insertion (Corrected Aliases)

-- 1. Insert Vendor User
INSERT INTO `users` (`username`, `email`, `password`, `role`) 
SELECT * FROM (SELECT 'KwameVendor' as u, 'vendor@test.com' as e, 'e10adc3949ba59abbe56e057f20f883e' as p, 'vendor' as r) AS tmp
WHERE NOT EXISTS (
    SELECT email FROM users WHERE email = 'vendor@test.com'
) LIMIT 1;

-- Get the User ID
SET @userID = (SELECT id FROM users WHERE email = 'vendor@test.com' LIMIT 1);

-- 2. Insert User Details
INSERT INTO `user_details` (`user_id`, `name`, `contactno`)
SELECT * FROM (SELECT @userID as uid, 'Kwame Vendor' as n, '0240000000' as c) AS tmp
WHERE NOT EXISTS (
    SELECT user_id FROM user_details WHERE user_id = @userID
) LIMIT 1;

-- 3. Insert Vendor Profile
INSERT INTO `vendors` (`user_id`, `store_name`, `store_slug`, `business_email`, `business_phone`, `commission_rate`, `status`, `created_at`, `approved_at`) 
SELECT * FROM (SELECT @userID as uid, 'Kwame Electronics' as sn, 'kwame-electronics' as ss, 'kwame@test.com' as be, '0240000000' as bp, 10.00 as cr, 'active' as st, NOW() as ca, NOW() as aa) AS tmp
WHERE NOT EXISTS (
    SELECT store_slug FROM vendors WHERE store_slug = 'kwame-electronics'
) LIMIT 1;

SET @vendorID = (SELECT id FROM vendors WHERE store_slug = 'kwame-electronics' LIMIT 1);

-- 4. Insert Sample Category
INSERT INTO `category` (`categoryName`, `categoryDescription`, `creationDate`, `updationDate`) 
SELECT * FROM (SELECT 'Electronics' as cn, 'Gadgets and devices' as cd, NOW() as cd_date, NOW() as ud_date) AS tmp
WHERE NOT EXISTS (
    SELECT categoryName FROM category WHERE categoryName = 'Electronics'
) LIMIT 1;

SET @catID = (SELECT id FROM category WHERE categoryName = 'Electronics' LIMIT 1);

-- 5. Insert Sample Subcategory
INSERT INTO `subcategory` (`categoryid`, `subcategory`, `creationDate`, `updationDate`) 
SELECT * FROM (SELECT @catID as cid, 'Smartphones' as s, NOW() as cd_date, NOW() as ud_date) AS tmp
WHERE NOT EXISTS (
    SELECT subcategory FROM subcategory WHERE subcategory = 'Smartphones' AND categoryid = @catID
) LIMIT 1;

SET @subCatID = (SELECT id FROM subcategory WHERE subcategory = 'Smartphones' AND categoryid = @catID LIMIT 1);

-- 6. Insert Sample Product
INSERT INTO `products` (`category`, `subCategory`, `productName`, `productCompany`, `productPrice`, `productPriceBeforeDiscount`, `productDescription`, `productImage1`, `productAvailability`, `postingDate`, `updationDate`, `vendor_id`, `approval_status`, `stock_quantity`, `sku`) 
SELECT * FROM (SELECT @catID as c, @subCatID as sc, 'iPhone 15 Pro' as pn, 'Apple' as pc, 15000 as pp, 16000 as ppbd, 'Brand new Apple iPhone 15 Pro with titanium build.' as pd, 'product1.jpg' as pi, 'In Stock' as pa, NOW() as pd_date, NOW() as ud_date, @vendorID as vid, 'approved' as ast, 50 as sq, 'IPH-15-PRO' as sku) AS tmp
WHERE NOT EXISTS (
    SELECT sku FROM products WHERE sku = 'IPH-15-PRO'
) LIMIT 1;
