-- Создание базы данных
CREATE DATABASE IF NOT EXISTS fuel_management 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE fuel_management;

-- Таблица пользователей
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'engineer', 'refueler') NOT NULL,
    full_name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),
    remember_token VARCHAR(100) NULL,
    is_active BOOLEAN DEFAULT TRUE,
    last_login DATETIME NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_role (role),
    INDEX idx_active (is_active),
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Таблица категорий техники
CREATE TABLE IF NOT EXISTS vehicle_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    icon VARCHAR(50) DEFAULT '🚜',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Таблица сельхозтехники
CREATE TABLE IF NOT EXISTS vehicles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    model VARCHAR(100),
    year YEAR,
    license_plate VARCHAR(20),
    type VARCHAR(50),
    category_id INT,
    description TEXT,
    image VARCHAR(255),
    tank_capacity DECIMAL(10,2) NOT NULL,
    fuel_type ENUM('diesel', 'petrol', 'gas', 'electric') DEFAULT 'diesel',
    status ENUM('active', 'maintenance', 'inactive', 'repair') DEFAULT 'active',
    mileage DECIMAL(10,2) DEFAULT 0,
    last_service_date DATE NULL,
    next_service_date DATE NULL,
    purchase_date DATE NULL,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name),
    INDEX idx_status (status),
    INDEX idx_category (category_id),
    INDEX idx_license_plate (license_plate),
    FOREIGN KEY (category_id) REFERENCES vehicle_categories(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Таблица приёмки топлива
CREATE TABLE IF NOT EXISTS fuel_receipts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    receipt_date DATETIME NOT NULL,
    fuel_type ENUM('diesel', 'petrol', 'gas', 'electric') DEFAULT 'diesel',
    fuel_amount DECIMAL(10,2) NOT NULL,
    supplier VARCHAR(255) NOT NULL,
    driver_name VARCHAR(100),
    vehicle_number VARCHAR(20),
    document_number VARCHAR(100),
    quality_check BOOLEAN DEFAULT FALSE,
    notes TEXT,
    received_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_receipt_date (receipt_date),
    INDEX idx_supplier (supplier),
    INDEX idx_fuel_type (fuel_type),
    FOREIGN KEY (received_by) REFERENCES users(id) ON DELETE CASCADE
);

-- Таблица склада топлива
CREATE TABLE IF NOT EXISTS fuel_storage (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fuel_type ENUM('diesel', 'petrol', 'gas', 'electric') DEFAULT 'diesel',
    current_quantity DECIMAL(10,2) DEFAULT 0,
    min_quantity DECIMAL(10,2) DEFAULT 0,
    max_quantity DECIMAL(10,2) DEFAULT 0,
    last_updated DATETIME,
    updated_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_fuel_type (fuel_type),
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Таблица заправок
CREATE TABLE IF NOT EXISTS refuels (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vehicle_id INT NOT NULL,
    user_id INT NOT NULL,
    fuel_amount DECIMAL(10,2) NOT NULL,
    fuel_type ENUM('diesel', 'petrol', 'gas', 'electric') DEFAULT 'diesel',
    refuel_date DATETIME NOT NULL,
    odometer_reading DECIMAL(10,2),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_vehicle_id (vehicle_id),
    INDEX idx_user_id (user_id),
    INDEX idx_refuel_date (refuel_date),
    INDEX idx_fuel_type (fuel_type),
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Таблица отчетов
CREATE TABLE IF NOT EXISTS fuel_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    report_type ENUM('monthly', 'vehicle', 'custom', 'yearly', 'weekly', 'receipt') NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    report_data JSON,
    total_fuel DECIMAL(12,2),
    vehicle_count INT,
    refuel_count INT,
    generated_by INT NOT NULL,
    is_archived BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_report_type (report_type),
    INDEX idx_period (period_start, period_end),
    INDEX idx_generated_by (generated_by),
    INDEX idx_archived (is_archived),
    FOREIGN KEY (generated_by) REFERENCES users(id) ON DELETE CASCADE
);

-- Таблица обслуживания техники
CREATE TABLE IF NOT EXISTS maintenance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vehicle_id INT NOT NULL,
    user_id INT NOT NULL,
    maintenance_type ENUM('routine', 'repair', 'inspection', 'other') DEFAULT 'routine',
    description TEXT NOT NULL,
    maintenance_date DATE NOT NULL,
    next_maintenance_date DATE NULL,
    odometer_reading DECIMAL(10,2),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_vehicle_id (vehicle_id),
    INDEX idx_maintenance_date (maintenance_date),
    INDEX idx_maintenance_type (maintenance_type),
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Таблица уведомлений
CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('info', 'warning', 'error', 'success') DEFAULT 'info',
    is_read BOOLEAN DEFAULT FALSE,
    related_entity ENUM('vehicle', 'refuel', 'maintenance', 'user', 'system', 'receipt') DEFAULT 'system',
    related_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_is_read (is_read),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Таблица настроек системы
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    setting_type ENUM('string', 'number', 'boolean', 'json') DEFAULT 'string',
    description TEXT,
    category VARCHAR(50) DEFAULT 'general',
    is_public BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_setting_key (setting_key),
    INDEX idx_category (category)
);

