-- =====================================================
-- BURGERSHOT JOB - SQL SETUP
-- Execute this in your FiveM database (skillhost)
-- 
-- Connection string for server.cfg:
-- set mysql_connection_string "mysql://skillhost:jrLnba5UVon1m9@localhost/skillhost?charset=utf8mb4"
-- =====================================================

-- Job Definition
INSERT INTO `jobs` (`name`, `label`) VALUES
    ('burgershot', 'Burgershot')
ON DUPLICATE KEY UPDATE `label` = VALUES(`label`);

-- Job Grades
INSERT INTO `job_grades` (`job_name`, `grade`, `name`, `label`, `salary`, `skin_male`, `skin_female`) VALUES
    ('burgershot', 0, 'trainee', 'Praktykant', 150, '{}', '{}'),
    ('burgershot', 1, 'employee', 'Pracownik', 200, '{}', '{}'),
    ('burgershot', 2, 'cook', 'Kucharz', 275, '{}', '{}'),
    ('burgershot', 3, 'manager', 'Manager', 350, '{}', '{}'),
    ('burgershot', 4, 'boss', 'Właściciel', 500, '{}', '{}')
ON DUPLICATE KEY UPDATE 
    `label` = VALUES(`label`),
    `salary` = VALUES(`salary`);

-- Society account for Burgershot
INSERT INTO `addon_account` (`name`, `label`, `shared`) VALUES
    ('society_burgershot', 'Kasa Burgershot', 1)
ON DUPLICATE KEY UPDATE `label` = VALUES(`label`);

-- Initial money in society account
INSERT INTO `addon_account_data` (`account_name`, `money`, `owner`) VALUES
    ('society_burgershot', 50000, NULL)
ON DUPLICATE KEY UPDATE `money` = VALUES(`money`);

