-- ========================================
-- UBER EATS - KOMPLETNA BAZA DANYCH
-- ========================================
-- Uruchom ten skrypt w phpMyAdmin lub MySQL CLI
-- ========================================

-- Użyj istniejącej bazy danych
-- USE pc_citizen;

-- ========================================
-- USUŃ STARE TABELE (jeśli istnieją)
-- ODKOMENTUJ PONIŻSZE LINIE ABY WYCZYŚCIĆ BAZĘ
-- ========================================

-- ========================================
-- 1. TABELA PRACOWNIKÓW (KIEROWCY, MANAGEROWIE, ADMINI)
-- ========================================
CREATE TABLE IF NOT EXISTS ubereats_employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id VARCHAR(20) UNIQUE COMMENT 'ID pracownika np. UE-DRV001',
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL COMMENT 'Hasło hashowane bcrypt',
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(20),
    
    -- Informacje o pojeździe
    vehicle_type ENUM('car', 'motorcycle', 'scooter', 'bike') DEFAULT 'car',
    vehicle_plate VARCHAR(20) COMMENT 'Tablica rejestracyjna',
    vehicle_model VARCHAR(100) COMMENT 'Model pojazdu',
    vehicle_color VARCHAR(30) COMMENT 'Kolor pojazdu',
    
    -- Rola i status
    role ENUM('driver', 'manager', 'admin') DEFAULT 'driver',
    status ENUM('available', 'busy', 'offline') DEFAULT 'offline',
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE COMMENT 'Czy konto zweryfikowane',
    
    -- Statystyki
    rating DECIMAL(3,2) DEFAULT 5.00 COMMENT 'Ocena 1-5',
    total_deliveries INT DEFAULT 0,
    total_earnings DECIMAL(12,2) DEFAULT 0.00,
    total_tips DECIMAL(10,2) DEFAULT 0.00,
    total_distance_km DECIMAL(10,2) DEFAULT 0.00 COMMENT 'Przejechane kilometry',
    
    -- Dane zatrudnienia
    hired_by INT COMMENT 'ID managera który zatrudnił',
    hired_at TIMESTAMP NULL,
    
    -- Lokalizacja (ostatnia znana)
    last_latitude DECIMAL(10,8) NULL,
    last_longitude DECIMAL(11,8) NULL,
    last_location_update TIMESTAMP NULL,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP NULL,
    
    INDEX idx_email (email),
    INDEX idx_role (role),
    INDEX idx_status (status),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 2. TABELA LOGOWAŃ KIEROWCÓW
-- ========================================
CREATE TABLE IF NOT EXISTS ubereats_driver_logins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    driver_id INT NULL COMMENT 'ID kierowcy (NULL jeśli nieznany)',
    email VARCHAR(150) NOT NULL COMMENT 'Email użyty przy logowaniu',
    
    -- Informacje o logowaniu
    ip VARCHAR(45) COMMENT 'Adres IP',
    user_agent VARCHAR(500) COMMENT 'Przeglądarka/urządzenie',
    device_type VARCHAR(50) COMMENT 'mobile/desktop/tablet',
    
    -- Wynik
    success BOOLEAN DEFAULT FALSE,
    reason VARCHAR(100) COMMENT 'not_found, wrong_password, ok, blocked, etc.',
    
    -- Token (jeśli sukces)
    token_issued VARCHAR(500) NULL COMMENT 'JWT token (skrócony hash)',
    
    -- Geolokalizacja
    geo_country VARCHAR(50),
    geo_city VARCHAR(100),
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_driver_id (driver_id),
    INDEX idx_email (email),
    INDEX idx_success (success),
    INDEX idx_created_at (created_at),
    INDEX idx_ip (ip)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 3. TABELA LOGOWAŃ BOSSÓW (MANAGER/ADMIN)
-- ========================================
CREATE TABLE IF NOT EXISTS ubereats_boss_logins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    boss_id INT NULL COMMENT 'ID bossa',
    email VARCHAR(150) NOT NULL,
    
    ip VARCHAR(45),
    user_agent VARCHAR(500),
    
    success BOOLEAN DEFAULT FALSE,
    reason VARCHAR(100),
    
    -- Dodatkowe info bezpieczeństwa
    two_factor_used BOOLEAN DEFAULT FALSE,
    session_id VARCHAR(100),
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_boss_id (boss_id),
    INDEX idx_email (email),
    INDEX idx_success (success),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 4. TABELA SESJI KIEROWCÓW (ŚLEDZENIE PRACY)
-- ========================================
CREATE TABLE IF NOT EXISTS ubereats_driver_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    driver_id INT NOT NULL,
    
    -- Czas pracy
    session_start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    session_end TIMESTAMP NULL,
    duration_minutes INT DEFAULT 0 COMMENT 'Czas trwania sesji w minutach',
    
    -- Status podczas sesji
    status ENUM('online', 'available', 'busy', 'break', 'offline') DEFAULT 'online',
    
    -- Lokalizacja
    start_latitude DECIMAL(10,8),
    start_longitude DECIMAL(11,8),
    end_latitude DECIMAL(10,8),
    end_longitude DECIMAL(11,8),
    
    -- Statystyki sesji
    deliveries_count INT DEFAULT 0,
    earnings DECIMAL(10,2) DEFAULT 0.00,
    tips DECIMAL(10,2) DEFAULT 0.00,
    distance_km DECIMAL(8,2) DEFAULT 0.00,
    
    INDEX idx_driver_id (driver_id),
    INDEX idx_session_start (session_start),
    
    FOREIGN KEY (driver_id) REFERENCES ubereats_employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 5. TABELA KLIENTÓW UBEREATS
-- ========================================
CREATE TABLE IF NOT EXISTS ubereats_customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(20),
    
    -- Adresy (JSON array)
    addresses JSON COMMENT 'Lista zapisanych adresów',
    default_address_id INT DEFAULT 0,
    
    -- Płatności (JSON)
    payment_methods JSON COMMENT 'Zapisane metody płatności',
    
    -- Preferencje
    preferences JSON COMMENT 'Preferencje żywieniowe, ulubione restauracje',
    
    -- Statystyki
    total_orders INT DEFAULT 0,
    total_spent DECIMAL(12,2) DEFAULT 0.00,
    
    -- Status
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_email (email),
    INDEX idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 6. TABELA RESTAURACJI
-- ========================================
CREATE TABLE IF NOT EXISTS ubereats_restaurants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE COMMENT 'URL-friendly nazwa',
    description TEXT,
    
    -- Lokalizacja
    address VARCHAR(255),
    city VARCHAR(100) DEFAULT 'Los Santos',
    latitude DECIMAL(10,8),
    longitude DECIMAL(11,8),
    
    -- Kontakt
    phone VARCHAR(20),
    email VARCHAR(150),
    
    -- Godziny otwarcia (JSON)
    opening_hours JSON COMMENT '{"mon": {"open": "09:00", "close": "22:00"}, ...}',
    
    -- Informacje
    cuisine_type VARCHAR(100) COMMENT 'Fast Food, Italian, Asian, etc.',
    price_range ENUM('$', '$$', '$$$', '$$$$') DEFAULT '$$',
    average_prep_time INT DEFAULT 20 COMMENT 'Średni czas przygotowania w minutach',
    minimum_order DECIMAL(8,2) DEFAULT 0.00,
    delivery_fee DECIMAL(8,2) DEFAULT 2.99,
    
    -- Oceny
    rating DECIMAL(3,2) DEFAULT 5.00,
    total_reviews INT DEFAULT 0,
    
    -- Status
    is_active BOOLEAN DEFAULT TRUE,
    is_featured BOOLEAN DEFAULT FALSE,
    accepts_orders BOOLEAN DEFAULT TRUE,
    
    -- Media
    logo_url VARCHAR(255),
    banner_url VARCHAR(255),
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_name (name),
    INDEX idx_city (city),
    INDEX idx_cuisine (cuisine_type),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 7. TABELA PRODUKTÓW/MENU
