-- Создание базы данных
DROP DATABASE IF EXISTS fuel;
CREATE DATABASE fuel_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE fuel;

-- Таблица: Клиент (расширенная)
CREATE TABLE client (
    id_client INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE,
    client_type ENUM('individual', 'company') DEFAULT 'individual',
    client_email VARCHAR(100),
    client_name VARCHAR(100) NOT NULL,
    client_surname VARCHAR(100) NOT NULL,
    client_patronymic VARCHAR(100),
    client_phone VARCHAR(20) NOT NULL,
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Таблица: Поставщик
CREATE TABLE provider (
    id_provider INT PRIMARY KEY AUTO_INCREMENT,
    provider_email VARCHAR(100),
    provider_phone VARCHAR(20),
    company_name VARCHAR(200) NOT NULL,
    owner_name VARCHAR(100),
    owner_surname VARCHAR(100),
    owner_patronymic VARCHAR(100),
    contract_number VARCHAR(50),
    contract_date DATE,
    is_active BOOLEAN DEFAULT TRUE
);

-- Таблица: Топливо
CREATE TABLE fuel (
    id_fuel INT PRIMARY KEY AUTO_INCREMENT,
    fuel_type VARCHAR(100) NOT NULL,
    fuel_price DECIMAL(10, 2) NOT NULL,
    id_provider INT,
    last_price_change TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_provider) REFERENCES provider(id_provider) ON DELETE SET NULL
);

-- Таблица: АЗС
CREATE TABLE gas_station (
    id_gas_station INT PRIMARY KEY AUTO_INCREMENT,
    gas_station_location VARCHAR(255) NOT NULL,
    gas_station_address TEXT,
    gas_station_phone VARCHAR(20),
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    owner_name VARCHAR(100),
    owner_surname VARCHAR(100),
    owner_patronymic VARCHAR(100),
    opening_time TIME DEFAULT '08:00:00',
    closing_time TIME DEFAULT '22:00:00',
    is_24h BOOLEAN DEFAULT FALSE
);

-- Таблица: Сотрудник
CREATE TABLE employee (
    id_employee INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE,
    employee_number INT UNIQUE,
    employee_position VARCHAR(100),
    employee_name VARCHAR(100) NOT NULL,
    employee_surname VARCHAR(100) NOT NULL,
    employee_patronymic VARCHAR(100),
    employee_phone VARCHAR(20),
    id_gas_station INT,
    hire_date DATE,
    salary DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (id_gas_station) REFERENCES gas_station(id_gas_station) ON DELETE SET NULL
);

-- Таблица: Заправочная колонка
CREATE TABLE pump (
    id_pump INT PRIMARY KEY AUTO_INCREMENT,
    pump_number VARCHAR(20),
    fuel_amount INT DEFAULT 0,
    max_capacity INT,
    id_fuel INT,
    id_gas_station INT,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (id_fuel) REFERENCES fuel(id_fuel) ON DELETE SET NULL,
    FOREIGN KEY (id_gas_station) REFERENCES gas_station(id_gas_station) ON DELETE CASCADE
);

-- Таблица: Транзакции (покупки)
CREATE TABLE receipt (
    id_receipt INT PRIMARY KEY AUTO_INCREMENT,
    summary DECIMAL(10, 2) NOT NULL,
    fuel_amount INT NOT NULL,
    fuel_price DECIMAL(10, 2) NOT NULL,
    purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    id_card INT,
    id_pump INT,
    id_fuel INT,
    transaction_status ENUM('pending', 'completed', 'cancelled') DEFAULT 'completed',
    FOREIGN KEY (id_card) REFERENCES fuel_card(id_card) ON DELETE SET NULL,
    FOREIGN KEY (id_pump) REFERENCES pump(id_pump) ON DELETE SET NULL,
    FOREIGN KEY (id_fuel) REFERENCES fuel(id_fuel) ON DELETE SET NULL,
    INDEX idx_purchase_date (purchase_date)
);

