database_setup.sql

-- =====================================================
-- Dream Brew Coffee Shop Management System
-- Database Setup Script
-- =====================================================
-- Database Name: DreamBrewCoffeeShop
-- =====================================================

-- Create Database
CREATE DATABASE IF NOT EXISTS DreamBrewCoffeeShop;
USE DreamBrewCoffeeShop;

-- =====================================================
-- Table: adminlogininfo
-- Description: Stores admin login credentials
-- =====================================================
CREATE TABLE IF NOT EXISTS adminlogininfo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    pin VARCHAR(10) NOT NULL
);

-- =====================================================
-- Table: employeelogininfo
-- Description: Stores employee login credentials
-- =====================================================
CREATE TABLE IF NOT EXISTS employeelogininfo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    pin VARCHAR(10) NOT NULL
);

-- =====================================================
-- Table: AllEmployeeInfo
-- Description: Stores detailed employee information
-- =====================================================
CREATE TABLE IF NOT EXISTS AllEmployeeInfo (
    ID VARCHAR(50) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    address TEXT,
    catagory VARCHAR(50) NOT NULL,
    bloodgroup VARCHAR(10),
    JoiningDate DATE NOT NULL
);

-- =====================================================
-- Table: AllFoodmenu
-- Description: Stores menu items with images
-- =====================================================
CREATE TABLE IF NOT EXISTS AllFoodmenu (
    productID VARCHAR(50) PRIMARY KEY,
    productName VARCHAR(100) NOT NULL,
    productPrice INT NOT NULL,
    ProductType VARCHAR(50) NOT NULL,
    ProductImage LONGBLOB
);

-- =====================================================
-- Table: orderinfo
-- Description: Temporary storage for current order items
-- =====================================================
CREATE TABLE IF NOT EXISTS orderinfo (
    ID VARCHAR(50) NOT NULL,
    name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    price INT NOT NULL
);

-- =====================================================
-- Table: salesInfo
-- Description: Stores completed sales records
-- =====================================================
CREATE TABLE IF NOT EXISTS salesInfo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,
    orderNo VARCHAR(50) NOT NULL,
    orderAmount INT NOT NULL
);

-- =====================================================
-- Insert Default Admin Account
-- =====================================================
-- Default Admin Credentials:
-- Username: admin
-- Password: admin123
-- PIN: 1234
-- =====================================================
INSERT INTO adminlogininfo (name, email, phone, username, password, pin)
VALUES ('Administrator', 'admin@dreambrew.com', '1234567890', 'admin', 'admin123', '1234')
ON DUPLICATE KEY UPDATE name=name;

-- =====================================================
-- Insert Sample Menu Items (Optional)
-- =====================================================
-- Note: ProductImage column requires BLOB data
-- You can add images through the application interface
-- =====================================================

-- Sample Coffee Items (without images - add images via application)
INSERT INTO AllFoodmenu (productID, productName, productPrice, ProductType)
VALUES 
    ('COF001', 'Espresso', 120, 'Coffee'),
    ('COF002', 'Cappuccino', 150, 'Coffee'),
    ('COF003', 'Latte', 160, 'Coffee'),
    ('COF004', 'Mocha', 170, 'Coffee'),
    ('COF005', 'Americano', 130, 'Coffee')
ON DUPLICATE KEY UPDATE productName=productName;

-- Sample Snacks
INSERT INTO AllFoodmenu (productID, productName, productPrice, ProductType)
VALUES 
    ('SNK001', 'Chocolate Muffin', 80, 'Muffins'),
    ('SNK002', 'Blueberry Muffin', 85, 'Muffins'),
    ('SNK003', 'Chicken Sandwich', 180, 'Sandwiches'),
    ('SNK004', 'Veg Sandwich', 150, 'Sandwiches')
ON DUPLICATE KEY UPDATE productName=productName;

-- =====================================================
-- Verification Queries
-- =====================================================
-- Run these queries to verify the setup:
-- SELECT * FROM adminlogininfo;
-- SELECT * FROM employeelogininfo;
-- SELECT * FROM AllEmployeeInfo;
-- SELECT * FROM AllFoodmenu;
-- SELECT * FROM salesInfo;
-- =====================================================