-- ========================================
CREATE TABLE IF NOT EXISTS ubereats_products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    restaurant_id INT NOT NULL,
    
    name VARCHAR(100) NOT NULL,
    description TEXT,
    category VARCHAR(50) COMMENT 'burgers, drinks, desserts, etc.',
    
    price DECIMAL(8,2) NOT NULL,
    sale_price DECIMAL(8,2) NULL COMMENT 'Cena promocyjna',
    
    -- Informacje dietetyczne
    calories INT DEFAULT 0,
    is_vegetarian BOOLEAN DEFAULT FALSE,
    is_vegan BOOLEAN DEFAULT FALSE,
    is_gluten_free BOOLEAN DEFAULT FALSE,
    allergens VARCHAR(255) COMMENT 'Lista alergenów',
    
    -- Dostępność
    is_available BOOLEAN DEFAULT TRUE,
    is_popular BOOLEAN DEFAULT FALSE,
    stock_quantity INT DEFAULT -1 COMMENT '-1 = nieograniczona ilość',
    
    -- Media
    image_url VARCHAR(255),
    
    -- Kolejność wyświetlania
    sort_order INT DEFAULT 0,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_restaurant (restaurant_id),
    INDEX idx_category (category),
    INDEX idx_is_available (is_available),
    
    FOREIGN KEY (restaurant_id) REFERENCES ubereats_restaurants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 8. TABELA ZAMÓWIEŃ