-- Таблица активности пользователей
CREATE TABLE IF NOT EXISTS user_activities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    activity_type VARCHAR(100) NOT NULL,
    description TEXT NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    entity_type VARCHAR(50),
    entity_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_activity_type (activity_type),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Вставка начальных данных

-- Категории техники
INSERT INTO vehicle_categories (name, description, icon) VALUES
('Тракторы', 'Сельскохозяйственные тракторы различной мощности', '🚜'),
('Комбайны', 'Зерноуборочные и кормоуборочные комбайны', '🌾'),
('Грузовики', 'Грузовые автомобили для перевозки', '🚚'),
('Легковые автомобили', 'Служебные легковые автомобили', '🚗'),
('Спецтехника', 'Специализированная сельхозтехника', '🏗️');

-- Пользователи (пароль: admin123)
INSERT INTO users (username, password, role, full_name, email, phone, created_by) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'Главный Администратор', 'admin@agro.ru', '+7 (999) 123-45-67', NULL),
('engineer1', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'engineer', 'Иван Петров', 'engineer@agro.ru', '+7 (999) 123-45-68', 1),
('refueler1', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'refueler', 'Сергей Сидоров', 'refueler@agro.ru', '+7 (999) 123-45-69', 1),
('agromanager', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'engineer', 'Мария Иванова', 'maria@agro.ru', '+7 (999) 123-45-70', 1);

-- Техника
INSERT INTO vehicles (name, model, year, license_plate, type, category_id, description, tank_capacity, fuel_type, status, mileage, purchase_date, created_by) VALUES
('Трактор John Deere 8330', '8330', 2020, 'А123АА77', 'Колесный трактор', 1, 'Мощный трактор для полевых работ', 400.00, 'diesel', 'active', 1250.50, '2020-05-15', 2),
('Комбайн ACROS 585', 'ACROS 585', 2021, 'В456ВВ77', 'Зерноуборочный', 2, 'Высокопроизводительный зерноуборочный комбайн', 600.00, 'diesel', 'active', 890.75, '2021-03-20', 2),
('Грузовик КАМАЗ 65115', '65115', 2019, 'С789СС77', 'Самосвал', 3, 'Грузовик для перевозки зерна', 350.00, 'diesel', 'active', 24560.25, '2019-07-10', 2),
('УАЗ Патриот', 'Patriot', 2022, 'Е111ЕЕ77', 'Внедорожник', 4, 'Служебный автомобиль', 87.00, 'petrol', 'active', 15670.80, '2022-01-12', 2),
('Трактор МТЗ-82', 'МТЗ-82', 2018, 'К222КК77', 'Универсальный трактор', 1, 'Многоцелевой трактор', 120.00, 'diesel', 'maintenance', 3450.25, '2018-09-05', 2),
('Комбайн ДОН-1500', 'ДОН-1500', 2017, 'М333ММ77', 'Зерноуборочный', 2, 'Надежный комбайн для уборки', 550.00, 'diesel', 'active', 2100.75, '2017-06-18', 2);

