-- База данни за система за разпознаване на автомобилни номера
-- Създаване на база данни
CREATE DATABASE IF NOT EXISTS license_plate_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE license_plate_system;

-- Таблица за роли
CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Таблица за връзка между потребители и роли
CREATE TABLE user_roles (
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица за права
CREATE TABLE permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Таблица за връзка между роли и права
CREATE TABLE role_permissions (
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица за камери
CREATE TABLE cameras (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(255) NOT NULL,
    description TEXT,
    ip_address VARCHAR(45),
    model VARCHAR(100),
    manufacturer VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Таблица за видео потоци
CREATE TABLE streams (
    id INT AUTO_INCREMENT PRIMARY KEY,
    camera_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    url VARCHAR(255) NOT NULL,
    protocol ENUM('RTSP', 'HTTP', 'HTTPS', 'MJPEG', 'HLS', 'OTHER') NOT NULL,
    resolution VARCHAR(20),
    fps INT,
    is_active BOOLEAN DEFAULT TRUE,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (camera_id) REFERENCES cameras(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Таблица за бял списък
CREATE TABLE whitelist (
    id INT AUTO_INCREMENT PRIMARY KEY,
    license_plate VARCHAR(20) NOT NULL,
    description TEXT,
    valid_from DATETIME NOT NULL,
    valid_to DATETIME,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE KEY (license_plate)
);

-- Таблица за черен списък
CREATE TABLE blacklist (
    id INT AUTO_INCREMENT PRIMARY KEY,
    license_plate VARCHAR(20) NOT NULL,
    reason TEXT NOT NULL,
    valid_from DATETIME NOT NULL,
    valid_to DATETIME,
    alert_level ENUM('LOW', 'MEDIUM', 'HIGH', 'CRITICAL') DEFAULT 'MEDIUM',
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE KEY (license_plate)
);

-- Таблица за разпознати номера
CREATE TABLE recognitions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    license_plate VARCHAR(20) NOT NULL,
    confidence FLOAT NOT NULL,
    camera_id INT NOT NULL,
    stream_id INT NOT NULL,
    capture_time DATETIME NOT NULL,
    image_path VARCHAR(255),
    plate_image_path VARCHAR(255),
    is_in_whitelist BOOLEAN DEFAULT FALSE,
    is_in_blacklist BOOLEAN DEFAULT FALSE,
    processed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (camera_id) REFERENCES cameras(id) ON DELETE CASCADE,
    FOREIGN KEY (stream_id) REFERENCES streams(id) ON DELETE CASCADE,
    INDEX (license_plate),
    INDEX (capture_time)
);

-- Таблица за настройки на известяване
CREATE TABLE notification_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    email_notifications BOOLEAN DEFAULT TRUE,
    sms_notifications BOOLEAN DEFAULT FALSE,
    web_notifications BOOLEAN DEFAULT TRUE,
    blacklist_alerts BOOLEAN DEFAULT TRUE,
    system_alerts BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Таблица за известия
CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type ENUM('BLACKLIST_ALERT', 'SYSTEM_ALERT', 'INFO') NOT NULL,
    title VARCHAR(100) NOT NULL,
    message TEXT NOT NULL,
    recognition_id INT,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (recognition_id) REFERENCES recognitions(id) ON DELETE SET NULL
);

-- Таблица за връзка между потребители и известия
CREATE TABLE user_notifications (
    notification_id INT NOT NULL,
    user_id INT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    read_at DATETIME,
    PRIMARY KEY (notification_id, user_id),
    FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Таблица за системен лог
CREATE TABLE system_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    description TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Таблица за настройки на системата
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(50) NOT NULL UNIQUE,
    setting_value TEXT,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Таблица за експортирани отчети
CREATE TABLE exports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('PDF', 'EXCEL', 'CSV') NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    parameters TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Вмъкване на основни роли
INSERT INTO roles (name, description) VALUES 
('admin', 'Администратор с пълен достъп до системата'),
('operator', 'Оператор с достъп до основните функционалности'),
('viewer', 'Потребител само с права за преглед');

-- Вмъкване на основни права
INSERT INTO permissions (name, description) VALUES 
('manage_users', 'Управление на потребители'),
('manage_cameras', 'Управление на камери и потоци'),
('manage_whitelist', 'Управление на бял списък'),
('manage_blacklist', 'Управление на черен списък'),
('view_recognitions', 'Преглед на разпознати номера'),
('export_data', 'Експортиране на данни'),
('view_reports', 'Преглед на отчети'),
('manage_settings', 'Управление на системни настройки');

-- Задаване на права за ролите
INSERT INTO role_permissions (role_id, permission_id) VALUES 
(1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), -- admin има всички права
(2, 2), (2, 3), (2, 4), (2, 5), (2, 6), (2, 7), -- operator има повечето права без управление на потребители и настройки
(3, 5), (3, 7);

-- Задаване на администраторска роля
INSERT INTO user_roles (user_id, role_id) VALUES (1, 1);

-- Основни системни настройки
INSERT INTO settings (setting_key, setting_value, description) VALUES 
('system_name', 'Система за разпознаване на автомобилни номера', 'Име на системата'),
('recognition_confidence_threshold', '80', 'Минимален праг на увереност при разпознаване (%)'),
('retention_period_days', '90', 'Период на съхранение на данни в дни'),
('max_alerts_per_day', '100', 'Максимален брой известия за ден'),
('default_language', 'bg', 'Език по подразбиране');

-- Индекси за оптимизация на търсенето
CREATE INDEX idx_recognitions_license_plate ON recognitions(license_plate);
CREATE INDEX idx_recognitions_capture_time ON recognitions(capture_time);
CREATE INDEX idx_whitelist_license_plate ON whitelist(license_plate);
CREATE INDEX idx_blacklist_license_plate ON blacklist(license_plate);
CREATE INDEX idx_system_log_created_at ON system_log(created_at);
