-- ========================================
-- UBER EATS SYSTEM - Baza danych
-- ========================================

-- Tabela użytkowników Uber Eats (klienci)
CREATE TABLE IF NOT EXISTS ubereats_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    address VARCHAR(255),
    city VARCHAR(100) DEFAULT 'Los Santos',
    profile_image VARCHAR(255) DEFAULT 'default_avatar.png',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabela pracowników Uber Eats (kierowcy/dostawcy)
CREATE TABLE IF NOT EXISTS ubereats_employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id VARCHAR(20) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    vehicle_type ENUM('bike', 'scooter', 'car', 'motorcycle') DEFAULT 'car',
    vehicle_plate VARCHAR(20),
    role ENUM('driver', 'manager', 'admin') DEFAULT 'driver',
    status ENUM('available', 'busy', 'offline') DEFAULT 'offline',
    rating DECIMAL(3,2) DEFAULT 5.00,
    total_deliveries INT DEFAULT 0,
    total_earnings DECIMAL(10,2) DEFAULT 0.00,
    is_active BOOLEAN DEFAULT TRUE,
    hired_by INT,
    hired_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabela zamówień Uber Eats
CREATE TABLE IF NOT EXISTS ubereats_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(20) NOT NULL UNIQUE,
    user_id INT,
    customer_name VARCHAR(100),
    customer_phone VARCHAR(20),
    customer_email VARCHAR(100),
    
    -- Szczegóły dostawy
    delivery_address VARCHAR(255) NOT NULL,
    delivery_city VARCHAR(100) DEFAULT 'Los Santos',
    delivery_notes TEXT,
    
    -- Restauracja
    restaurant_name VARCHAR(100) DEFAULT 'Burgershot',
    
    -- Status
    status ENUM('pending', 'confirmed', 'preparing', 'ready_for_pickup', 'picked_up', 'delivering', 'delivered', 'cancelled') DEFAULT 'pending',
    
    -- Przypisany kierowca
    driver_id INT,
    driver_assigned_at TIMESTAMP NULL,
    picked_up_at TIMESTAMP NULL,
    delivered_at TIMESTAMP NULL,
    
    -- Płatności
    payment_method ENUM('card', 'cash', 'paypal') DEFAULT 'card',
    payment_status ENUM('pending', 'paid', 'refunded') DEFAULT 'pending',
    
    -- Kwoty
    subtotal DECIMAL(10,2) NOT NULL,
    delivery_fee DECIMAL(10,2) DEFAULT 2.99,
    service_fee DECIMAL(10,2) DEFAULT 0.99,
    tip DECIMAL(10,2) DEFAULT 0.00,
    total DECIMAL(10,2) NOT NULL,
    
    -- Driver earnings
    driver_earnings DECIMAL(10,2) DEFAULT 0.00,
    
    -- Ocena
    customer_rating INT,
    customer_review TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 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,
    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,
    notes TEXT,
    FOREIGN KEY (order_id) REFERENCES ubereats_orders(id) ON DELETE CASCADE
);

-- Tabela historii statusów zamówienia
CREATE TABLE IF NOT EXISTS ubereats_order_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    status VARCHAR(50) NOT NULL,
    changed_by VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES ubereats_orders(id) ON DELETE CASCADE
);

-- Tabela sesji kierowców (lokalizacja, dostępność)
CREATE TABLE IF NOT EXISTS ubereats_driver_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    driver_id INT NOT NULL,
    status ENUM('online', 'offline') DEFAULT 'offline',
    current_lat DECIMAL(10,8),
    current_lng DECIMAL(11,8),
    last_location_update TIMESTAMP,
    session_start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    session_end TIMESTAMP NULL,
    FOREIGN KEY (driver_id) REFERENCES ubereats_employees(id) ON DELETE CASCADE
);

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

-- Domyślny manager (hasło: manager123)
INSERT IGNORE INTO ubereats_employees (employee_id, email, password, first_name, last_name, phone, role, vehicle_type) VALUES
('UE-MGR001', 'manager@ubereats.ls', '$2b$10$rQZ5hJ3h4G6g5k8M9nP0qOxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXx', 'John', 'Manager', '555-0100', 'manager', 'car');

-- Przykładowi kierowcy (hasło: driver123)
INSERT IGNORE INTO ubereats_employees (employee_id, email, password, first_name, last_name, phone, role, vehicle_type, vehicle_plate) VALUES
('UE-DRV001', 'driver1@ubereats.ls', '$2b$10$rQZ5hJ3h4G6g5k8M9nP0qOxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXx', 'Mike', 'Driver', '555-0101', 'driver', 'car', 'LS-1234'),
('UE-DRV002', 'driver2@ubereats.ls', '$2b$10$rQZ5hJ3h4G6g5k8M9nP0qOxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXx', 'Sarah', 'Swift', '555-0102', 'driver', 'scooter', 'LS-5678'),
('UE-DRV003', 'driver3@ubereats.ls', '$2b$10$rQZ5hJ3h4G6g5k8M9nP0qOxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXx', 'Carlos', 'Ramirez', '555-0103', 'driver', 'motorcycle', 'LS-9012');

-- Przykładowi klienci (hasło: user123)
INSERT IGNORE INTO ubereats_users (email, password, first_name, last_name, phone, address) VALUES
('john@email.com', '$2b$10$rQZ5hJ3h4G6g5k8M9nP0qOxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXx', 'John', 'Doe', '555-1001', 'Vinewood Blvd 123'),
('jane@email.com', '$2b$10$rQZ5hJ3h4G6g5k8M9nP0qOxXxXxXxXxXxXxXxXxXxXxXxXxXxXxXx', 'Jane', 'Smith', '555-1002', 'Alta Street 456');

-- ========================================
-- INDEKSY
-- ========================================

CREATE INDEX idx_ubereats_orders_status ON ubereats_orders(status);
CREATE INDEX idx_ubereats_orders_driver ON ubereats_orders(driver_id);
CREATE INDEX idx_ubereats_orders_user ON ubereats_orders(user_id);
CREATE INDEX idx_ubereats_orders_created ON ubereats_orders(created_at);
CREATE INDEX idx_ubereats_employees_role ON ubereats_employees(role);
CREATE INDEX idx_ubereats_employees_status ON ubereats_employees(status);

SELECT 'Uber Eats database setup completed!' as message;