-- Приёмка топлива
INSERT INTO fuel_receipts (receipt_date, fuel_type, fuel_amount, supplier, driver_name, vehicle_number, document_number, quality_check, notes, received_by) VALUES
('2024-01-10 09:00:00', 'diesel', 5000.00, 'Лукойл', 'Петров А.И.', 'А123ВС77', 'Накладная №245', TRUE, 'Основная поставка дизельного топлива', 1),
('2024-01-15 10:30:00', 'diesel', 3000.00, 'Газпромнефть', 'Сидоров С.К.', 'В456ОР77', 'Накладная №312', TRUE, 'Дополнительная поставка', 1),
('2024-01-20 08:45:00', 'petrol', 1000.00, 'Роснефть', 'Иванов М.П.', 'С789ТУ77', 'Накладная №278', TRUE, 'Бензин для легковой техники', 1),
('2024-01-25 14:20:00', 'diesel', 4000.00, 'Лукойл', 'Петров А.И.', 'А123ВС77', 'Накладная №356', TRUE, 'Регулярная поставка', 1);

-- Склад топлива (начальные остатки)
INSERT INTO fuel_storage (fuel_type, current_quantity, min_quantity, max_quantity, last_updated, updated_by) VALUES
('diesel', 8000.00, 1000.00, 15000.00, NOW(), 1),
('petrol', 800.00, 200.00, 2000.00, NOW(), 1);

-- Заправки
INSERT INTO refuels (vehicle_id, user_id, fuel_amount, fuel_type, refuel_date, odometer_reading, notes) VALUES
(1, 3, 200.50, 'diesel', '2024-01-15 08:30:00', 1200.00, 'Полная заправка перед выездом в поле'),
(2, 3, 350.75, 'diesel', '2024-01-15 09:15:00', 850.00, 'Заправка комбайна перед уборочной'),
(3, 3, 180.25, 'diesel', '2024-01-16 10:00:00', 24500.00, 'Заправка перед рейсом'),
(4, 3, 35.50, 'petrol', '2024-01-16 14:20:00', 15600.00, 'Заправка служебного автомобиля'),
(1, 3, 185.25, 'diesel', '2024-01-18 16:45:00', 1350.50, 'Вечерняя заправка после работы'),
(2, 3, 280.00, 'diesel', '2024-01-20 07:30:00', 1100.75, 'Утренняя заправка перед работой');

-- Обслуживание
INSERT INTO maintenance (vehicle_id, user_id, maintenance_type, description, maintenance_date, next_maintenance_date, odometer_reading, notes) VALUES
(1, 2, 'routine', 'Регулярное ТО: замена масла, фильтров', '2024-01-10', '2024-04-10', 1200.00, 'Плановое техническое обслуживание'),
(5, 2, 'repair', 'Замена гидравлического насоса', '2024-01-12', NULL, 3400.00, 'Ремонт гидросистемы'),
(3, 2, 'inspection', 'Технический осмотр перед сезоном', '2024-01-08', '2024-07-08', 24500.00, 'Ежегодный техосмотр');

-- Настройки системы
INSERT INTO settings (setting_key, setting_value, setting_type, description, category, is_public) VALUES
('company_name', 'АгроТопливо', 'string', 'Название компании', 'general', TRUE),
('company_address', 'с. Полевое, ул. Центральная, 1', 'string', 'Адрес компании', 'general', TRUE),
('low_fuel_warning', '20', 'number', 'Предупреждение о низком уровне топлива (%)', 'alerts', TRUE),
('maintenance_reminder_days', '7', 'number', 'Напоминание о ТО за дней', 'alerts', TRUE),
('default_currency', 'RUB', 'string', 'Валюта по умолчанию', 'general', TRUE),
('timezone', 'Europe/Moscow', 'string', 'Часовой пояс', 'general', TRUE),
('records_per_page', '25', 'number', 'Записей на странице', 'interface', TRUE),
('min_fuel_stock', '1000', 'number', 'Минимальный запас топлива на складе (л)', 'fuel', TRUE);

