CREATE DATABASE IF NOT EXISTS brisco_db;
USE brisco_db;

-- Products table
CREATE TABLE IF NOT EXISTS products (
    id VARCHAR(100) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    originalPrice DECIMAL(10,2) DEFAULT 0,
    category VARCHAR(50) NOT NULL,
    unit VARCHAR(50) NOT NULL,
    weight DECIMAL(8,2) DEFAULT 0,
    weightUnit VARCHAR(10) DEFAULT 'kg',
    grade VARCHAR(20) DEFAULT 'Premium',
    imageUrl VARCHAR(500),
    stock INT DEFAULT 0,
    onSpecial BOOLEAN DEFAULT FALSE,
    active BOOLEAN DEFAULT TRUE,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Categories table
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    displayName VARCHAR(100) NOT NULL,
    description TEXT,
    icon VARCHAR(10),
    color VARCHAR(7),
    order_index INT DEFAULT 0,
    active BOOLEAN DEFAULT TRUE,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    firstName VARCHAR(50) NOT NULL,
    lastName VARCHAR(50) NOT NULL,
    role VARCHAR(20) DEFAULT 'staff',
    active BOOLEAN DEFAULT TRUE,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample categories
INSERT INTO categories (name, displayName, description, icon, color, order_index) VALUES
('bulk_hampers', 'Bulk Hampers', 'Premium meat hampers for bulk orders', '📦', '#FF6B6B', 1),
('master_wors', 'Master Wors Range®', 'Artisanal boerewors and sausage products', '🍖', '#4ECDC4', 2),
('beef', 'Beef', 'Premium beef cuts and products', '🥩', '#45B7D1', 3),
('chicken', 'Chicken', 'Fresh chicken products and cuts', '🍗', '#96CEB4', 4),
('pork', 'Pork', 'Quality pork products and cuts', '🥓', '#FFEAA7', 5),
('lamb', 'Lamb', 'Premium lamb and mutton products', '🐑', '#DDA0DD', 6),
('ostrich', 'Ostrich', 'Healthy ostrich meat products', '🦆', '#98D8C8', 7),
('specials', 'On Special', 'Current special offers and deals', '🏷️', '#FFB6C1', 8),
('pies', 'Piemans Assorted Pies', 'Traditional South African pies', '🥧', '#F4A460', 9),
('restaurant', 'Restaurant Sector', 'Products for restaurant and food service', '🍽️', '#87CEEB', 10);

-- Insert sample products
INSERT INTO products (id, name, description, price, category, unit, weight, grade, imageUrl, stock, onSpecial) VALUES
('jade_hamper', 'JADE HAMPER', '23 KG Premium Quality Meat - 52% Beef | 9% Lamb | 17% Pork | 22% Chicken', 2999.99, 'bulk_hampers', 'hamper', 23, 'Premium', 'jade_hamper', 50, FALSE),
('gold_hamper', 'EVERYDAY Bulk Hamper (Gold)', 'Premium Quality Meat - R100 p/kg', 1799.99, 'bulk_hampers', 'hamper', 18, 'Premium', 'gold_hamper', 30, FALSE),
('brisco_boerewors', 'Brisco Boerewors (2kg)', 'The perfect balanced and juicy Boerewors - Vacuum Sealed - Package totals 2kg (4 x 500g packs)', 262.00, 'master_wors', '2kg', 2, 'Premium', 'brisco_boerewors', 100, FALSE),
('cheese_filled_boerewors', 'Cheese Filled Boerewors', 'A juicy, flavour-packed boerewors with premium cuts of beef and pork, blended with signature spices and generous pockets of real cheese', 238.00, 'master_wors', '2kg', 2, 'Premium', 'cheese_filled_boerewors_2kg', 75, FALSE),
('ostrich_wors', 'Ostrich Wors (1kg)', 'Crafted from 100% pure ground ostrich steak - 500g x 2 Packs (1kg) - Naturally low in fat, kilojoules, and cholesterol', 170.00, 'master_wors', '1kg', 1, 'Premium', 'ostrich_wors', 50, FALSE);

-- Create admin user (password: admin123)
INSERT INTO users (username, email, password, firstName, lastName, role) VALUES
('admin', 'admin@brisco.co.za', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Admin', 'User', 'admin');