-- Duty logs table
CREATE TABLE IF NOT EXISTS `burgershot_duty_logs` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `identifier` VARCHAR(60) NOT NULL,
    `action` VARCHAR(20) NOT NULL,
    `timestamp` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `identifier` (`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Production logs table
CREATE TABLE IF NOT EXISTS `burgershot_production_logs` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `identifier` VARCHAR(60) NOT NULL,
    `item` VARCHAR(50) NOT NULL,
    `amount` INT(11) NOT NULL DEFAULT 1,
    `timestamp` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `identifier` (`identifier`),
    INDEX `item` (`item`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sales logs table
CREATE TABLE IF NOT EXISTS `burgershot_sales_logs` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `identifier` VARCHAR(60) NOT NULL,
    `item` VARCHAR(50) NOT NULL,
    `amount` INT(11) NOT NULL DEFAULT 1,
    `total_price` INT(11) NOT NULL DEFAULT 0,
    `commission` INT(11) NOT NULL DEFAULT 0,
    `timestamp` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `identifier` (`identifier`),
    INDEX `item` (`item`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- PRODUCTS TABLE - Produkty kiosku
-- =====================================================

CREATE TABLE IF NOT EXISTS `burgershot_products` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `calories` INT(11) DEFAULT 0,
    `category` ENUM('burgers', 'meals', 'sides', 'drinks', 'desserts') NOT NULL DEFAULT 'burgers',
    `image` VARCHAR(255) DEFAULT 'images/default.png',
    `popular` TINYINT(1) DEFAULT 0,
    `available` TINYINT(1) DEFAULT 1,
    `sort_order` INT(11) DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `category` (`category`),
    INDEX `available` (`available`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Product customizations (opcje personalizacji)
CREATE TABLE IF NOT EXISTS `burgershot_product_options` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `product_id` INT(11) NOT NULL,
    `group_name` VARCHAR(50) NOT NULL,
    `option_name` VARCHAR(50) NOT NULL,
    `price_modifier` DECIMAL(10,2) DEFAULT 0.00,
    `sort_order` INT(11) DEFAULT 0,
    PRIMARY KEY (`id`),
    INDEX `product_id` (`product_id`),
    FOREIGN KEY (`product_id`) REFERENCES `burgershot_products`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- DEFAULT PRODUCTS DATA
-- =====================================================

INSERT INTO `burgershot_products` (`id`, `name`, `description`, `price`, `calories`, `category`, `image`, `popular`, `sort_order`) VALUES
-- Burgery (używamy dostępnych obrazków)
(1, 'Bleeder Burger', 'Klasyczny burger z podwójnym kotletem wołowym, serem, sałatą i sosem specjalnym', 5.99, 650, 'burgers', 'images/burger 1.png', 1, 1),
(2, 'Heart Stopper', 'Najbardziej intensywny burger - potrójne mięso, bekon, ser, cebula', 8.99, 1100, 'burgers', 'images/burger2.png', 1, 2),
(3, 'Money Shot', 'Premium burger z wagyu, truflami i złotą bułką', 12.99, 850, 'burgers', 'images/burger3.png', 0, 3),
(4, 'Torpedo', 'Długi burger w stylu submarine z sosem BBQ', 7.49, 780, 'burgers', 'images/burger 1.png', 0, 4),
(5, 'Meat Free', 'Burger wegetariański z kotletem roślinnym', 6.49, 420, 'burgers', 'images/burger2.png', 0, 5),
(6, 'Double Shot', 'Podwójny klasyk z podwójnym serem', 6.99, 720, 'burgers', 'images/burger3.png', 1, 6),
-- Zestawy
(10, 'Zestaw Bleeder', 'Bleeder Burger + Średnie Frytki + Napój', 8.99, 1050, 'meals', 'images/burger 1.png', 1, 10),
(11, 'Zestaw Heart Stopper', 'Heart Stopper + Duże Frytki + Napój XL', 12.99, 1650, 'meals', 'images/burger2.png', 1, 11),
(12, 'Family Box', '4x Burger + 2x Duże Frytki + 4x Napój', 29.99, 3200, 'meals', 'images/burger3.png', 0, 12),
(13, 'Kids Meal', 'Mini Burger + Małe Frytki + Napój + Zabawka', 5.99, 480, 'meals', 'images/burger 1.png', 0, 13),
-- Dodatki
(20, 'Frytki', 'Chrupiące złote frytki', 2.49, 340, 'sides', 'images/fryty.png', 1, 20),
(21, 'Onion Rings', 'Chrupiące krążki cebulowe', 3.49, 410, 'sides', 'images/fryty.png', 0, 21),
(22, 'Nuggetsy', '6 sztuk złocistych nuggetsów z kurczaka', 4.49, 280, 'sides', 'images/fryty.png', 1, 22),
(23, 'Cheese Bites', 'Gorące kąski serowe', 3.99, 350, 'sides', 'images/fryty.png', 0, 23),
-- Napoje
(30, 'Sprunk', 'Orzeźwiający napój cytrynowo-limonkowy', 1.99, 140, 'drinks', 'images/drink.png', 1, 30),
(31, 'eCola', 'Klasyczna cola', 1.99, 150, 'drinks', 'images/drink.png', 1, 31),
(32, 'Orang-O-Tang', 'Napój pomarańczowy', 1.99, 160, 'drinks', 'images/drink.png', 0, 32),
(33, 'Shake', 'Kremowy shake', 3.49, 420, 'drinks', 'images/drink.png', 1, 33),
(34, 'Woda mineralna', 'Naturalna woda źródlana', 1.49, 0, 'drinks', 'images/drink.png', 0, 34),
(35, 'Kawa', 'Świeżo parzona kawa', 2.49, 5, 'drinks', 'images/drink.png', 0, 35)
ON DUPLICATE KEY UPDATE 
    `name` = VALUES(`name`),
    `description` = VALUES(`description`),
    `price` = VALUES(`price`),
    `image` = VALUES(`image`);

-- Opcje personalizacji produktów
INSERT INTO `burgershot_product_options` (`product_id`, `group_name`, `option_name`, `price_modifier`, `sort_order`) VALUES
-- Bleeder Burger options
(1, 'Mięso', 'Pojedyncze', 0.00, 1),
(1, 'Mięso', 'Podwójne', 2.00, 2),
(1, 'Mięso', 'Potrójne', 4.00, 3),
(1, 'Dodatki', 'Extra ser', 0.50, 1),
(1, 'Dodatki', 'Bekon', 1.50, 2),
(1, 'Dodatki', 'Jalapeno', 0.50, 3),
-- Heart Stopper options
(2, 'Rozmiar', 'Normalny', 0.00, 1),
(2, 'Rozmiar', 'XL', 2.50, 2),
-- Zestaw Bleeder options
(10, 'Napój', 'Sprunk', 0.00, 1),
(10, 'Napój', 'eCola', 0.00, 2),
(10, 'Napój', 'Orang-O-Tang', 0.00, 3),
(10, 'Frytki', 'Średnie', 0.00, 1),
(10, 'Frytki', 'Duże', 1.00, 2),
-- Frytki options
(20, 'Rozmiar', 'Małe', -0.50, 1),
(20, 'Rozmiar', 'Średnie', 0.00, 2),
(20, 'Rozmiar', 'Duże', 1.00, 3),
-- Nuggetsy options
(22, 'Ilość', '6 sztuk', 0.00, 1),
(22, 'Ilość', '10 sztuk', 2.00, 2),
(22, 'Ilość', '20 sztuk', 5.00, 3),
(22, 'Sos', 'BBQ', 0.00, 1),
(22, 'Sos', 'Sweet Chili', 0.00, 2),
(22, 'Sos', 'Ranch', 0.00, 3),
-- Sprunk options
(30, 'Rozmiar', 'Mały', -0.50, 1),
(30, 'Rozmiar', 'Średni', 0.00, 2),
(30, 'Rozmiar', 'Duży', 0.80, 3),
(30, 'Rozmiar', 'XL', 1.50, 4),
-- eCola options
(31, 'Rozmiar', 'Mały', -0.50, 1),
(31, 'Rozmiar', 'Średni', 0.00, 2),
(31, 'Rozmiar', 'Duży', 0.80, 3),
(31, 'Rozmiar', 'XL', 1.50, 4),
-- Shake options
(33, 'Smak', 'Wanilia', 0.00, 1),
(33, 'Smak', 'Czekolada', 0.00, 2),
(33, 'Smak', 'Truskawka', 0.00, 3),
(33, 'Smak', 'Oreo', 0.50, 4)
ON DUPLICATE KEY UPDATE `price_modifier` = VALUES(`price_modifier`);

-- =====================================================
-- ORDERS SYSTEM - TABELE ZAMÓWIEŃ
-- =====================================================

-- Główna tabela zamówień
CREATE TABLE IF NOT EXISTS `burgershot_orders` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `order_number` VARCHAR(20) NOT NULL,
    `customer_identifier` VARCHAR(60) DEFAULT NULL,
    `customer_name` VARCHAR(100) DEFAULT NULL,
    `order_type` ENUM('dine-in', 'takeaway') DEFAULT 'dine-in',
    `payment_method` ENUM('cash', 'card') DEFAULT 'cash',
    `subtotal` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `tax` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `total` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `status` ENUM('pending', 'preparing', 'ready', 'completed', 'cancelled') DEFAULT 'pending',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `completed_at` DATETIME DEFAULT NULL,
    `prepared_by` VARCHAR(60) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `order_number` (`order_number`),
    INDEX `status` (`status`),
    INDEX `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pozycje w zamówieniu
CREATE TABLE IF NOT EXISTS `burgershot_order_items` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `order_id` INT(11) NOT NULL,
    `product_id` INT(11) NOT NULL,
    `product_name` VARCHAR(100) NOT NULL,
    `quantity` INT(11) NOT NULL DEFAULT 1,
    `unit_price` DECIMAL(10,2) NOT NULL,
    `customizations` TEXT DEFAULT NULL,
    `total_price` DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `order_id` (`order_id`),
    FOREIGN KEY (`order_id`) REFERENCES `burgershot_orders`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- KIOSK SYSTEM - TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS `burgershot_kiosks` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `x` FLOAT NOT NULL,
    `y` FLOAT NOT NULL,
    `z` FLOAT NOT NULL,
    `heading` FLOAT NOT NULL DEFAULT 0.0,
    `placed_by` VARCHAR(60) DEFAULT NULL,
    `placed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Boss Menu positions table
CREATE TABLE IF NOT EXISTS `burgershot_boss_menu` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `x` FLOAT NOT NULL,
    `y` FLOAT NOT NULL,
    `z` FLOAT NOT NULL,
    `heading` FLOAT NOT NULL DEFAULT 0.0,
    `placed_by` VARCHAR(60) DEFAULT NULL,
    `placed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Employee Menu positions table
CREATE TABLE IF NOT EXISTS `burgershot_employee_menu` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `x` FLOAT NOT NULL,
    `y` FLOAT NOT NULL,
    `z` FLOAT NOT NULL,
    `heading` FLOAT NOT NULL DEFAULT 0.0,
    `placed_by` VARCHAR(60) DEFAULT NULL,
    `placed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- OX_INVENTORY ITEMS
-- Add these to your ox_inventory/data/items.lua
-- =====================================================

/*
    -- INGREDIENTS (Składniki)
    ['bread'] = {
        label = 'Bułka',
        weight = 50,
        stack = true,
        close = true,
        description = 'Świeża bułka do burgerów'
    },
    ['meat_raw'] = {
        label = 'Surowe mięso',
        weight = 150,
        stack = true,
        close = true,
        description = 'Surowe mięso wołowe'
    },
    ['cheese'] = {
        label = 'Ser',
        weight = 30,
        stack = true,
        close = true,
        description = 'Plasterek sera'
    },
    ['lettuce'] = {
        label = 'Sałata',
        weight = 20,
        stack = true,
        close = true,
        description = 'Świeża sałata'
    },
    ['tomato'] = {
        label = 'Pomidor',
        weight = 25,
        stack = true,
        close = true,
        description = 'Świeży pomidor'
    },
    ['fries_raw'] = {
        label = 'Surowe frytki',
        weight = 100,
        stack = true,
        close = true,
        description = 'Mrożone frytki do smażenia'
    },
    ['soda_syrup'] = {
        label = 'Syrop do napojów',
        weight = 50,
        stack = true,
        close = true,
        description = 'Syrop do napojów gazowanych'
    },
    ['cup_empty'] = {
        label = 'Pusty kubek',
        weight = 10,
        stack = true,
        close = true,
        description = 'Pusty kubek na napoje'
    },
    ['wrapper'] = {
        label = 'Opakowanie',
        weight = 5,
        stack = true,
        close = true,
        description = 'Opakowanie na jedzenie'
    },

    -- PRODUCTS (Gotowe produkty)
    ['burger'] = {
        label = 'Burger',
        weight = 200,
        stack = true,
        close = true,
        description = 'Klasyczny burger z Burgershot',
        client = {
            status = { hunger = 250000 },
            anim = { dict = 'mp_player_inteat@burger', clip = 'mp_player_int_eat_burger' },
            prop = { model = 'prop_cs_burger_01', pos = vec3(0.02, 0.02, -0.02), rot = vec3(0.0, 0.0, 0.0) },
            usetime = 7500
        }
    },
    ['cheeseburger'] = {
        label = 'Cheeseburger',
        weight = 220,
        stack = true,
        close = true,
        description = 'Burger z serem z Burgershot',
        client = {
            status = { hunger = 300000 },
            anim = { dict = 'mp_player_inteat@burger', clip = 'mp_player_int_eat_burger' },
            prop = { model = 'prop_cs_burger_01', pos = vec3(0.02, 0.02, -0.02), rot = vec3(0.0, 0.0, 0.0) },
            usetime = 7500
        }
    },
    ['deluxe_burger'] = {
        label = 'Burger Deluxe',
        weight = 300,
        stack = true,
        close = true,
        description = 'Burger deluxe z dodatkami z Burgershot',
        client = {
            status = { hunger = 450000 },
            anim = { dict = 'mp_player_inteat@burger', clip = 'mp_player_int_eat_burger' },
            prop = { model = 'prop_cs_burger_01', pos = vec3(0.02, 0.02, -0.02), rot = vec3(0.0, 0.0, 0.0) },
            usetime = 10000
        }
    },
    ['fries'] = {
        label = 'Frytki',
        weight = 150,
        stack = true,
        close = true,
        description = 'Chrupiące frytki z Burgershot',
        client = {
            status = { hunger = 150000 },
            anim = { dict = 'mp_player_inteat@burger', clip = 'mp_player_int_eat_burger' },
            usetime = 5000
        }
    },
    ['soda'] = {
        label = 'Napój gazowany',
        weight = 250,
        stack = true,
        close = true,
        description = 'Orzeźwiający napój z Burgershot',
        client = {
            status = { thirst = 350000 },
            anim = { dict = 'mp_player_intdrink', clip = 'loop_bottle' },
            prop = { model = 'prop_ecola_can', pos = vec3(0.01, 0.01, 0.06), rot = vec3(5.0, 5.0, -180.5) },
            usetime = 5000
        }
    },
    ['meal_combo'] = {
        label = 'Zestaw Burgershot',
        weight = 500,
        stack = true,
        close = true,
        description = 'Pełny zestaw: burger, frytki i napój',
        client = {
            status = { hunger = 500000, thirst = 250000 },
            anim = { dict = 'mp_player_inteat@burger', clip = 'mp_player_int_eat_burger' },
            prop = { model = 'prop_cs_burger_01', pos = vec3(0.02, 0.02, -0.02), rot = vec3(0.0, 0.0, 0.0) },
            usetime = 15000
        }
    },
*/