-- Уведомления
INSERT INTO notifications (user_id, title, message, type, related_entity, related_id) VALUES
(1, 'Новая техника', 'Добавлена новая техника: Трактор John Deere 8330', 'info', 'vehicle', 1),
(2, 'Требуется ТО', 'Трактор МТЗ-82 требует технического обслуживания', 'warning', 'vehicle', 5),
(3, 'Заправка выполнена', 'Успешно заправлен Комбайн ACROS 585', 'success', 'refuel', 2),
(1, 'Поставка топлива', 'Принята поставка дизельного топлива: 5000 л', 'info', 'receipt', 1);

-- Активность пользователей
INSERT INTO user_activities (user_id, activity_type, description, ip_address, entity_type, entity_id) VALUES
(1, 'login', 'Успешный вход в систему', '192.168.1.100', 'user', 1),
(1, 'user_create', 'Создан пользователь: engineer1', '192.168.1.100', 'user', 2),
(2, 'vehicle_create', 'Добавлена техника: Трактор John Deere 8330', '192.168.1.101', 'vehicle', 1),
(3, 'refuel_create', 'Выполнена заправка: 200.50 л', '192.168.1.102', 'refuel', 1),
(1, 'fuel_receipt', 'Принято топливо: 5000 л дизельного', '192.168.1.100', 'receipt', 1);

-- Создание пользователя для базы данных (опционально)
CREATE USER IF NOT EXISTS 'fuel_user'@'localhost' IDENTIFIED BY 'fuel_password123';
GRANT ALL PRIVILEGES ON fuel_management.* TO 'fuel_user'@'localhost';
FLUSH PRIVILEGES;

-- Добавляем tank_id в таблицу refuels
ALTER TABLE refuels ADD COLUMN tank_id INT NOT NULL AFTER vehicle_id;

-- Добавляем внешний ключ
ALTER TABLE refuels ADD FOREIGN KEY (tank_id) REFERENCES fuel_tanks(id) ON DELETE CASCADE;

-- Обновляем существующие записи заправок (привязываем к первому резервуару)
UPDATE refuels SET tank_id = (SELECT id FROM fuel_tanks LIMIT 1) WHERE tank_id IS NULL OR tank_id = 0;

-- Создаем таблицу для учета остатков в резервуарах, если ее нет
CREATE TABLE IF NOT EXISTS fuel_tanks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(255),
    capacity DECIMAL(10,2) NOT NULL,
    current_level DECIMAL(10,2) DEFAULT 0,
    fuel_type ENUM('diesel', 'petrol', 'gas', 'electric') DEFAULT 'diesel',
    min_level DECIMAL(10,2) DEFAULT 0,
    status ENUM('active', 'maintenance', 'empty') DEFAULT 'active',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Добавляем примеры резервуаров
INSERT IGNORE INTO fuel_tanks (name, location, capacity, current_level, fuel_type, min_level) VALUES
('Основной дизельный резервуар', 'Территория предприятия', 20000, 15000, 'diesel', 1000),
('Резервный дизельный резервуар', 'Территория предприятия', 10000, 5000, 'diesel', 500),
('Бензиновый резервуар', 'Территория предприятия', 5000, 3000, 'petrol', 200);

-- Создание представлений для удобства

-- Представление для сводки по технике
CREATE OR REPLACE VIEW vehicle_summary AS
SELECT 
    v.id,
    v.name,
    v.model,
    v.license_plate,
    vc.name as category_name,
    v.tank_capacity,
    v.status,
    v.mileage,
    COUNT(r.id) as total_refuels,
    COALESCE(SUM(r.fuel_amount), 0) as total_fuel_used,
    MAX(r.refuel_date) as last_refuel_date
FROM vehicles v
LEFT JOIN vehicle_categories vc ON v.category_id = vc.id
LEFT JOIN refuels r ON v.id = r.vehicle_id
GROUP BY v.id, v.name, v.model, v.license_plate, vc.name, v.tank_capacity, v.status, v.mileage;