-- Таблица: Операции пополнения
CREATE TABLE refill_operation (
    id_refill INT PRIMARY KEY AUTO_INCREMENT,
    id_card INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    refill_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    payment_method ENUM('card', 'cash', 'bank_transfer') DEFAULT 'card',
    status ENUM('pending', 'completed', 'failed') DEFAULT 'completed',
    operator_id INT,
    FOREIGN KEY (id_card) REFERENCES fuel_card(id_card) ON DELETE CASCADE,
    FOREIGN KEY (operator_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Триггер для автоматического обновления баланса после пополнения
DELIMITER $$
        
        INSERT INTO system_log (action, details, user_id)
        VALUES ('refill', CONCAT('Пополнение карты #', NEW.id_card, ' на сумму ', NEW.amount), NEW.operator_id);
    END IF;
END$$
DELIMITER ;

-- Триггер для проверки покупки (достаточно ли средств)
DELIMITER $$
CREATE TRIGGER before_receipt_insert
BEFORE INSERT ON receipt
FOR EACH ROW
BEGIN
    DECLARE current_balance DECIMAL(10, 2);
    
    IF current_balance < NEW.summary THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Недостаточно средств на карте';
    END IF;
END$$
DELIMITER ;

-- Триггер для обновления баланса после покупки
DELIMITER $$
        
        -- Обновляем количество топлива на колонке
        UPDATE pump 
        SET fuel_amount = fuel_amount - NEW.fuel_amount 
        WHERE id_pump = NEW.id_pump;
    END IF;
END$$
DELIMITER ;

-- Таблица для логов системы
CREATE TABLE system_log (
    id_log INT PRIMARY KEY AUTO_INCREMENT,
    action VARCHAR(50),
    details TEXT,
    user_id INT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Клиенты
INSERT INTO client (user_id, client_name, client_surname, client_patronymic, client_phone, client_email) VALUES
(4, 'Иван', 'Иванов', 'Иванович', '+7-999-111-22-33', 'client@mail.ru'),
(5, 'Петр', 'Петров', 'Петрович', '+7-999-222-33-44', 'petrov@mail.ru');

-- Поставщики
INSERT INTO provider (company_name, provider_phone, provider_email, contract_number) VALUES
('Роснефть', '+7-800-100-00-00', 'info@rosneft.ru', 'ROS-2025-001'),
('Лукойл', '+7-800-200-00-00', 'info@lukoil.ru', 'LUK-2025-001'),
('Татнефть', '+7-800-300-00-00', 'info@tatneft.ru', 'TAT-2025-001'),
('Газпром нефть', '+7-800-400-00-00', 'info@gazprom-neft.ru', 'GAZ-2025-001');

-- Топливо
INSERT INTO fuel (fuel_type, fuel_price, id_provider) VALUES
('АИ-92', 45.50, 1),
('АИ-95', 50.75, 1),
('АИ-98', 55.90, 1),
('Дизель', 52.30, 2),
('Газ', 28.50, 3),
('АИ-100', 60.25, 4);

-- АЗС
INSERT INTO gas_station (gas_station_location, gas_station_address, gas_station_phone, latitude, longitude) VALUES
('Москва, центр', 'ул. Тверская, 10', '+7-495-111-11-11', 55.7558, 37.6173),
('Москва, САО', 'Ленинградский пр-т, 45', '+7-495-222-22-22', 55.8000, 37.5000),
('Санкт-Петербург', 'Невский пр-т, 25', '+7-812-333-33-33', 59.9343, 30.3351),
('Казань', 'ул. Баумана, 15', '+7-843-444-44-44', 55.7961, 49.1064);

-- Сотрудники
INSERT INTO employee (user_id, employee_number, employee_position, employee_name, employee_surname, id_gas_station) VALUES
(3, 1001, 'Оператор', 'Алексей', 'Сидоров', 1);

-- Заправочные колонки
INSERT INTO pump (pump_number, fuel_amount, max_capacity, id_fuel, id_gas_station) VALUES
('P001', 5000, 10000, 1, 1),
('P002', 8000, 10000, 2, 1),
('P003', 6000, 10000, 3, 1),
('P004', 7000, 10000, 4, 2),
('P005', 9000, 10000, 5, 2);

-- Транзакции
INSERT INTO receipt (summary, fuel_amount, fuel_price, id_card, id_pump, id_fuel) VALUES
(455.00, 10, 45.50, 1, 1, 1),
(1015.00, 20, 50.75, 1, 2, 2),
(1046.00, 20, 52.30, 2, 4, 4);

-- Создание индексов
CREATE INDEX idx_client_phone ON client(client_phone);
CREATE INDEX idx_receipt_card ON receipt(id_card);
CREATE INDEX idx_receipt_date ON receipt(purchase_date);
CREATE INDEX idx_gas_station_location ON gas_station(gas_station_location);
CREATE INDEX idx_fuel_type ON fuel(fuel_type);

-- Хранимая процедура для ежемесячного отчета
DELIMITER $$
CREATE PROCEDURE GenerateMonthlyReport(IN month INT, IN year INT)
BEGIN
    SELECT 
        p.company_name as provider,
        f.fuel_type,
        COUNT(r.id_receipt) as sales_count,
        SUM(r.summary) as total_revenue,
        SUM(r.fuel_amount) as total_volume
    FROM receipt r
    JOIN fuel f ON r.id_fuel = f.id_fuel
    JOIN provider p ON f.id_provider = p.id_provider
    WHERE MONTH(r.purchase_date) = month 
        AND YEAR(r.purchase_date) = year
        AND r.transaction_status = 'completed'
    GROUP BY p.company_name, f.fuel_type
    ORDER BY total_revenue DESC;
END$$
DELIMITER ;