-- ========================================
CREATE TABLE IF NOT EXISTS ubereats_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(20) NOT NULL UNIQUE COMMENT 'np. UE-12345678',
    
    -- Klient
    customer_id INT NULL COMMENT 'NULL dla gości',
    customer_name VARCHAR(100),
    customer_email VARCHAR(150),
    customer_phone VARCHAR(20),
    
    -- Restauracja
    restaurant_id INT NULL,
    restaurant_name VARCHAR(100),
    
    -- Kierowca
    driver_id INT NULL,
    driver_assigned_at TIMESTAMP NULL,
    
    -- Adresy
    pickup_address VARCHAR(255) COMMENT 'Adres restauracji',
    delivery_address VARCHAR(255) NOT NULL,
    delivery_city VARCHAR(100) DEFAULT 'Los Santos',
    delivery_latitude DECIMAL(10,8),
    delivery_longitude DECIMAL(11,8),
    delivery_notes TEXT COMMENT 'Instrukcje dla kierowcy',
    
    -- Typ zamówienia
    order_type ENUM('delivery', 'pickup', 'dine_in') DEFAULT 'delivery',
    
    -- Status
    status ENUM(
        'pending',           -- Oczekuje na potwierdzenie
        'confirmed',         -- Potwierdzone przez restaurację
        'preparing',         -- W przygotowaniu
        'ready_for_pickup',  -- Gotowe do odbioru
        'picked_up',         -- Odebrane przez kierowcę
        'delivering',        -- W drodze
        'delivered',         -- Dostarczone
        'cancelled',         -- Anulowane
        'refunded'           -- Zwrócone
    ) DEFAULT 'pending',
    
    -- Czasy
    estimated_prep_time INT COMMENT 'Szacowany czas przygotowania (min)',
    estimated_delivery_time INT COMMENT 'Szacowany czas dostawy (min)',
    actual_prep_time INT NULL,
    actual_delivery_time INT NULL,
    
    picked_up_at TIMESTAMP NULL,
    delivered_at TIMESTAMP NULL,
    cancelled_at TIMESTAMP NULL,
    
    -- Płatność
    payment_method ENUM('card', 'cash', 'paypal', 'apple_pay', 'google_pay') DEFAULT 'card',
    payment_status ENUM('pending', 'paid', 'failed', 'refunded') DEFAULT 'pending',
    payment_transaction_id VARCHAR(100),
    
    -- Kwoty
    subtotal DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    delivery_fee DECIMAL(8,2) DEFAULT 2.99,
    service_fee DECIMAL(8,2) DEFAULT 0.99,
    tax DECIMAL(8,2) DEFAULT 0.00,
    discount DECIMAL(8,2) DEFAULT 0.00,
    tip DECIMAL(8,2) DEFAULT 0.00,
    total DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    
    -- Zarobki kierowcy
    driver_earnings DECIMAL(8,2) DEFAULT 0.00,
    driver_tip DECIMAL(8,2) DEFAULT 0.00,
    
    -- Kod promocyjny
    promo_code VARCHAR(50),
    promo_discount DECIMAL(8,2) DEFAULT 0.00,
    
    -- Ocena
    customer_rating INT NULL COMMENT '1-5 gwiazdek',
    customer_review TEXT,
    driver_rating INT NULL COMMENT 'Ocena kierowcy 1-5',
    restaurant_rating INT NULL COMMENT 'Ocena restauracji 1-5',
    
    -- Źródło zamówienia
    source ENUM('web', 'mobile_app', 'phone', 'kiosk') DEFAULT 'web',
    
    -- Notatki
    notes TEXT,
    cancellation_reason TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_order_number (order_number),
    INDEX idx_customer_id (customer_id),
    INDEX idx_restaurant_id (restaurant_id),
    INDEX idx_driver_id (driver_id),
    INDEX idx_status (status),
    INDEX idx_payment_status (payment_status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 9. TABELA POZYCJI ZAMÓWIENIA
-- ========================================
CREATE TABLE IF NOT EXISTS ubereats_order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NULL,
    
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(8,2) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    
    -- Modyfikacje/dodatki (JSON)
    modifications JSON COMMENT '{"extra_cheese": true, "no_onions": true}',
    special_instructions TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_order_id (order_id),
    
    FOREIGN KEY (order_id) REFERENCES ubereats_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 10. TABELA HISTORII STATUSÓW ZAMÓWIENIA
-- ========================================
CREATE TABLE IF NOT EXISTS ubereats_order_status_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    
    status VARCHAR(50) NOT NULL,
    previous_status VARCHAR(50),
    
    changed_by_type ENUM('system', 'customer', 'restaurant', 'driver', 'admin') DEFAULT 'system',
    changed_by_id INT NULL,
    changed_by_name VARCHAR(100),
    
    notes TEXT,
    
    -- Lokalizacja przy zmianie statusu
    latitude DECIMAL(10,8),
    longitude DECIMAL(11,8),
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_order_id (order_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    
    FOREIGN KEY (order_id) REFERENCES ubereats_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 11. TABELA LOKALIZACJI KIEROWCY (TRACKING)
-- ========================================
CREATE TABLE IF NOT EXISTS ubereats_driver_locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    driver_id INT NOT NULL,
    order_id INT NULL COMMENT 'Jeśli w trakcie dostawy',
    
    latitude DECIMAL(10,8) NOT NULL,
    longitude DECIMAL(11,8) NOT NULL,
    
    speed DECIMAL(5,2) COMMENT 'Prędkość km/h',
    heading INT COMMENT 'Kierunek 0-360 stopni',
    accuracy DECIMAL(8,2) COMMENT 'Dokładność GPS w metrach',
    
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_driver_id (driver_id),
    INDEX idx_order_id (order_id),
    INDEX idx_recorded_at (recorded_at),
    
    FOREIGN KEY (driver_id) REFERENCES ubereats_employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 12. TABELA KODÓW PROMOCYJNYCH
-- ========================================
CREATE TABLE IF NOT EXISTS ubereats_promo_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255),
    
    -- Typ rabatu
    discount_type ENUM('percentage', 'fixed') DEFAULT 'percentage',
    discount_value DECIMAL(8,2) NOT NULL COMMENT 'Procent lub kwota',
    
    -- Ograniczenia
    minimum_order DECIMAL(8,2) DEFAULT 0.00,
    maximum_discount DECIMAL(8,2) NULL COMMENT 'Max rabat dla procentowych',
    
    -- Limity użycia
    usage_limit INT NULL COMMENT 'NULL = bez limitu',
    usage_count INT DEFAULT 0,
    usage_per_user INT DEFAULT 1,
    
    -- Ważność
    valid_from TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    valid_until TIMESTAMP NULL,
    
    -- Status
    is_active BOOLEAN DEFAULT TRUE,
    
    -- Ograniczenia (JSON)
    restrictions JSON COMMENT '{"restaurants": [1,2,3], "first_order_only": true}',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_code (code),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 13. TABELA POWIADOMIEŃ
-- ========================================
CREATE TABLE IF NOT EXISTS ubereats_notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Odbiorca
    recipient_type ENUM('customer', 'driver', 'restaurant', 'admin') NOT NULL,
    recipient_id INT NOT NULL,
    
    -- Treść
    title VARCHAR(100) NOT NULL,
    message TEXT NOT NULL,
    
    -- Typ
    notification_type ENUM('order', 'promo', 'system', 'alert') DEFAULT 'system',
    
    -- Powiązanie
    related_order_id INT NULL,
    
    -- Status
    is_read BOOLEAN DEFAULT FALSE,
    read_at TIMESTAMP NULL,
    
    -- Kanał
    channel ENUM('push', 'email', 'sms', 'in_app') DEFAULT 'in_app',
    sent_at TIMESTAMP NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_recipient (recipient_type, recipient_id),
    INDEX idx_is_read (is_read),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 14. TABELA RECENZJI
-- ========================================
CREATE TABLE IF NOT EXISTS ubereats_reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    customer_id INT NULL,
    
    -- Oceny
    overall_rating INT NOT NULL COMMENT '1-5',
    food_rating INT COMMENT '1-5',
    delivery_rating INT COMMENT '1-5',
    
    -- Recenzja
    review_text TEXT,
    
    -- Odpowiedź restauracji
    restaurant_response TEXT,
    restaurant_responded_at TIMESTAMP NULL,
    
    -- Status
    is_visible BOOLEAN DEFAULT TRUE,
    is_verified_purchase BOOLEAN DEFAULT TRUE,
    
    -- Media (zdjęcia)
    photos JSON COMMENT '["url1", "url2"]',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_order_id (order_id),
    INDEX idx_overall_rating (overall_rating),
    
    FOREIGN KEY (order_id) REFERENCES ubereats_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- WSTAW DOMYŚLNE DANE
-- ========================================

-- ========================================
-- ADMINISTRATORZY (hasło: admin123)
-- ========================================
INSERT INTO ubereats_employees (employee_id, email, password, first_name, last_name, phone, role, status, is_active, is_verified, rating, total_deliveries, total_earnings) VALUES
('UE-ADM001', 'admin@ubereats.ls', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'System', 'Admin', '555-0001', 'admin', 'offline', 1, 1, 5.00, 0, 0),
('UE-ADM002', 'tom.boss@ubereats.ls', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Tom', 'Richardson', '555-0002', 'admin', 'offline', 1, 1, 5.00, 0, 0)
ON DUPLICATE KEY UPDATE email = email;

-- ========================================
-- MANAGEROWIE (hasło: manager123)
-- ========================================
INSERT INTO ubereats_employees (employee_id, email, password, first_name, last_name, phone, role, status, is_active, is_verified, rating, total_deliveries, total_earnings) VALUES
('UE-MGR001', 'manager@ubereats.ls', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'John', 'Manager', '555-0100', 'manager', 'offline', 1, 1, 5.00, 0, 0),
('UE-MGR002', 'anna.kowalski@ubereats.ls', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Anna', 'Kowalski', '555-0101', 'manager', 'offline', 1, 1, 5.00, 0, 0),
('UE-MGR003', 'james.wilson@ubereats.ls', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'James', 'Wilson', '555-0102', 'manager', 'available', 1, 1, 5.00, 0, 0)
ON DUPLICATE KEY UPDATE email = email;

-- ========================================
-- KIEROWCY (hasło: driver123)
-- ========================================
INSERT INTO ubereats_employees (employee_id, email, password, first_name, last_name, phone, role, vehicle_type, vehicle_plate, vehicle_model, vehicle_color, status, is_active, is_verified, rating, total_deliveries, total_earnings, total_tips) VALUES
-- Kierowcy samochodowi
('UE-DRV001', 'mike.johnson@ubereats.ls', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Mike', 'Johnson', '555-1001', 'driver', 'car', 'LS-1234', 'Toyota Corolla', 'Silver', 'available', 1, 1, 4.85, 342, 4250.00, 680.50),
('UE-DRV002', 'sarah.williams@ubereats.ls', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Sarah', 'Williams', '555-1002', 'driver', 'car', 'LS-5678', 'Honda Civic', 'Blue', 'busy', 1, 1, 4.92, 567, 7120.00, 1245.00),
('UE-DRV003', 'david.martinez@ubereats.ls', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'David', 'Martinez', '555-1003', 'driver', 'car', 'LS-9012', 'Ford Focus', 'Red', 'offline', 1, 1, 4.78, 234, 2890.00, 412.30),
('UE-DRV004', 'jessica.brown@ubereats.ls', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Jessica', 'Brown', '555-1004', 'driver', 'car', 'LS-3456', 'Chevrolet Malibu', 'White', 'available', 1, 1, 4.95, 789, 9650.00, 1890.00),

-- Kierowcy na skuterach
('UE-DRV005', 'carlos.rodriguez@ubereats.ls', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Carlos', 'Rodriguez', '555-1005', 'driver', 'scooter', 'SC-1111', 'Vespa Primavera', 'Green', 'available', 1, 1, 4.88, 456, 5230.00, 890.00),
('UE-DRV006', 'maria.garcia@ubereats.ls', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Maria', 'Garcia', '555-1006', 'driver', 'scooter', 'SC-2222', 'Honda PCX', 'Black', 'busy', 1, 1, 4.91, 623, 7450.00, 1320.00),

-- Kierowcy na motocyklach
('UE-DRV007', 'kevin.lee@ubereats.ls', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Kevin', 'Lee', '555-1007', 'driver', 'motorcycle', 'MC-1234', 'Yamaha MT-07', 'Black', 'available', 1, 1, 4.72, 189, 2340.00, 345.00),
('UE-DRV008', 'jason.taylor@ubereats.ls', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Jason', 'Taylor', '555-1008', 'driver', 'motorcycle', 'MC-5678', 'Kawasaki Ninja', 'Green', 'offline', 1, 1, 4.65, 156, 1920.00, 278.00),

-- Kierowcy rowerowi
('UE-DRV009', 'emily.davis@ubereats.ls', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Emily', 'Davis', '555-1009', 'driver', 'bike', NULL, 'Trek FX3', 'Blue', 'available', 1, 1, 4.96, 834, 8920.00, 2150.00),
('UE-DRV010', 'ryan.anderson@ubereats.ls', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Ryan', 'Anderson', '555-1010', 'driver', 'bike', NULL, 'Specialized Sirrus', 'Red', 'busy', 1, 1, 4.89, 567, 6230.00, 1456.00),
('UE-DRV011', 'alex.thompson@ubereats.ls', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Alex', 'Thompson', '555-1011', 'driver', 'bike', NULL, 'Giant Escape', 'Black', 'available', 1, 1, 4.82, 423, 4780.00, 890.00),

-- Nowi kierowcy (jeszcze bez dostaw)
('UE-DRV012', 'nowy.kierowca@ubereats.ls', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Tomasz', 'Nowak', '555-1012', 'driver', 'car', 'LS-NEW1', 'Kia Rio', 'Gray', 'offline', 1, 0, 5.00, 0, 0, 0),
('UE-DRV013', 'probny.kurier@ubereats.ls', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Katarzyna', 'Wiśniewska', '555-1013', 'driver', 'scooter', 'SC-NEW1', 'Piaggio Liberty', 'White', 'offline', 1, 0, 5.00, 0, 0, 0),

-- Nieaktywni kierowcy
('UE-DRV014', 'inactive.driver@ubereats.ls', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Mark', 'Wilson', '555-1014', 'driver', 'car', 'LS-OLD1', 'Nissan Sentra', 'Black', 'offline', 0, 1, 4.20, 45, 540.00, 67.00),
('UE-DRV015', 'fired.driver@ubereats.ls', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Steve', 'Jackson', '555-1015', 'driver', 'motorcycle', 'MC-OLD1', 'Honda CB500', 'Yellow', 'offline', 0, 1, 3.50, 28, 320.00, 35.00)
ON DUPLICATE KEY UPDATE email = email;

-- Domyślna restauracja Burgershot
INSERT INTO ubereats_restaurants (name, slug, description, address, city, cuisine_type, price_range, average_prep_time, delivery_fee, rating, is_active, is_featured) VALUES
('Burgershot', 'burgershot', 'Najlepsze burgery w Los Santos! Szybka obsługa, świeże składniki.', 'Vinewood Blvd 123', 'Los Santos', 'Fast Food', '$$', 15, 2.99, 4.8, 1, 1),
('Cluckin Bell', 'cluckin-bell', 'Kurczak smażony i grillowany - tradycja Los Santos od 1984.', 'Market St 456', 'Los Santos', 'Fast Food', '$', 12, 2.49, 4.5, 1, 1),
('Pizza This', 'pizza-this', 'Autentyczna włoska pizza pieczona w piecu opalanym drewnem.', 'Little Italy 78', 'Los Santos', 'Italian', '$$$', 25, 3.99, 4.7, 1, 0),
('Taco Bomb', 'taco-bomb', 'Eksplozja meksykańskich smaków! Tacos, burritos, nachos.', 'El Rancho Blvd 234', 'Los Santos', 'Mexican', '$', 10, 1.99, 4.3, 1, 0),
('Up-n-Atom Burger', 'up-n-atom', 'Burgery atomowe - porcje które cię rozwalą!', 'Route 68', 'Sandy Shores', 'Fast Food', '$$', 18, 4.99, 4.1, 1, 0)
ON DUPLICATE KEY UPDATE name = name;

-- ========================================
-- PRZYKŁADOWI KLIENCI (hasło: admin123)
-- ========================================
INSERT INTO ubereats_customers (email, password, first_name, last_name, phone, addresses, total_orders, total_spent, is_active, is_verified) VALUES
('jan.kowalski@email.com', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Jan', 'Kowalski', '555-2001', '[{"id": 1, "label": "Dom", "address": "Grove Street 15", "city": "Los Santos"}, {"id": 2, "label": "Praca", "address": "Vinewood Hills 88", "city": "Los Santos"}]', 23, 345.67, 1, 1),
('anna.nowak@email.com', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Anna', 'Nowak', '555-2002', '[{"id": 1, "label": "Dom", "address": "Strawberry Ave 42", "city": "Los Santos"}]', 56, 789.12, 1, 1),
('piotr.wisniewski@email.com', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Piotr', 'Wiśniewski', '555-2003', '[{"id": 1, "label": "Apartament", "address": "Alta Street 101", "city": "Los Santos"}]', 12, 156.89, 1, 1),
('marta.zielinska@email.com', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Marta', 'Zielińska', '555-2004', '[{"id": 1, "label": "Dom", "address": "Mirror Park Blvd 67", "city": "Los Santos"}]', 89, 1234.56, 1, 1),
('tomek.majewski@email.com', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Tomek', 'Majewski', '555-2005', '[{"id": 1, "label": "Biuro", "address": "Maze Bank Tower, Floor 32", "city": "Los Santos"}]', 34, 567.89, 1, 1),
('kasia.lewandowska@email.com', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Kasia', 'Lewandowska', '555-2006', '[{"id": 1, "label": "Studio", "address": "Del Perro Beach 23", "city": "Los Santos"}]', 67, 890.23, 1, 1),
('marcin.wojcik@email.com', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Marcin', 'Wójcik', '555-2007', '[{"id": 1, "label": "Dom", "address": "Rockford Hills 156", "city": "Los Santos"}]', 15, 234.56, 1, 1),
('ewa.kaminska@email.com', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Ewa', 'Kamińska', '555-2008', '[{"id": 1, "label": "Mieszkanie", "address": "Vespucci Canals 89", "city": "Los Santos"}]', 45, 678.90, 1, 1),
('adam.szymanski@email.com', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Adam', 'Szymański', '555-2009', '[{"id": 1, "label": "Dom", "address": "Paleto Bay 45", "city": "Paleto Bay"}]', 8, 98.76, 1, 1),
('natalia.dabrowska@email.com', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 'Natalia', 'Dąbrowska', '555-2010', '[{"id": 1, "label": "Willa", "address": "Vinewood Hills 200", "city": "Los Santos"}]', 123, 2345.67, 1, 1)
ON DUPLICATE KEY UPDATE email = email;

-- Produkty Burgershot
INSERT INTO ubereats_products (restaurant_id, name, description, category, price, calories, is_popular, sort_order) VALUES
((SELECT id FROM ubereats_restaurants WHERE slug = 'burgershot' LIMIT 1), 'Bleeder Burger', 'Klasyczny burger z podwójnym kotletem wołowym, serem, sałatą i sosem specjalnym', 'burgers', 5.99, 650, 1, 1),
((SELECT id FROM ubereats_restaurants WHERE slug = 'burgershot' LIMIT 1), 'Heart Stopper', 'Najbardziej intensywny burger - potrójne mięso, bekon, ser, cebula', 'burgers', 8.99, 1100, 1, 2),
((SELECT id FROM ubereats_restaurants WHERE slug = 'burgershot' LIMIT 1), 'Money Shot', 'Premium burger z wagyu, truflami i złotą bułką', 'burgers', 12.99, 850, 0, 3),
((SELECT id FROM ubereats_restaurants WHERE slug = 'burgershot' LIMIT 1), 'Meat Free', 'Burger wegetariański z kotletem roślinnym', 'burgers', 6.49, 420, 0, 4),
((SELECT id FROM ubereats_restaurants WHERE slug = 'burgershot' LIMIT 1), 'Frytki', 'Chrupiące złote frytki', 'sides', 2.49, 340, 1, 10),
((SELECT id FROM ubereats_restaurants WHERE slug = 'burgershot' LIMIT 1), 'Onion Rings', 'Chrupiące krążki cebulowe', 'sides', 3.49, 410, 0, 11),
((SELECT id FROM ubereats_restaurants WHERE slug = 'burgershot' LIMIT 1), 'Nuggetsy', '6 sztuk złocistych nuggetsów z kurczaka', 'sides', 4.49, 280, 1, 12),
((SELECT id FROM ubereats_restaurants WHERE slug = 'burgershot' LIMIT 1), 'Sprunk', 'Orzeźwiający napój cytrynowo-limonkowy', 'drinks', 1.99, 140, 1, 20),
((SELECT id FROM ubereats_restaurants WHERE slug = 'burgershot' LIMIT 1), 'eCola', 'Klasyczna cola', 'drinks', 1.99, 150, 1, 21),
((SELECT id FROM ubereats_restaurants WHERE slug = 'burgershot' LIMIT 1), 'Shake', 'Kremowy shake', 'drinks', 3.49, 420, 1, 22),
((SELECT id FROM ubereats_restaurants WHERE slug = 'burgershot' LIMIT 1), 'Lody Soft', 'Kremowe lody w rożku', 'desserts', 1.99, 200, 1, 30),
((SELECT id FROM ubereats_restaurants WHERE slug = 'burgershot' LIMIT 1), 'McFlurry', 'Lody z dodatkami', 'desserts', 3.49, 380, 1, 31)
ON DUPLICATE KEY UPDATE name = name;

-- Przykładowe kody promocyjne
INSERT INTO ubereats_promo_codes (code, description, discount_type, discount_value, minimum_order, usage_limit, is_active) VALUES
('WELCOME20', 'Rabat 20% dla nowych klientów', 'percentage', 20.00, 10.00, NULL, 1),
('BURGERSHOT10', 'Rabat $10 w Burgershot', 'fixed', 10.00, 20.00, 100, 1),
('FREEDEL', 'Darmowa dostawa', 'fixed', 2.99, 15.00, 50, 1),
('SUMMER15', 'Letnia promocja 15% rabatu', 'percentage', 15.00, 15.00, 200, 1),
('VIP50', 'Rabat VIP $50 (min. $100)', 'fixed', 50.00, 100.00, 10, 1),
('LUNCH25', 'Rabat lunchowy 25% (11:00-14:00)', 'percentage', 25.00, 12.00, NULL, 1),
('NEWDRIVER', 'Pierwszy kierowca - darmowa dostawa', 'fixed', 5.00, 0.00, 100, 1),
('PIZZA20', 'Rabat 20% na pizze', 'percentage', 20.00, 20.00, 50, 1),
('WEEKEND10', 'Weekendowy rabat 10%', 'percentage', 10.00, 8.00, NULL, 1),
('RODZINA30', 'Zamówienie rodzinne - $30 rabatu', 'fixed', 30.00, 80.00, 25, 1)
ON DUPLICATE KEY UPDATE code = code;

-- ========================================
-- PRZYKŁADOWE ZAMÓWIENIA
-- ========================================
INSERT INTO ubereats_orders (order_number, customer_id, customer_name, customer_email, customer_phone, restaurant_id, restaurant_name, driver_id, driver_assigned_at, pickup_address, delivery_address, delivery_city, order_type, status, payment_method, payment_status, subtotal, delivery_fee, service_fee, tax, tip, total, driver_earnings, driver_tip, created_at, delivered_at) VALUES
-- Zamówienia dostarczone
('UE-20241201-001', 1, 'Jan Kowalski', 'jan.kowalski@email.com', '555-2001', 1, 'Burgershot', 1, '2024-12-01 12:05:00', 'Vinewood Blvd 123', 'Grove Street 15', 'Los Santos', 'delivery', 'delivered', 'card', 'paid', 24.97, 2.99, 0.99, 2.50, 3.00, 34.45, 8.50, 3.00, '2024-12-01 12:00:00', '2024-12-01 12:35:00'),
('UE-20241201-002', 2, 'Anna Nowak', 'anna.nowak@email.com', '555-2002', 1, 'Burgershot', 2, '2024-12-01 13:10:00', 'Vinewood Blvd 123', 'Strawberry Ave 42', 'Los Santos', 'delivery', 'delivered', 'card', 'paid', 35.95, 2.99, 0.99, 3.60, 5.00, 48.53, 9.20, 5.00, '2024-12-01 13:00:00', '2024-12-01 13:42:00'),
('UE-20241201-003', 3, 'Piotr Wiśniewski', 'piotr.wisniewski@email.com', '555-2003', 2, 'Cluckin Bell', 5, '2024-12-01 14:08:00', 'Market St 456', 'Alta Street 101', 'Los Santos', 'delivery', 'delivered', 'cash', 'paid', 18.99, 2.49, 0.99, 1.90, 2.00, 26.37, 7.80, 2.00, '2024-12-01 14:00:00', '2024-12-01 14:28:00'),
('UE-20241201-004', 4, 'Marta Zielińska', 'marta.zielinska@email.com', '555-2004', 3, 'Pizza This', 9, '2024-12-01 19:15:00', 'Little Italy 78', 'Mirror Park Blvd 67', 'Los Santos', 'delivery', 'delivered', 'card', 'paid', 42.50, 3.99, 0.99, 4.25, 8.00, 59.73, 11.50, 8.00, '2024-12-01 19:00:00', '2024-12-01 19:45:00'),
('UE-20241202-001', 5, 'Tomek Majewski', 'tomek.majewski@email.com', '555-2005', 1, 'Burgershot', 4, '2024-12-02 12:12:00', 'Vinewood Blvd 123', 'Maze Bank Tower, Floor 32', 'Los Santos', 'delivery', 'delivered', 'apple_pay', 'paid', 56.89, 2.99, 0.99, 5.69, 10.00, 76.56, 12.30, 10.00, '2024-12-02 12:00:00', '2024-12-02 12:38:00'),
('UE-20241202-002', 6, 'Kasia Lewandowska', 'kasia.lewandowska@email.com', '555-2006', 4, 'Taco Bomb', 10, '2024-12-02 13:05:00', 'El Rancho Blvd 234', 'Del Perro Beach 23', 'Los Santos', 'delivery', 'delivered', 'card', 'paid', 22.45, 1.99, 0.99, 2.25, 4.00, 31.68, 7.50, 4.00, '2024-12-02 13:00:00', '2024-12-02 13:25:00'),
('UE-20241202-003', 7, 'Marcin Wójcik', 'marcin.wojcik@email.com', '555-2007', 1, 'Burgershot', 1, '2024-12-02 18:20:00', 'Vinewood Blvd 123', 'Rockford Hills 156', 'Los Santos', 'delivery', 'delivered', 'google_pay', 'paid', 31.97, 2.99, 0.99, 3.20, 5.00, 44.15, 9.00, 5.00, '2024-12-02 18:10:00', '2024-12-02 18:48:00'),
('UE-20241203-001', 8, 'Ewa Kamińska', 'ewa.kaminska@email.com', '555-2008', 3, 'Pizza This', 6, '2024-12-03 20:10:00', 'Little Italy 78', 'Vespucci Canals 89', 'Los Santos', 'delivery', 'delivered', 'card', 'paid', 38.99, 3.99, 0.99, 3.90, 6.00, 53.87, 10.80, 6.00, '2024-12-03 20:00:00', '2024-12-03 20:42:00'),
('UE-20241203-002', 10, 'Natalia Dąbrowska', 'natalia.dabrowska@email.com', '555-2010', 1, 'Burgershot', 2, '2024-12-03 12:08:00', 'Vinewood Blvd 123', 'Vinewood Hills 200', 'Los Santos', 'delivery', 'delivered', 'card', 'paid', 67.85, 2.99, 0.99, 6.79, 15.00, 93.62, 14.50, 15.00, '2024-12-03 12:00:00', '2024-12-03 12:50:00'),
('UE-20241203-003', 1, 'Jan Kowalski', 'jan.kowalski@email.com', '555-2001', 2, 'Cluckin Bell', 11, '2024-12-03 19:12:00', 'Market St 456', 'Grove Street 15', 'Los Santos', 'delivery', 'delivered', 'card', 'paid', 28.50, 2.49, 0.99, 2.85, 4.00, 38.83, 8.20, 4.00, '2024-12-03 19:00:00', '2024-12-03 19:35:00'),

-- Zamówienia w toku (dzisiejsze)
('UE-20241204-001', 2, 'Anna Nowak', 'anna.nowak@email.com', '555-2002', 1, 'Burgershot', 5, NOW(), 'Vinewood Blvd 123', 'Strawberry Ave 42', 'Los Santos', 'delivery', 'delivering', 'card', 'paid', 19.98, 2.99, 0.99, 2.00, 3.00, 28.96, 7.80, 3.00, NOW() - INTERVAL 25 MINUTE, NULL),
('UE-20241204-002', 4, 'Marta Zielińska', 'marta.zielinska@email.com', '555-2004', 3, 'Pizza This', 9, NOW() - INTERVAL 10 MINUTE, 'Little Italy 78', 'Mirror Park Blvd 67', 'Los Santos', 'delivery', 'picked_up', 'card', 'paid', 45.00, 3.99, 0.99, 4.50, 7.00, 61.48, 12.00, 7.00, NOW() - INTERVAL 35 MINUTE, NULL),
('UE-20241204-003', 6, 'Kasia Lewandowska', 'kasia.lewandowska@email.com', '555-2006', 4, 'Taco Bomb', NULL, NULL, 'El Rancho Blvd 234', 'Del Perro Beach 23', 'Los Santos', 'delivery', 'preparing', 'card', 'paid', 15.99, 1.99, 0.99, 1.60, 2.00, 22.57, 0.00, 0.00, NOW() - INTERVAL 15 MINUTE, NULL),
('UE-20241204-004', 7, 'Marcin Wójcik', 'marcin.wojcik@email.com', '555-2007', 1, 'Burgershot', NULL, NULL, 'Vinewood Blvd 123', 'Rockford Hills 156', 'Los Santos', 'delivery', 'confirmed', 'cash', 'pending', 24.97, 2.99, 0.99, 2.50, 0.00, 31.45, 0.00, 0.00, NOW() - INTERVAL 5 MINUTE, NULL),
('UE-20241204-005', 10, 'Natalia Dąbrowska', 'natalia.dabrowska@email.com', '555-2010', 2, 'Cluckin Bell', NULL, NULL, 'Market St 456', 'Vinewood Hills 200', 'Los Santos', 'delivery', 'pending', 'card', 'pending', 32.45, 2.49, 0.99, 3.25, 5.00, 44.18, 0.00, 0.00, NOW(), NULL),

-- Zamówienia anulowane
('UE-20241130-001', 3, 'Piotr Wiśniewski', 'piotr.wisniewski@email.com', '555-2003', 1, 'Burgershot', NULL, NULL, 'Vinewood Blvd 123', 'Alta Street 101', 'Los Santos', 'delivery', 'cancelled', 'card', 'refunded', 18.99, 2.99, 0.99, 1.90, 0.00, 24.87, 0.00, 0.00, '2024-11-30 15:00:00', NULL),
('UE-20241129-001', 8, 'Ewa Kamińska', 'ewa.kaminska@email.com', '555-2008', 3, 'Pizza This', 7, '2024-11-29 19:10:00', 'Little Italy 78', 'Vespucci Canals 89', 'Los Santos', 'delivery', 'cancelled', 'card', 'refunded', 35.00, 3.99, 0.99, 3.50, 0.00, 43.48, 0.00, 0.00, '2024-11-29 19:00:00', NULL)
ON DUPLICATE KEY UPDATE order_number = order_number;

-- ========================================
-- POZYCJE ZAMÓWIEŃ
-- ========================================
INSERT INTO ubereats_order_items (order_id, product_id, product_name, quantity, unit_price, total_price, special_instructions) VALUES
-- Zamówienie 1
(1, 1, 'Bleeder Burger', 2, 5.99, 11.98, NULL),
(1, 5, 'Frytki', 2, 2.49, 4.98, 'Dodatkowa sól'),
(1, 8, 'Sprunk', 2, 1.99, 3.98, NULL),
(1, 11, 'Lody Soft', 2, 1.99, 3.98, NULL),
-- Zamówienie 2
(2, 2, 'Heart Stopper', 2, 8.99, 17.98, NULL),
(2, 3, 'Money Shot', 1, 12.99, 12.99, 'Bez cebuli'),
(2, 10, 'Shake', 1, 3.49, 3.49, 'Czekoladowy'),
-- Zamówienie 3
(3, NULL, 'Zestaw Kurczaka', 1, 12.99, 12.99, NULL),
(3, NULL, 'Skrzydełka Hot', 1, 5.99, 5.99, 'Bardzo ostre'),
-- Zamówienie 4
(4, NULL, 'Pizza Margherita', 1, 18.50, 18.50, NULL),
(4, NULL, 'Pizza Pepperoni', 1, 22.00, 22.00, 'Podwójny ser'),
(4, NULL, 'Coca-Cola 2L', 1, 2.00, 2.00, NULL)
ON DUPLICATE KEY UPDATE order_id = order_id;

-- ========================================
-- HISTORIA LOGOWAŃ KIEROWCÓW
-- ========================================
INSERT INTO ubereats_driver_logins (driver_id, email, ip, user_agent, device_type, success, reason, created_at) VALUES
-- Udane logowania
(1, 'mike.johnson@ubereats.ls', '192.168.1.101', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) Chrome/120.0', 'desktop', 1, 'ok', '2024-12-04 08:00:00'),
(2, 'sarah.williams@ubereats.ls', '192.168.1.102', 'Mozilla/5.0 (iPhone; CPU iPhone OS 17_0) Safari/605.1', 'mobile', 1, 'ok', '2024-12-04 08:15:00'),
(5, 'carlos.rodriguez@ubereats.ls', '192.168.1.105', 'Mozilla/5.0 (Android 14) Chrome/120.0', 'mobile', 1, 'ok', '2024-12-04 08:30:00'),
(9, 'emily.davis@ubereats.ls', '192.168.1.109', 'Mozilla/5.0 (iPad; CPU OS 17_0) Safari/605.1', 'tablet', 1, 'ok', '2024-12-04 09:00:00'),
(6, 'maria.garcia@ubereats.ls', '192.168.1.106', 'Mozilla/5.0 (Android 14) Chrome/120.0', 'mobile', 1, 'ok', '2024-12-04 09:15:00'),
(10, 'ryan.anderson@ubereats.ls', '192.168.1.110', 'Mozilla/5.0 (iPhone; CPU iPhone OS 17_0) Safari/605.1', 'mobile', 1, 'ok', '2024-12-04 09:30:00'),
(1, 'mike.johnson@ubereats.ls', '192.168.1.101', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) Chrome/120.0', 'desktop', 1, 'ok', '2024-12-03 07:45:00'),
(2, 'sarah.williams@ubereats.ls', '192.168.1.102', 'Mozilla/5.0 (iPhone; CPU iPhone OS 17_0) Safari/605.1', 'mobile', 1, 'ok', '2024-12-03 08:00:00'),
(4, 'jessica.brown@ubereats.ls', '192.168.1.104', 'Mozilla/5.0 (Android 14) Chrome/120.0', 'mobile', 1, 'ok', '2024-12-03 08:30:00'),

-- Nieudane logowania
(NULL, 'fake.driver@ubereats.ls', '45.67.89.123', 'Mozilla/5.0 (Windows NT 10.0) Chrome/120.0', 'desktop', 0, 'not_found', '2024-12-04 03:15:00'),
(1, 'mike.johnson@ubereats.ls', '123.45.67.89', 'Mozilla/5.0 (Linux) Chrome/120.0', 'desktop', 0, 'wrong_password', '2024-12-04 02:30:00'),
(NULL, 'hacker@evil.com', '66.77.88.99', 'curl/7.68.0', 'desktop', 0, 'not_found', '2024-12-03 22:45:00'),
(14, 'inactive.driver@ubereats.ls', '192.168.1.114', 'Mozilla/5.0 (Windows NT 10.0) Chrome/120.0', 'desktop', 0, 'account_disabled', '2024-12-03 10:00:00'),
(2, 'sarah.williams@ubereats.ls', '192.168.1.102', 'Mozilla/5.0 (iPhone; CPU iPhone OS 17_0) Safari/605.1', 'mobile', 0, 'wrong_password', '2024-12-02 15:30:00')
ON DUPLICATE KEY UPDATE driver_id = driver_id;

-- ========================================
-- HISTORIA LOGOWAŃ BOSSÓW
-- ========================================
INSERT INTO ubereats_boss_logins (boss_id, email, ip, user_agent, success, reason, created_at) VALUES
-- Udane logowania
(1, 'admin@ubereats.ls', '192.168.1.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) Chrome/120.0', 1, 'ok', '2024-12-04 07:00:00'),
(3, 'manager@ubereats.ls', '192.168.1.100', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) Firefox/121.0', 1, 'ok', '2024-12-04 07:30:00'),
(4, 'anna.kowalski@ubereats.ls', '192.168.1.101', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 14_0) Safari/17.0', 1, 'ok', '2024-12-04 08:00:00'),
(1, 'admin@ubereats.ls', '192.168.1.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) Chrome/120.0', 1, 'ok', '2024-12-03 07:00:00'),
(3, 'manager@ubereats.ls', '192.168.1.100', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) Firefox/121.0', 1, 'ok', '2024-12-03 07:15:00'),
(5, 'james.wilson@ubereats.ls', '192.168.1.102', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) Edge/120.0', 1, 'ok', '2024-12-03 08:30:00'),

-- Nieudane logowania
(NULL, 'admin@test.com', '45.67.89.100', 'Mozilla/5.0 (Windows NT 10.0) Chrome/120.0', 0, 'not_found', '2024-12-04 04:00:00'),
(1, 'admin@ubereats.ls', '88.99.11.22', 'curl/7.68.0', 0, 'wrong_password', '2024-12-04 03:45:00'),
(NULL, 'root@ubereats.ls', '66.77.88.99', 'python-requests/2.28.0', 0, 'not_found', '2024-12-03 23:00:00'),
(3, 'manager@ubereats.ls', '192.168.1.100', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) Firefox/121.0', 0, 'wrong_password', '2024-12-02 09:00:00')
ON DUPLICATE KEY UPDATE boss_id = boss_id;

-- ========================================
-- SESJE KIEROWCÓW
-- ========================================
INSERT INTO ubereats_driver_sessions (driver_id, session_start, session_end, duration_minutes, status, deliveries_count, earnings, tips, distance_km) VALUES
-- Dzisiejsze sesje
(1, '2024-12-04 08:00:00', NULL, 0, 'available', 3, 25.50, 8.00, 12.5),
(2, '2024-12-04 08:15:00', NULL, 0, 'busy', 5, 46.00, 15.00, 22.3),
(5, '2024-12-04 08:30:00', NULL, 0, 'available', 4, 31.20, 10.00, 18.7),
(6, '2024-12-04 09:15:00', NULL, 0, 'busy', 2, 17.60, 6.00, 8.4),
(9, '2024-12-04 09:00:00', NULL, 0, 'available', 6, 58.00, 22.00, 35.2),
(10, '2024-12-04 09:30:00', NULL, 0, 'busy', 3, 22.50, 9.00, 14.1),

-- Wczorajsze sesje (zakończone)
(1, '2024-12-03 07:45:00', '2024-12-03 16:30:00', 525, 'offline', 12, 105.60, 42.00, 58.3),
(2, '2024-12-03 08:00:00', '2024-12-03 18:00:00', 600, 'offline', 15, 138.00, 55.00, 72.1),
(4, '2024-12-03 08:30:00', '2024-12-03 15:00:00', 390, 'offline', 8, 72.00, 28.00, 38.5),
(5, '2024-12-03 09:00:00', '2024-12-03 17:30:00', 510, 'offline', 11, 85.80, 33.00, 52.0),
(9, '2024-12-03 07:00:00', '2024-12-03 19:00:00', 720, 'offline', 18, 158.40, 68.00, 95.6)
ON DUPLICATE KEY UPDATE driver_id = driver_id;

-- ========================================
-- HISTORIA STATUSÓW ZAMÓWIEŃ
-- ========================================
INSERT INTO ubereats_order_status_history (order_id, status, previous_status, changed_by_type, changed_by_name, notes, created_at) VALUES
-- Zamówienie 1
(1, 'pending', NULL, 'customer', 'Jan Kowalski', 'Zamówienie złożone', '2024-12-01 12:00:00'),
(1, 'confirmed', 'pending', 'restaurant', 'Burgershot', 'Zamówienie przyjęte', '2024-12-01 12:02:00'),
(1, 'preparing', 'confirmed', 'restaurant', 'Burgershot', 'Rozpoczęto przygotowanie', '2024-12-01 12:05:00'),
(1, 'ready_for_pickup', 'preparing', 'restaurant', 'Burgershot', 'Gotowe do odbioru', '2024-12-01 12:18:00'),
(1, 'picked_up', 'ready_for_pickup', 'driver', 'Mike Johnson', 'Kierowca odebrał zamówienie', '2024-12-01 12:20:00'),
(1, 'delivering', 'picked_up', 'driver', 'Mike Johnson', 'W drodze do klienta', '2024-12-01 12:22:00'),
(1, 'delivered', 'delivering', 'driver', 'Mike Johnson', 'Zamówienie dostarczone', '2024-12-01 12:35:00'),

-- Zamówienie 11 (w trakcie)
(11, 'pending', NULL, 'customer', 'Anna Nowak', 'Zamówienie złożone', NOW() - INTERVAL 25 MINUTE),
(11, 'confirmed', 'pending', 'restaurant', 'Burgershot', 'Zamówienie przyjęte', NOW() - INTERVAL 23 MINUTE),
(11, 'preparing', 'confirmed', 'restaurant', 'Burgershot', 'Rozpoczęto przygotowanie', NOW() - INTERVAL 20 MINUTE),
(11, 'ready_for_pickup', 'preparing', 'restaurant', 'Burgershot', 'Gotowe do odbioru', NOW() - INTERVAL 10 MINUTE),
(11, 'picked_up', 'ready_for_pickup', 'driver', 'Carlos Rodriguez', 'Kierowca odebrał', NOW() - INTERVAL 8 MINUTE),
(11, 'delivering', 'picked_up', 'driver', 'Carlos Rodriguez', 'W drodze', NOW() - INTERVAL 5 MINUTE),

-- Zamówienie anulowane
(16, 'pending', NULL, 'customer', 'Piotr Wiśniewski', 'Zamówienie złożone', '2024-11-30 15:00:00'),
(16, 'confirmed', 'pending', 'restaurant', 'Burgershot', 'Zamówienie przyjęte', '2024-11-30 15:03:00'),
(16, 'cancelled', 'confirmed', 'customer', 'Piotr Wiśniewski', 'Klient anulował - zmiana planów', '2024-11-30 15:10:00')
ON DUPLICATE KEY UPDATE order_id = order_id;

-- ========================================
-- RECENZJE
-- ========================================
INSERT INTO ubereats_reviews (order_id, customer_id, overall_rating, food_rating, delivery_rating, review_text, is_visible, created_at) VALUES
(1, 1, 5, 5, 5, 'Świetne burgery! Szybka dostawa, gorące jedzenie. Polecam!', 1, '2024-12-01 13:00:00'),
(2, 2, 4, 5, 4, 'Jedzenie pyszne, ale dostawa trochę się opóźniła.', 1, '2024-12-01 14:00:00'),
(3, 3, 5, 5, 5, 'Kurczak był idealny! Kierowca bardzo miły.', 1, '2024-12-01 15:00:00'),
(4, 4, 5, 5, 5, 'Najlepsza pizza w Los Santos! Gorąca i chrupiąca.', 1, '2024-12-01 20:00:00'),
(5, 5, 4, 4, 5, 'Dobry burger, szybka dostawa do biura.', 1, '2024-12-02 13:00:00'),
(6, 6, 5, 5, 5, 'Tacos były rewelacyjne! Na pewno zamówię znowu.', 1, '2024-12-02 14:00:00'),
(8, 8, 4, 5, 3, 'Pizza pyszna, ale kierowca się trochę zgubił.', 1, '2024-12-03 21:00:00'),
(9, 10, 5, 5, 5, 'Mega zamówienie na imprezę - wszystko perfekcyjne!', 1, '2024-12-03 13:30:00')
ON DUPLICATE KEY UPDATE order_id = order_id;

-- ========================================
-- POWIADOMIENIA
-- ========================================
INSERT INTO ubereats_notifications (recipient_type, recipient_id, title, message, notification_type, is_read, channel, created_at) VALUES
-- Powiadomienia dla kierowców
('driver', 1, 'Nowe zamówienie!', 'Masz nowe zamówienie do odbioru w Burgershot. Adres: Grove Street 15', 'order', 0, 'push', NOW()),
('driver', 2, 'Gratulacje!', 'Osiągnąłeś 500 dostaw! Dziękujemy za Twoją pracę.', 'system', 0, 'in_app', NOW() - INTERVAL 1 DAY),
('driver', 5, 'Bonus weekendowy!', 'W ten weekend zarabiasz +20% za każdą dostawę!', 'promo', 1, 'push', NOW() - INTERVAL 2 DAY),
('driver', 9, 'Najlepszy tydzień!', 'Byłeś najlepszym kierowcą tygodnia z 95 dostawami!', 'system', 1, 'in_app', NOW() - INTERVAL 7 DAY),

-- Powiadomienia dla klientów
('customer', 1, 'Twoje zamówienie w drodze!', 'Kierowca Mike jest w drodze z Twoim zamówieniem. ETA: 15 min', 'order', 1, 'push', '2024-12-01 12:22:00'),
('customer', 2, 'Zamówienie dostarczone', 'Twoje zamówienie zostało dostarczone. Smacznego!', 'order', 1, 'push', '2024-12-01 13:42:00'),
('customer', 4, 'Nowa promocja!', 'Użyj kodu PIZZA20 i otrzymaj 20% rabatu na pizze!', 'promo', 0, 'email', NOW() - INTERVAL 1 DAY),
('customer', 10, 'VIP Status!', 'Gratulacje! Osiągnąłeś status VIP z 100+ zamówieniami.', 'system', 0, 'in_app', NOW() - INTERVAL 3 DAY),

-- Powiadomienia dla adminów
('admin', 1, 'Raport dzienny', 'Wczorajsze statystyki: 156 zamówień, $4,523.45 przychodu', 'system', 0, 'email', NOW()),
('admin', 1, 'Alert bezpieczeństwa', 'Wykryto 3 nieudane próby logowania z podejrzanego IP', 'alert', 0, 'push', NOW() - INTERVAL 2 HOUR)
ON DUPLICATE KEY UPDATE recipient_type = recipient_type;

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

-- Widok aktywnych kierowców
CREATE OR REPLACE VIEW ubereats_active_drivers AS
SELECT 
    e.id, e.employee_id, e.email, e.first_name, e.last_name, e.phone,
    e.vehicle_type, e.vehicle_plate, e.status, e.rating,
    e.total_deliveries, e.total_earnings,
    e.last_latitude, e.last_longitude, e.last_location_update
FROM ubereats_employees e
WHERE e.role = 'driver' AND e.is_active = 1;

-- Widok dzisiejszych zamówień
CREATE OR REPLACE VIEW ubereats_today_orders AS
SELECT *
FROM ubereats_orders
WHERE DATE(created_at) = CURDATE();

-- Widok statystyk kierowców
CREATE OR REPLACE VIEW ubereats_driver_stats AS
SELECT 
    e.id,
    e.employee_id,
    CONCAT(e.first_name, ' ', e.last_name) AS driver_name,
    e.status,
    e.rating,
    e.total_deliveries,
    e.total_earnings,
    (SELECT COUNT(*) FROM ubereats_orders WHERE driver_id = e.id AND DATE(created_at) = CURDATE()) AS today_deliveries,
    (SELECT SUM(driver_earnings + driver_tip) FROM ubereats_orders WHERE driver_id = e.id AND DATE(created_at) = CURDATE()) AS today_earnings
FROM ubereats_employees e
WHERE e.role = 'driver' AND e.is_active = 1;

-- ========================================
-- ZAKOŃCZENIE
-- ========================================

SELECT 'UberEats database setup completed!' AS message;
SELECT COUNT(*) AS employees_count FROM ubereats_employees;
SELECT COUNT(*) AS restaurants_count FROM ubereats_restaurants;
SELECT COUNT(*) AS products_count FROM ubereats_products;