-- Представление для месячных отчетов по заправкам
CREATE OR REPLACE VIEW monthly_refuel_reports AS
SELECT 
    DATE_FORMAT(r.refuel_date, '%Y-%m') as month,
    COUNT(DISTINCT v.id) as vehicles_count,
    COUNT(r.id) as refuels_count,
    SUM(r.fuel_amount) as total_fuel,
    SUM(r.fuel_amount) / COUNT(DISTINCT v.id) as avg_fuel_per_vehicle
FROM refuels r
LEFT JOIN vehicles v ON r.vehicle_id = v.id
GROUP BY DATE_FORMAT(r.refuel_date, '%Y-%m')
ORDER BY month DESC;

-- Представление для отчетов по приёмке топлива
CREATE OR REPLACE VIEW fuel_receipt_summary AS
SELECT 
    DATE_FORMAT(receipt_date, '%Y-%m') as month,
    fuel_type,
    COUNT(*) as receipt_count,
    SUM(fuel_amount) as total_received,
    GROUP_CONCAT(DISTINCT supplier) as suppliers
FROM fuel_receipts
GROUP BY DATE_FORMAT(receipt_date, '%Y-%m'), fuel_type
ORDER BY month DESC, fuel_type;

-- Представление для топ-5 техники по расходу
CREATE OR REPLACE VIEW top_fuel_consumers AS
SELECT 
    v.id,
    v.name,
    v.license_plate,
    SUM(r.fuel_amount) as total_fuel,
    COUNT(r.id) as refuel_count
FROM vehicles v
LEFT JOIN refuels r ON v.id = r.vehicle_id
WHERE r.refuel_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY v.id, v.name, v.license_plate
ORDER BY total_fuel DESC
LIMIT 5;

-- Триггеры для автоматических действий

-- Триггер для проверки объема заправки (не больше бака)
DELIMITER //
CREATE TRIGGER before_refuel_insert 
BEFORE INSERT ON refuels
FOR EACH ROW
BEGIN
    DECLARE vehicle_tank_capacity DECIMAL(10,2);
    
    SELECT tank_capacity INTO vehicle_tank_capacity
    FROM vehicles WHERE id = NEW.vehicle_id;
    
    IF NEW.fuel_amount > vehicle_tank_capacity THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Невозможно заправить больше объема бака техники';
    END IF;
END//
DELIMITER ;

-- Триггер для обновления склада при приёмке топлива
DELIMITER //
CREATE TRIGGER after_fuel_receipt_insert 
AFTER INSERT ON fuel_receipts
FOR EACH ROW
BEGIN
    DECLARE current_stock DECIMAL(10,2);
    DECLARE storage_id INT;
    
    -- Ищем запись склада для данного типа топлива
    SELECT id, current_quantity INTO storage_id, current_stock
    FROM fuel_storage 
    WHERE fuel_type = NEW.fuel_type 
    LIMIT 1;
    
    IF storage_id IS NOT NULL THEN
        -- Обновляем существующую запись
        UPDATE fuel_storage 
        SET current_quantity = current_stock + NEW.fuel_amount,
            last_updated = NOW(),
            updated_by = NEW.received_by
        WHERE id = storage_id;
    ELSE
        -- Создаем новую запись
        INSERT INTO fuel_storage (fuel_type, current_quantity, last_updated, updated_by)
        VALUES (NEW.fuel_type, NEW.fuel_amount, NOW(), NEW.received_by);
    END IF;
    
    -- Запись в активность
    INSERT INTO user_activities (user_id, activity_type, description, entity_type, entity_id)
    VALUES (NEW.received_by, 'fuel_receipt', 
            CONCAT('Приёмка топлива: ', NEW.fuel_amount, ' л ', NEW.fuel_type, ' от ', NEW.supplier), 
            'receipt', NEW.id);
END//
DELIMITER ;

