-- База данни за система за управление на пожароизвестителна техника

-- Създаване на база данни
DROP DATABASE IF EXISTS fire_alarm_system;
CREATE DATABASE fire_alarm_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE fire_alarm_system;

-- Таблица за потребителски роли
CREATE TABLE IF NOT EXISTS roles (
    role_id INT AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Таблица за типове обекти
CREATE TABLE IF NOT EXISTS object_types (
    type_id INT AUTO_INCREMENT PRIMARY KEY,
    type_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Таблица за обекти с пожароизвестителна техника
CREATE TABLE IF NOT EXISTS objects (
    object_id INT AUTO_INCREMENT PRIMARY KEY,
    object_name VARCHAR(100) NOT NULL,
    object_type_id INT NOT NULL,
    address TEXT NOT NULL,
    contact_person VARCHAR(100),
    contact_phone VARCHAR(20),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (object_type_id),
    FOREIGN KEY (object_type_id) REFERENCES object_types(type_id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- Таблица за типове оборудване
CREATE TABLE IF NOT EXISTS equipment_types (
    equipment_type_id INT AUTO_INCREMENT PRIMARY KEY,
    type_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    maintenance_period INT NOT NULL COMMENT 'Период на поддръжка в дни',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Таблица за оборудване в обектите
CREATE TABLE IF NOT EXISTS equipment (
    equipment_id INT AUTO_INCREMENT PRIMARY KEY,
    object_id INT NOT NULL,
    equipment_type_id INT NOT NULL,
    serial_number VARCHAR(100),
    installation_date DATE,
    last_maintenance_date DATE,
    next_maintenance_date DATE,
    status ENUM('active', 'inactive', 'maintenance', 'faulty') DEFAULT 'active',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (object_id),
    INDEX (equipment_type_id),
    FOREIGN KEY (object_id) REFERENCES objects(object_id) ON DELETE CASCADE,
    FOREIGN KEY (equipment_type_id) REFERENCES equipment_types(equipment_type_id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- Таблица за графици на проверки
CREATE TABLE IF NOT EXISTS inspection_schedules (
    schedule_id INT AUTO_INCREMENT PRIMARY KEY,
    object_id INT NOT NULL,
    scheduled_date DATE NOT NULL,
    scheduled_time TIME,
    inspector_id INT,
    status ENUM('pending', 'completed', 'cancelled', 'rescheduled') DEFAULT 'pending',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (object_id),
    INDEX (inspector_id),
    FOREIGN KEY (object_id) REFERENCES objects(object_id) ON DELETE CASCADE,
    FOREIGN KEY (inspector_id) REFERENCES users(user_id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Таблица за проверки
CREATE TABLE IF NOT EXISTS inspections (
    inspection_id INT AUTO_INCREMENT PRIMARY KEY,
    schedule_id INT,
    object_id INT NOT NULL,
    inspector_id INT NOT NULL,
    inspection_date DATE NOT NULL,
    start_time TIME,
    end_time TIME,
    status ENUM('in_progress', 'completed', 'incomplete') DEFAULT 'in_progress',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (schedule_id),
    INDEX (object_id),
    INDEX (inspector_id),
    FOREIGN KEY (schedule_id) REFERENCES inspection_schedules(schedule_id) ON DELETE SET NULL,
    FOREIGN KEY (object_id) REFERENCES objects(object_id) ON DELETE CASCADE,
    FOREIGN KEY (inspector_id) REFERENCES users(user_id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- Таблица за типове повреди
CREATE TABLE IF NOT EXISTS fault_types (
    fault_type_id INT AUTO_INCREMENT PRIMARY KEY,
    type_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    severity ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Таблица за повреди
CREATE TABLE IF NOT EXISTS faults (
    fault_id INT AUTO_INCREMENT PRIMARY KEY,
    equipment_id INT NOT NULL,
    inspection_id INT,
    fault_type_id INT NOT NULL,
    reported_by INT NOT NULL,
    reported_date DATETIME NOT NULL,
    description TEXT,
    status ENUM('reported', 'in_progress', 'resolved', 'unresolved') DEFAULT 'reported',
    resolution_notes TEXT,
    resolved_date DATETIME,
    resolved_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (equipment_id),
    INDEX (inspection_id),
    INDEX (fault_type_id),
    INDEX (reported_by),
    INDEX (resolved_by),
    FOREIGN KEY (equipment_id) REFERENCES equipment(equipment_id) ON DELETE CASCADE,
    FOREIGN KEY (inspection_id) REFERENCES inspections(inspection_id) ON DELETE SET NULL,
    FOREIGN KEY (fault_type_id) REFERENCES fault_types(fault_type_id) ON DELETE RESTRICT,
    FOREIGN KEY (reported_by) REFERENCES users(user_id) ON DELETE RESTRICT,
    FOREIGN KEY (resolved_by) REFERENCES users(user_id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Таблица за известия
CREATE TABLE IF NOT EXISTS notifications (
    notification_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(100) NOT NULL,
    message TEXT NOT NULL,
    related_to ENUM('inspection', 'fault', 'maintenance', 'system') NOT NULL,
    related_id INT,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX (user_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Таблица за логове на системата
CREATE TABLE IF NOT EXISTS system_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id INT,
    details TEXT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX (user_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Вмъкване на начални данни за роли
INSERT INTO roles (role_name, description) VALUES
('admin', 'Администратор със пълни права за управление на системата'),
('manager', 'Мениджър с права за управление на обекти и графици'),
('technician', 'Техник с права за извършване на проверки и регистриране на повреди'),
('viewer', 'Потребител с права само за преглед');

-- Вмъкване на начални данни за типове обекти
INSERT INTO object_types (type_name, description) VALUES
('Офис сграда', 'Офис сгради и административни центрове'),
('Търговски център', 'Търговски центрове и молове'),
('Производствено предприятие', 'Производствени предприятия и фабрики'),
('Склад', 'Складови помещения и логистични центрове'),
('Хотел', 'Хотели и места за настаняване'),
('Болница', 'Болници и медицински центрове'),
('Училище', 'Училища и образователни институции');

-- Вмъкване на начални данни за типове оборудване
INSERT INTO equipment_types (type_name, description, maintenance_period) VALUES
('Пожароизвестителна централа', 'Централно управляващо устройство на системата', 90),
('Димен датчик', 'Датчик за откриване на дим', 180),
('Термичен датчик', 'Датчик за откриване на висока температура', 180),
('Ръчен пожароизвестител', 'Бутон за ръчно активиране на аларма', 180),
('Сирена', 'Звуково сигнализиращо устройство', 180),
('Светлинен индикатор', 'Визуално сигнализиращо устройство', 180),
('Пожарогасителна система', 'Автоматична система за гасене на пожар', 90);

-- Вмъкване на начални данни за типове повреди
INSERT INTO fault_types (type_name, description, severity) VALUES
('Повреда в захранването', 'Проблеми със захранването на устройството', 'high'),
('Комуникационна грешка', 'Проблеми в комуникацията между устройствата', 'medium'),
('Физическа повреда', 'Физическа повреда на устройството', 'high'),
('Фалшива аларма', 'Задействане на аларма без наличие на опасност', 'low'),
('Изтощена батерия', 'Батерията на устройството е изтощена', 'medium'),
('Запрашен датчик', 'Датчикът е запрашен и се нуждае от почистване', 'low'),
('Софтуерна грешка', 'Грешка в софтуера на устройството', 'medium');
