-- ========================================
-- BURGERSHOT KIOSK - System bazy danych
-- ========================================

-- Tabela kategorii produktów
CREATE TABLE IF NOT EXISTS burgershot_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    name_pl VARCHAR(50) NOT NULL,
    icon VARCHAR(50) DEFAULT 'fas fa-utensils',
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Wstaw domyślne kategorie
INSERT IGNORE INTO burgershot_categories (name, name_pl, icon, display_order) VALUES
('burgers', 'Burgery', 'fas fa-burger', 1),
('meals', 'Zestawy', 'fas fa-box', 2),
('sides', 'Dodatki', 'fas fa-french-fries', 3),
('drinks', 'Napoje', 'fas fa-cup-straw-swooshing', 4),
('desserts', 'Desery', 'fas fa-ice-cream', 5);

-- Tabela produktów
CREATE TABLE IF NOT EXISTS burgershot_products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(8,2) NOT NULL,
    calories INT DEFAULT 0,
    image VARCHAR(255) DEFAULT 'placeholder.png',
    is_popular BOOLEAN DEFAULT FALSE,
    is_available BOOLEAN DEFAULT TRUE,
    stock_quantity INT DEFAULT -1, -- -1 = nieograniczony
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES burgershot_categories(id) ON DELETE CASCADE
);

-- Tabela opcji customizacji (grupy)
CREATE TABLE IF NOT EXISTS burgershot_customization_groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    is_required BOOLEAN DEFAULT FALSE,
    max_selections INT DEFAULT 1, -- ile opcji można wybrać
    display_order INT DEFAULT 0,
    FOREIGN KEY (product_id) REFERENCES burgershot_products(id) ON DELETE CASCADE
);

-- Tabela opcji customizacji (wartości)
CREATE TABLE IF NOT EXISTS burgershot_customization_options (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    price_modifier DECIMAL(8,2) DEFAULT 0.00, -- dodatnia lub ujemna wartość
    is_default BOOLEAN DEFAULT FALSE,
    is_available BOOLEAN DEFAULT TRUE,
    display_order INT DEFAULT 0,
    FOREIGN KEY (group_id) REFERENCES burgershot_customization_groups(id) ON DELETE CASCADE
);

-- Tabela zamówień
CREATE TABLE IF NOT EXISTS burgershot_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(20) NOT NULL UNIQUE,
    order_type ENUM('dine-in', 'takeaway') NOT NULL DEFAULT 'dine-in',
    status ENUM('pending', 'preparing', 'ready', 'completed', 'cancelled') DEFAULT 'pending',
    payment_method ENUM('card', 'cash', 'phone') NOT NULL,
    payment_status ENUM('pending', 'paid', 'refunded') DEFAULT 'pending',
    subtotal DECIMAL(10,2) NOT NULL,
    tax DECIMAL(10,2) NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    customer_name VARCHAR(100),
    customer_phone VARCHAR(20),
    notes TEXT,
    kiosk_id VARCHAR(50), -- identyfikator kiosku
    cashier_id INT, -- jeśli obsługiwane przez kasjera
    prepared_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL
);

-- Tabela pozycji zamówienia
CREATE TABLE IF NOT EXISTS burgershot_order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL, -- kopia nazwy w momencie zamówienia
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(8,2) NOT NULL,
    customizations JSON, -- JSON z wybranymi opcjami
    total_price DECIMAL(10,2) NOT NULL,
    notes TEXT,
    FOREIGN KEY (order_id) REFERENCES burgershot_orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES burgershot_products(id)
);

-- Tabela historii statusów zamówienia
CREATE TABLE IF NOT EXISTS burgershot_order_status_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    status ENUM('pending', 'preparing', 'ready', 'completed', 'cancelled') NOT NULL,
    changed_by VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES burgershot_orders(id) ON DELETE CASCADE
);

-- Tabela promocji
CREATE TABLE IF NOT EXISTS burgershot_promotions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    discount_type ENUM('percentage', 'fixed', 'bogo') NOT NULL, -- BOGO = Buy One Get One
    discount_value DECIMAL(8,2) NOT NULL,
    min_order_value DECIMAL(8,2) DEFAULT 0,
    promo_code VARCHAR(50) UNIQUE,
    applicable_products JSON, -- JSON z listą ID produktów lub NULL dla wszystkich
    applicable_categories JSON, -- JSON z listą ID kategorii lub NULL dla wszystkich
    start_date DATE,
    end_date DATE,
    max_uses INT DEFAULT -1, -- -1 = nieograniczony
    current_uses INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabela statystyk dziennych