-- Триггер для обновления склада при заправке (уменьшение остатков)
DELIMITER //
CREATE TRIGGER after_refuel_insert 
AFTER INSERT ON refuels
FOR EACH ROW
BEGIN
    DECLARE current_stock DECIMAL(10,2);
    DECLARE storage_id INT;
    
    -- Ищем запись склада для данного типа топлива
    SELECT id, current_quantity INTO storage_id, current_stock
    FROM fuel_storage 
    WHERE fuel_type = NEW.fuel_type 
    LIMIT 1;
    
    IF storage_id IS NOT NULL THEN
        -- Обновляем существующую запись (уменьшаем остаток)
        UPDATE fuel_storage 
        SET current_quantity = GREATEST(0, current_stock - NEW.fuel_amount),
            last_updated = NOW(),
            updated_by = NEW.user_id
        WHERE id = storage_id;
    END IF;
    
    -- Запись в активность
    INSERT INTO user_activities (user_id, activity_type, description, entity_type, entity_id)
    VALUES (NEW.user_id, 'refuel_create', 
            CONCAT('Заправка: ', NEW.fuel_amount, ' л для техники ID:', NEW.vehicle_id), 
            'refuel', NEW.id);
END//
DELIMITER ;

-- Триггер для уведомления о низком уровне топлива на складе
DELIMITER //
CREATE TRIGGER after_fuel_storage_update 
AFTER UPDATE ON fuel_storage
FOR EACH ROW
BEGIN
    DECLARE min_level DECIMAL(10,2);
    
    -- Получаем минимальный уровень из настроек
    SELECT CAST(setting_value AS DECIMAL(10,2)) INTO min_level
    FROM settings 
    WHERE setting_key = 'min_fuel_stock';
    
    IF min_level IS NULL THEN
        SET min_level = 1000; -- значение по умолчанию
    END IF;
    
    -- Если уровень опустился ниже минимального
    IF NEW.current_quantity < min_level AND OLD.current_quantity >= min_level THEN
        INSERT INTO notifications (user_id, title, message, type, related_entity, related_id)
        SELECT 
            u.id,
            'Низкий запас топлива',
            CONCAT('На складе осталось ', ROUND(NEW.current_quantity, 2), ' л ', NEW.fuel_type, '. Требуется пополнение.'),
            'warning',
            'system',
            NULL
        FROM users u 
        WHERE u.role IN ('admin', 'engineer') AND u.is_active = TRUE;
    END IF;
END//
DELIMITER ;

-- Процедуры для отчетов

-- Процедура для генерации месячного отчета по заправкам
DELIMITER //
CREATE PROCEDURE GenerateMonthlyRefuelReport(IN report_month VARCHAR(7), IN generated_by_user INT)
BEGIN
    DECLARE report_title VARCHAR(255);
    DECLARE start_date DATE;
    DECLARE end_date DATE;
    DECLARE total_fuel DECIMAL(12,2);
    DECLARE vehicle_count INT;
    DECLARE refuel_count INT;
    DECLARE report_json JSON;
    
    SET start_date = STR_TO_DATE(CONCAT(report_month, '-01'), '%Y-%m-%d');
    SET end_date = LAST_DAY(start_date);
    SET report_title = CONCAT('Отчет по заправкам за ', DATE_FORMAT(start_date, '%M %Y'));
    
    -- Сбор статистики
    SELECT 
        COUNT(DISTINCT vehicle_id),
        COUNT(*),
        SUM(fuel_amount)
    INTO vehicle_count, refuel_count, total_fuel
    FROM refuels 
    WHERE DATE(refuel_date) BETWEEN start_date AND end_date;
    
    -- Формирование JSON с детальными данными
    SET report_json = (
        SELECT JSON_OBJECT(
            'vehicles', (
                SELECT JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'id', v.id,
                        'name', v.name,
                        'refuels', (
                            SELECT JSON_ARRAYAGG(
                                JSON_OBJECT(
                                    'date', r.refuel_date,
                                    'amount', r.fuel_amount
                                )
                            )
                            FROM refuels r 
                            WHERE r.vehicle_id = v.id 
                            AND DATE(r.refuel_date) BETWEEN start_date AND end_date
                        ),
                        'total_fuel', COALESCE(SUM(r.fuel_amount), 0)
                    )
                )
                FROM vehicles v
                LEFT JOIN refuels r ON v.id = r.vehicle_id 
                    AND DATE(r.refuel_date) BETWEEN start_date AND end_date
                GROUP BY v.id, v.name
            ),
            'summary', JSON_OBJECT(
                'vehicle_count', vehicle_count,
                'refuel_count', refuel_count,
                'total_fuel', total_fuel,
                'avg_fuel_per_vehicle', total_fuel / NULLIF(vehicle_count, 0)
            )
        )
    );
    
    -- Вставка отчета
    INSERT INTO fuel_reports (title, report_type, period_start, period_end, report_data, total_fuel, vehicle_count, refuel_count, generated_by)
    VALUES (report_title, 'monthly', start_date, end_date, report_json, total_fuel, vehicle_count, refuel_count, generated_by_user);
    
