DATABASEsql.sql

-- Create database if it doesn't exist
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'storeDB')
    CREATE DATABASE storeDB;
GO

USE storeDB;
GO

-- Create the 'Inventory' table if it doesn't exist
IF OBJECT_ID('Inventory', 'U') IS NULL
BEGIN
    CREATE TABLE Inventory
    (
      inventory_id INT IDENTITY(1,1) PRIMARY KEY,
      category_name VARCHAR(50) UNIQUE NOT NULL
    );
END

-- Create the 'Items' table if it doesn't exist
IF OBJECT_ID('Items', 'U') IS NULL
BEGIN
    CREATE TABLE Items
    (
      item_id INT IDENTITY(1,1) PRIMARY KEY,
      item_name VARCHAR(50) NOT NULL,
      item_price DECIMAL(10,2) NOT NULL,
      item_unit VARCHAR(50) NOT NULL,
      item_stocks INT DEFAULT 0 CHECK (item_stocks >= 0),
      inventory_id INT FOREIGN KEY REFERENCES Inventory(inventory_id)
    );
END

-- Insert categories into the 'Inventory' table
INSERT INTO Inventory (category_name)
    SELECT category_name FROM 
    (VALUES 
        ('Vegetables'), 
        ('Meats'), 
        ('Fruits'), 
        ('Drinks'), 
        ('Liquor')
    ) AS V(category_name)
    WHERE NOT EXISTS (SELECT 1 FROM Inventory WHERE category_name = V.category_name);

-- Insert items into the 'Items' table
INSERT INTO Items (item_name, item_price, item_unit, item_stocks, inventory_id)
    SELECT V.item_name, V.item_price, V.item_unit, V.item_stocks, I.inventory_id
    FROM
    (VALUES
    -- Vegetables
    ('Tomato', 10.00, 'per piece', 100, 'Vegetables'),
    ('Kamunggay', 10.00, 'per bundle', 50, 'Vegetables'),
    ('Onion', 10.00, 'per piece', 200, 'Vegetables'),
    ('Garlic', 10.00, 'per piece', 150, 'Vegetables'),
    ('Ginger', 20.00, 'per 10 grams', 75, 'Vegetables'),
    ('Lettuce', 100.00, 'per 250 grams', 60, 'Vegetables'),
    ('Carrot', 20.00, 'per 250 grams', 90, 'Vegetables'),
    ('Bell Pepper', 199.00, 'per 250 grams', 40, 'Vegetables'),
    ('Squash', 99.00, 'per Kilo', 30, 'Vegetables'),
    ('Eggplant', 75.00, 'per Kilo', 25, 'Vegetables'),
    ('Potato', 80.00, 'per Kilo', 70, 'Vegetables'),
    ('Green Onion', 50.00, 'per 100 grams', 80, 'Vegetables'),

    -- Meats
    ('Beef Ribeye', 580.00, 'per kilo', 50, 'Meats'),
    ('Pork Belly', 280.00, 'per kilo', 50, 'Meats'),
    ('Beef Flank', 575.00, 'per kilo', 50, 'Meats'),
    ('A5 Wagyu', 13069.00, 'per kilo', 50, 'Meats'),
    ('Chicken Thigh', 280.00, 'per kilo', 50, 'Meats'),
    ('Whole Chicken', 155.00, 'per piece', 50, 'Meats'),
    ('Chicken Wings', 295.00, 'per kilo', 50, 'Meats'),
    ('Chicken Drumsticks', 228.00, 'per kilo', 50, 'Meats'),

    -- Fruits
    ('Apple', 10.00, 'per piece', 50, 'Fruits'),
    ('Orange', 10.00, 'per piece', 50, 'Fruits'),
    ('Banana', 80.00, 'per kilo', 50, 'Fruits'),
    ('Mango', 155.00, 'per kilo', 50, 'Fruits'),
    ('Grapes', 300.00, 'per kilo', 50, 'Fruits'),
    ('Pineapple', 170.00, 'per kilo', 50, 'Fruits'),
    ('Watermelon', 120.00, 'per kilo', 50, 'Fruits'),
    ('Strawberry', 250.00, 'per kilo', 50, 'Fruits'),
    ('Blueberry', 195.00, 'per kilo', 50, 'Fruits'),
    ('Avocado', 150.00, 'per kilo', 50, 'Fruits'),
    ('Mangosteen', 300.00, 'per kilo', 50, 'Fruits'),
    ('Kiwi', 150.00, 'per kilo', 50, 'Fruits'),

    -- Drinks
    ('Water 500ml', 20.00, 'per bottle', 50, 'Drinks'),
    ('Water 1L', 30.00, 'per bottle', 50, 'Drinks'),
    ('Gatorade', 35.00, 'per bottle', 50, 'Drinks'),
    ('Coke', 35.00, 'per can', 50, 'Drinks'),
    ('Coke 1L', 70.00, 'per bottle', 50, 'Drinks'),
    ('Sprite', 35.00, 'per can', 50, 'Drinks'),
    ('Sprite 1L', 70.00, 'per bottle', 50, 'Drinks'),

    -- Liquor
    ('Bacardi Rum', 275.00, 'per bottle', 50, 'Liquor'),
    ('Absolut Vodka', 800.00, 'per bottle', 50, 'Liquor'),
    ('Red Horse 1L', 120.00, 'per bottle', 50, 'Liquor'),
    ('Sauvignon Blanc', 3400.00, 'per bottle', 50, 'Liquor'),
    ('Casamigos Tequila', 2500.00, 'per bottle', 50, 'Liquor')
    ) AS V(item_name, item_price, item_unit, item_stocks, category_name) -- Changed alias to V
    JOIN Inventory I ON I.category_name = V.category_name
    WHERE NOT EXISTS (SELECT 1 FROM Items WHERE item_name = V.item_name);

-- Displaying the items in the 'Items' table with category descriptions
SELECT
    I.item_id,
    I.item_name,
    I.item_price,
    I.item_unit,
    I.item_stocks,
    C.category_name AS category_description
FROM Items I
JOIN Inventory C ON I.inventory_id = C.inventory_id;