CREATE TABLE IF NOT EXISTS burgershot_daily_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    stat_date DATE NOT NULL UNIQUE,
    total_orders INT DEFAULT 0,
    total_revenue DECIMAL(12,2) DEFAULT 0,
    avg_order_value DECIMAL(8,2) DEFAULT 0,
    most_sold_product_id INT,
    dine_in_orders INT DEFAULT 0,
    takeaway_orders INT DEFAULT 0,
    card_payments INT DEFAULT 0,
    cash_payments INT DEFAULT 0,
    phone_payments INT DEFAULT 0,
    peak_hour INT, -- godzina z największą liczbą zamówień
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabela kiosków FiveM (z pozycjami do stawiania przez adminów)
CREATE TABLE IF NOT EXISTS burgershot_kiosks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    x FLOAT NOT NULL,
    y FLOAT NOT NULL,
    z FLOAT NOT NULL,
    heading FLOAT DEFAULT 0.0,
    placed_by VARCHAR(100),
    placed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabela Boss Menu (panele managera)
CREATE TABLE IF NOT EXISTS burgershot_boss_menu (
    id INT AUTO_INCREMENT PRIMARY KEY,
    x FLOAT NOT NULL,
    y FLOAT NOT NULL,
    z FLOAT NOT NULL,
    heading FLOAT DEFAULT 0.0,
    placed_by VARCHAR(100),
    placed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabela Employee Menu (panele pracownika)
CREATE TABLE IF NOT EXISTS burgershot_employee_menu (
    id INT AUTO_INCREMENT PRIMARY KEY,
    x FLOAT NOT NULL,
    y FLOAT NOT NULL,
    z FLOAT NOT NULL,
    heading FLOAT DEFAULT 0.0,
    placed_by VARCHAR(100),
    placed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabela pracowników Burgershot
CREATE TABLE IF NOT EXISTS burgershot_employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    role ENUM('cashier', 'cook', 'shift_manager', 'manager', 'director') DEFAULT 'cashier',
    pin_code VARCHAR(10) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Przykładowi pracownicy (PIN: 1234 dla pracowników, 9999 dla managerów)
INSERT IGNORE INTO burgershot_employees (employee_id, name, role, pin_code) VALUES
('EMP001', 'John Smith', 'cook', '1234'),
('EMP002', 'Jane Doe', 'cashier', '1234'),
('EMP003', 'Mike Johnson', 'shift_manager', '1234'),
('MGR001', 'Sarah Williams', 'manager', '9999'),
('DIR001', 'Robert Brown', 'director', '9999');

-- ========================================
-- WSTAW PRZYKŁADOWE PRODUKTY
-- ========================================

-- Burgery
INSERT IGNORE INTO burgershot_products (category_id, name, description, price, calories, image, is_popular) VALUES
(1, 'Bleeder Burger', 'Klasyczny burger z podwójnym kotletem wołowym, serem, sałatą i sosem specjalnym', 5.99, 650, 'bleeder.png', TRUE),
(1, 'Heart Stopper', 'Najbardziej intensywny burger - potrójne mięso, bekon, ser, cebula', 8.99, 1100, 'heartstopper.png', TRUE),
(1, 'Money Shot', 'Premium burger z wagyu, truflami i złotą bułką', 12.99, 850, 'moneyshot.png', FALSE),
(1, 'Torpedo', 'Długi burger w stylu submarine z sosem BBQ', 7.49, 780, 'torpedo.png', FALSE),
(1, 'Meat Free', 'Burger wegetariański z kotletem roślinnym', 6.49, 420, 'meatfree.png', FALSE),
(1, 'Double Shot', 'Podwójny klasyk z podwójnym serem', 6.99, 720, 'doubleshot.png', TRUE);

-- Zestawy
INSERT IGNORE INTO burgershot_products (category_id, name, description, price, calories, image, is_popular) VALUES
(2, 'Zestaw Bleeder', 'Bleeder Burger + Średnie Frytki + Napój', 8.99, 1050, 'meal-bleeder.png', TRUE),
(2, 'Zestaw Heart Stopper', 'Heart Stopper + Duże Frytki + Napój XL', 12.99, 1650, 'meal-heartstopper.png', TRUE),
(2, 'Family Box', '4x Burger + 2x Duże Frytki + 4x Napój', 29.99, 3200, 'family-box.png', FALSE),
(2, 'Kids Meal', 'Mini Burger + Małe Frytki + Napój + Zabawka', 5.99, 480, 'kids-meal.png', FALSE);

-- Dodatki
INSERT IGNORE INTO burgershot_products (category_id, name, description, price, calories, image, is_popular) VALUES
(3, 'Frytki', 'Chrupiące złote frytki', 2.49, 340, 'fries.png', TRUE),
(3, 'Onion Rings', 'Chrupiące krążki cebulowe', 3.49, 410, 'onion-rings.png', FALSE),
(3, 'Nuggetsy', '6 sztuk złocistych nuggetsów z kurczaka', 4.49, 280, 'nuggets.png', TRUE),
(3, 'Cheese Bites', 'Gorące kąski serowe', 3.99, 350, 'cheese-bites.png', FALSE),
(3, 'Coleslaw', 'Świeża surówka coleslaw', 1.99, 180, 'coleslaw.png', FALSE);

-- Napoje
INSERT IGNORE INTO burgershot_products (category_id, name, description, price, calories, image, is_popular) VALUES
(4, 'Sprunk', 'Orzeźwiający napój cytrynowo-limonkowy', 1.99, 140, 'sprunk.png', TRUE),
(4, 'eCola', 'Klasyczna cola', 1.99, 150, 'ecola.png', TRUE),
(4, 'Orang-O-Tang', 'Napój pomarańczowy', 1.99, 160, 'orangotang.png', FALSE),
(4, 'Shake', 'Kremowy shake', 3.49, 420, 'shake.png', TRUE),
(4, 'Woda mineralna', 'Naturalna woda źródlana', 1.49, 0, 'water.png', FALSE),
(4, 'Kawa', 'Świeżo parzona kawa', 2.49, 5, 'coffee.png', FALSE);

-- Desery
INSERT IGNORE INTO burgershot_products (category_id, name, description, price, calories, image, is_popular) VALUES
(5, 'Lody Soft', 'Kremowe lody w rożku', 1.99, 200, 'soft-ice.png', TRUE),
(5, 'McFlurry', 'Lody z dodatkami', 3.49, 380, 'mcflurry.png', TRUE),
(5, 'Ciasto jabłkowe', 'Ciepłe ciasto z jabłkami', 2.49, 240, 'apple-pie.png', FALSE),
(5, 'Donut', 'Świeży donut z polewą', 1.49, 250, 'donut.png', FALSE),
(5, 'Sundae', 'Lody z polewą i bitą śmietaną', 2.99, 320, 'sundae.png', FALSE);

-- ========================================
-- WSTAW OPCJE CUSTOMIZACJI
-- ========================================

-- Bleeder Burger - opcje mięsa
INSERT INTO burgershot_customization_groups (product_id, name, is_required, display_order) VALUES (1, 'Mięso', FALSE, 1);
SET @group_id = LAST_INSERT_ID();
INSERT INTO burgershot_customization_options (group_id, name, price_modifier, is_default, display_order) VALUES
(@group_id, 'Pojedyncze', 0.00, TRUE, 1),
(@group_id, 'Podwójne', 2.00, FALSE, 2),
(@group_id, 'Potrójne', 4.00, FALSE, 3);

-- Bleeder Burger - dodatki
INSERT INTO burgershot_customization_groups (product_id, name, is_required, display_order, max_selections) VALUES (1, 'Dodatki', FALSE, 2, 3);
SET @group_id = LAST_INSERT_ID();
INSERT INTO burgershot_customization_options (group_id, name, price_modifier, is_default, display_order) VALUES
(@group_id, 'Extra ser', 0.50, FALSE, 1),
(@group_id, 'Bekon', 1.50, FALSE, 2),
(@group_id, 'Jalapeno', 0.50, FALSE, 3);

-- Heart Stopper - rozmiar
INSERT INTO burgershot_customization_groups (product_id, name, is_required, display_order) VALUES (2, 'Rozmiar', FALSE, 1);
SET @group_id = LAST_INSERT_ID();
INSERT INTO burgershot_customization_options (group_id, name, price_modifier, is_default, display_order) VALUES
(@group_id, 'Normalny', 0.00, TRUE, 1),
(@group_id, 'XL', 2.50, FALSE, 2);

-- Zestaw Bleeder - napój
INSERT INTO burgershot_customization_groups (product_id, name, is_required, display_order) VALUES (7, 'Napój', TRUE, 1);
SET @group_id = LAST_INSERT_ID();
INSERT INTO burgershot_customization_options (group_id, name, price_modifier, is_default, display_order) VALUES
(@group_id, 'Sprunk', 0.00, TRUE, 1),
(@group_id, 'eCola', 0.00, FALSE, 2),
(@group_id, 'Orang-O-Tang', 0.00, FALSE, 3);

-- Zestaw Bleeder - frytki
INSERT INTO burgershot_customization_groups (product_id, name, is_required, display_order) VALUES (7, 'Frytki', TRUE, 2);
SET @group_id = LAST_INSERT_ID();
INSERT INTO burgershot_customization_options (group_id, name, price_modifier, is_default, display_order) VALUES
(@group_id, 'Średnie', 0.00, TRUE, 1),
(@group_id, 'Duże', 1.00, FALSE, 2);

-- Frytki - rozmiar
INSERT INTO burgershot_customization_groups (product_id, name, is_required, display_order) VALUES (11, 'Rozmiar', FALSE, 1);
SET @group_id = LAST_INSERT_ID();
INSERT INTO burgershot_customization_options (group_id, name, price_modifier, is_default, display_order) VALUES
(@group_id, 'Małe', -0.50, FALSE, 1),
(@group_id, 'Średnie', 0.00, TRUE, 2),
(@group_id, 'Duże', 1.00, FALSE, 3);

-- Nuggetsy - ilość
INSERT INTO burgershot_customization_groups (product_id, name, is_required, display_order) VALUES (13, 'Ilość', FALSE, 1);
SET @group_id = LAST_INSERT_ID();
INSERT INTO burgershot_customization_options (group_id, name, price_modifier, is_default, display_order) VALUES
(@group_id, '6 sztuk', 0.00, TRUE, 1),
(@group_id, '10 sztuk', 2.00, FALSE, 2),
(@group_id, '20 sztuk', 5.00, FALSE, 3);

-- Nuggetsy - sos
INSERT INTO burgershot_customization_groups (product_id, name, is_required, display_order) VALUES (13, 'Sos', FALSE, 2);
SET @group_id = LAST_INSERT_ID();
INSERT INTO burgershot_customization_options (group_id, name, price_modifier, is_default, display_order) VALUES
(@group_id, 'BBQ', 0.00, TRUE, 1),
(@group_id, 'Sweet Chili', 0.00, FALSE, 2),
(@group_id, 'Ranch', 0.00, FALSE, 3);

-- Sprunk/eCola - rozmiar
INSERT INTO burgershot_customization_groups (product_id, name, is_required, display_order) VALUES (16, 'Rozmiar', FALSE, 1);
SET @group_id = LAST_INSERT_ID();
INSERT INTO burgershot_customization_options (group_id, name, price_modifier, is_default, display_order) VALUES
(@group_id, 'Mały', -0.50, FALSE, 1),
(@group_id, 'Średni', 0.00, TRUE, 2),
(@group_id, 'Duży', 0.80, FALSE, 3),
(@group_id, 'XL', 1.50, FALSE, 4);

INSERT INTO burgershot_customization_groups (product_id, name, is_required, display_order) VALUES (17, 'Rozmiar', FALSE, 1);
SET @group_id = LAST_INSERT_ID();
INSERT INTO burgershot_customization_options (group_id, name, price_modifier, is_default, display_order) VALUES
(@group_id, 'Mały', -0.50, FALSE, 1),
(@group_id, 'Średni', 0.00, TRUE, 2),
(@group_id, 'Duży', 0.80, FALSE, 3),
(@group_id, 'XL', 1.50, FALSE, 4);

-- Shake - smak
INSERT INTO burgershot_customization_groups (product_id, name, is_required, display_order) VALUES (19, 'Smak', TRUE, 1);
SET @group_id = LAST_INSERT_ID();
INSERT INTO burgershot_customization_options (group_id, name, price_modifier, is_default, display_order) VALUES
(@group_id, 'Wanilia', 0.00, TRUE, 1),
(@group_id, 'Czekolada', 0.00, FALSE, 2),
(@group_id, 'Truskawka', 0.00, FALSE, 3),
(@group_id, 'Oreo', 0.50, FALSE, 4);

-- Lody Soft - polewa
INSERT INTO burgershot_customization_groups (product_id, name, is_required, display_order) VALUES (22, 'Polewa', FALSE, 1);
SET @group_id = LAST_INSERT_ID();
INSERT INTO burgershot_customization_options (group_id, name, price_modifier, is_default, display_order) VALUES
(@group_id, 'Bez polewy', 0.00, TRUE, 1),
(@group_id, 'Czekolada', 0.30, FALSE, 2),
(@group_id, 'Karmel', 0.30, FALSE, 3),
(@group_id, 'Truskawka', 0.30, FALSE, 4);

-- McFlurry - dodatek
INSERT INTO burgershot_customization_groups (product_id, name, is_required, display_order) VALUES (23, 'Dodatek', TRUE, 1);
SET @group_id = LAST_INSERT_ID();
INSERT INTO burgershot_customization_options (group_id, name, price_modifier, is_default, display_order) VALUES
(@group_id, 'Oreo', 0.00, TRUE, 1),
(@group_id, 'M&Ms', 0.00, FALSE, 2),
(@group_id, 'Brownie', 0.50, FALSE, 3);

-- ========================================
-- WSTAW PRZYKŁADOWE PROMOCJE
-- ========================================

INSERT IGNORE INTO burgershot_promotions (name, description, discount_type, discount_value, promo_code, start_date, end_date) VALUES
('Mega Deal', 'Bleeder Burger + Frytki + Napój za $8.99', 'fixed', 2.00, 'MEGADEAL', CURDATE(), DATE_ADD(CURDATE(), INTERVAL 30 DAY)),
('10% zniżki', '10% zniżki na całe zamówienie', 'percentage', 10.00, 'BURGERSHOT10', CURDATE(), DATE_ADD(CURDATE(), INTERVAL 14 DAY)),
('Darmowy deser', 'Kup 2 burgery, dostań darmowy deser', 'bogo', 0.00, NULL, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 7 DAY));

-- ========================================
-- INDEKSY DLA WYDAJNOŚCI
-- ========================================

CREATE INDEX idx_orders_status ON burgershot_orders(status);
CREATE INDEX idx_orders_created ON burgershot_orders(created_at);
CREATE INDEX idx_orders_number ON burgershot_orders(order_number);
CREATE INDEX idx_products_category ON burgershot_products(category_id);
CREATE INDEX idx_products_popular ON burgershot_products(is_popular);
CREATE INDEX idx_order_items_order ON burgershot_order_items(order_id);
CREATE INDEX idx_daily_stats_date ON burgershot_daily_stats(stat_date);

-- ========================================
-- WIDOKI POMOCNICZE
-- ========================================

-- Widok produktów z kategoriami
CREATE OR REPLACE VIEW v_burgershot_products AS
SELECT 
    p.id,
    p.name,
    p.description,
    p.price,
    p.calories,
    p.image,
    p.is_popular,
    p.is_available,
    c.name as category,
    c.name_pl as category_pl
FROM burgershot_products p
JOIN burgershot_categories c ON p.category_id = c.id
WHERE p.is_available = TRUE AND c.is_active = TRUE;

-- Widok zamówień z podsumowaniem
CREATE OR REPLACE VIEW v_burgershot_orders_summary AS
SELECT 
    o.id,
    o.order_number,
    o.order_type,
    o.status,
    o.payment_method,
    o.total,
    o.created_at,
    COUNT(oi.id) as items_count,
    GROUP_CONCAT(CONCAT(oi.quantity, 'x ', oi.product_name) SEPARATOR ', ') as items_list
FROM burgershot_orders o
LEFT JOIN burgershot_order_items oi ON o.id = oi.order_id
GROUP BY o.id;

-- ========================================
-- PROCEDURY POMOCNICZE
-- ========================================

DELIMITER //

-- Procedura generująca numer zamówienia
CREATE PROCEDURE sp_generate_order_number(OUT new_order_number VARCHAR(20))
BEGIN
    DECLARE today_orders INT;
    DECLARE date_prefix VARCHAR(8);
    
    SET date_prefix = DATE_FORMAT(NOW(), '%Y%m%d');
    
    SELECT COUNT(*) + 1 INTO today_orders
    FROM burgershot_orders
    WHERE DATE(created_at) = CURDATE();
    
    SET new_order_number = CONCAT('BS', date_prefix, LPAD(today_orders, 4, '0'));
END //

-- Procedura aktualizująca statystyki dzienne
CREATE PROCEDURE sp_update_daily_stats()
BEGIN
    DECLARE v_date DATE;
    DECLARE v_total_orders INT;
    DECLARE v_total_revenue DECIMAL(12,2);
    DECLARE v_avg_order DECIMAL(8,2);
    DECLARE v_most_sold INT;
    DECLARE v_dine_in INT;
    DECLARE v_takeaway INT;
    DECLARE v_card INT;
    DECLARE v_cash INT;
    DECLARE v_phone INT;
    DECLARE v_peak_hour INT;
    
    SET v_date = CURDATE();
    
    -- Podstawowe statystyki
    SELECT 
        COUNT(*),
        COALESCE(SUM(total), 0),
        COALESCE(AVG(total), 0)
    INTO v_total_orders, v_total_revenue, v_avg_order
    FROM burgershot_orders
    WHERE DATE(created_at) = v_date AND status != 'cancelled';
    
    -- Typy zamówień
    SELECT COUNT(*) INTO v_dine_in FROM burgershot_orders WHERE DATE(created_at) = v_date AND order_type = 'dine-in' AND status != 'cancelled';
    SELECT COUNT(*) INTO v_takeaway FROM burgershot_orders WHERE DATE(created_at) = v_date AND order_type = 'takeaway' AND status != 'cancelled';
    
    -- Metody płatności
    SELECT COUNT(*) INTO v_card FROM burgershot_orders WHERE DATE(created_at) = v_date AND payment_method = 'card' AND status != 'cancelled';
    SELECT COUNT(*) INTO v_cash FROM burgershot_orders WHERE DATE(created_at) = v_date AND payment_method = 'cash' AND status != 'cancelled';
    SELECT COUNT(*) INTO v_phone FROM burgershot_orders WHERE DATE(created_at) = v_date AND payment_method = 'phone' AND status != 'cancelled';
    
    -- Najczęściej sprzedawany produkt
    SELECT product_id INTO v_most_sold
    FROM burgershot_order_items oi
    JOIN burgershot_orders o ON oi.order_id = o.id
    WHERE DATE(o.created_at) = v_date AND o.status != 'cancelled'
    GROUP BY product_id
    ORDER BY SUM(quantity) DESC
    LIMIT 1;
    
    -- Godzina szczytu
    SELECT HOUR(created_at) INTO v_peak_hour
    FROM burgershot_orders
    WHERE DATE(created_at) = v_date AND status != 'cancelled'
    GROUP BY HOUR(created_at)
    ORDER BY COUNT(*) DESC
    LIMIT 1;
    
    -- Wstaw lub zaktualizuj statystyki
    INSERT INTO burgershot_daily_stats 
        (stat_date, total_orders, total_revenue, avg_order_value, most_sold_product_id,
         dine_in_orders, takeaway_orders, card_payments, cash_payments, phone_payments, peak_hour)
    VALUES 
        (v_date, v_total_orders, v_total_revenue, v_avg_order, v_most_sold,
         v_dine_in, v_takeaway, v_card, v_cash, v_phone, v_peak_hour)
    ON DUPLICATE KEY UPDATE
        total_orders = v_total_orders,
        total_revenue = v_total_revenue,
        avg_order_value = v_avg_order,
        most_sold_product_id = v_most_sold,
        dine_in_orders = v_dine_in,
        takeaway_orders = v_takeaway,
        card_payments = v_card,
        cash_payments = v_cash,
        phone_payments = v_phone,
        peak_hour = v_peak_hour;
END //

DELIMITER ;

-- ========================================
-- EVENT DO AKTUALIZACJI STATYSTYK
-- ========================================

-- Włącz event scheduler jeśli nie jest włączony
-- SET GLOBAL event_scheduler = ON;

-- Event aktualizujący statystyki co godzinę
-- CREATE EVENT IF NOT EXISTS evt_update_burgershot_stats
-- ON SCHEDULE EVERY 1 HOUR
-- DO CALL sp_update_daily_stats();

SELECT 'Burgershot database setup completed!' as message;