END//
DELIMITER ;

-- Процедура для генерации отчета по приёмке топлива
DELIMITER //
CREATE PROCEDURE GenerateFuelReceiptReport(IN start_date DATE, IN end_date DATE, IN generated_by_user INT)
BEGIN
    DECLARE report_title VARCHAR(255);
    DECLARE total_received DECIMAL(12,2);
    DECLARE receipt_count INT;
    DECLARE report_json JSON;
    
    SET report_title = CONCAT('Отчет по приёмке топлива с ', start_date, ' по ', end_date);
    
    -- Сбор статистики
    SELECT 
        COUNT(*),
        SUM(fuel_amount)
    INTO receipt_count, total_received
    FROM fuel_receipts 
    WHERE DATE(receipt_date) BETWEEN start_date AND end_date;
    
    -- Формирование JSON с детальными данными
    SET report_json = (
        SELECT JSON_OBJECT(
            'receipts', (
                SELECT JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'id', fr.id,
                        'date', fr.receipt_date,
                        'fuel_type', fr.fuel_type,
                        'amount', fr.fuel_amount,
                        'supplier', fr.supplier,
                        'driver', fr.driver_name,
                        'document', fr.document_number
                    )
                )
                FROM fuel_receipts fr
                WHERE DATE(fr.receipt_date) BETWEEN start_date AND end_date
            ),
            'summary', JSON_OBJECT(
                'receipt_count', receipt_count,
                'total_received', total_received,
                'by_fuel_type', (
                    SELECT JSON_OBJECTAGG(fuel_type, JSON_OBJECT(
                        'count', COUNT(*),
                        'total', SUM(fuel_amount)
                    ))
                    FROM fuel_receipts
                    WHERE DATE(receipt_date) BETWEEN start_date AND end_date
                    GROUP BY fuel_type
                )
            )
        )
    );
    
    -- Вставка отчета
    INSERT INTO fuel_reports (title, report_type, period_start, period_end, report_data, total_fuel, generated_by)
    VALUES (report_title, 'receipt', start_date, end_date, report_json, total_received, generated_by_user);
    
END//
DELIMITER ;

-- Процедура для получения статистики за период
DELIMITER //
CREATE PROCEDURE GetPeriodStats(IN start_date DATE, IN end_date DATE)
BEGIN
    SELECT 
        COUNT(DISTINCT v.id) as total_vehicles,
        COUNT(r.id) as total_refuels,
        SUM(r.fuel_amount) as total_fuel_used,
        COUNT(fr.id) as total_receipts,
        SUM(fr.fuel_amount) as total_fuel_received,
        MIN(r.refuel_date) as first_refuel,
        MAX(r.refuel_date) as last_refuel
    FROM vehicles v
    LEFT JOIN refuels r ON v.id = r.vehicle_id 
        AND DATE(r.refuel_date) BETWEEN start_date AND end_date
    CROSS JOIN fuel_receipts fr ON DATE(fr.receipt_date) BETWEEN start_date AND end_date
    WHERE r.id IS NOT NULL OR fr.id IS NOT NULL;
END//
DELIMITER ;

-- Создание индексов для оптимизации
CREATE INDEX idx_refuels_date_vehicle ON refuels(refuel_date, vehicle_id);
CREATE INDEX idx_vehicles_status ON vehicles(status);
CREATE INDEX idx_maintenance_dates ON maintenance(maintenance_date, next_maintenance_date);
CREATE INDEX idx_users_activity_date ON user_activities(created_at, user_id);
CREATE INDEX idx_notifications_user_read ON notifications(user_id, is_read, created_at);
CREATE INDEX idx_fuel_receipts_date ON fuel_receipts(receipt_date);
CREATE INDEX idx_fuel_receipts_supplier ON fuel_receipts(supplier, receipt_date);

-- Функция для расчета общего расхода техники
DELIMITER //
CREATE FUNCTION CalculateVehicleFuelConsumption(vehicle_id INT, days_back INT) 
RETURNS DECIMAL(10,2)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE total_consumption DECIMAL(10,2);
    
    SELECT SUM(fuel_amount) INTO total_consumption
    FROM refuels 
    WHERE vehicle_id = vehicle_id 
    AND refuel_date >= DATE_SUB(CURRENT_DATE, INTERVAL days_back DAY);
    
    RETURN COALESCE(total_consumption, 0);
END//
DELIMITER ;

-- Функция для получения текущего остатка на складе
DELIMITER //
CREATE FUNCTION GetCurrentFuelStock(fuel_type_param VARCHAR(20)) 
RETURNS DECIMAL(10,2)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE current_stock DECIMAL(10,2);
    
    SELECT current_quantity INTO current_stock
    FROM fuel_storage 
    WHERE fuel_type = fuel_type_param;
    
    RETURN COALESCE(current_stock, 0);
END//
DELIMITER ;

-- Завершение скрипта
SELECT 'База данных fuel_management успешно создана и заполнена!' as message;

-- Показать основную информацию
SELECT 
    (SELECT COUNT(*) FROM users) as total_users,
    (SELECT COUNT(*) FROM vehicles) as total_vehicles,
    (SELECT COUNT(*) FROM refuels) as total_refuels,
    (SELECT COUNT(*) FROM fuel_receipts) as total_receipts,
    (SELECT SUM(fuel_amount) FROM refuels) as total_fuel_used,
    (SELECT SUM(fuel_amount) FROM fuel_receipts) as total_fuel_received,
    (SELECT GetCurrentFuelStock('diesel')) as current_diesel_stock,
    (SELECT GetCurrentFuelStock('petrol')) as current_petrol_stock;
    -- Удаляем триггер, который вызывает ошибку
DROP TRIGGER IF EXISTS before_refuel_insert;

-- Создаем новый триггер без проверки current_fuel (которого больше нет)
DELIMITER //
CREATE TRIGGER before_refuel_insert 
BEFORE INSERT ON refuels
FOR EACH ROW
BEGIN
    DECLARE vehicle_tank_capacity DECIMAL(10,2);
    DECLARE tank_current_level DECIMAL(10,2);
    DECLARE vehicle_fuel_type VARCHAR(20);
    DECLARE tank_fuel_type VARCHAR(20);
    
    -- Получаем объем бака техники
    SELECT tank_capacity, fuel_type INTO vehicle_tank_capacity, vehicle_fuel_type
    FROM vehicles WHERE id = NEW.vehicle_id;
    
    -- Получаем текущий уровень в резервуаре
    SELECT current_level, fuel_type INTO tank_current_level, tank_fuel_type
    FROM fuel_tanks WHERE id = NEW.tank_id;
    
    -- Проверяем объем бака
    IF NEW.fuel_amount > vehicle_tank_capacity THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Невозможно заправить больше объема бака техники';
    END IF;
    
    -- Проверяем достаточность топлива в резервуаре
    IF NEW.fuel_amount > tank_current_level THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'В резервуаре недостаточно топлива';
    END IF;
    
    -- Проверяем совместимость типов топлива
    IF vehicle_fuel_type != tank_fuel_type THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Тип топлива в технике и резервуаре не совпадает';
    END IF;
END//
DELIMITER ;