-- ================================================================
-- Citizen System - KOMPLETNA STRUKTURA BAZY DANYCH
-- Utworzono: 5 sierpnia 2025
-- Opis: Pełna struktura wszystkich tabel potrzebnych do działania systemu
-- ================================================================

-- Utwórz bazę danych jeśli nie istnieje
CREATE DATABASE IF NOT EXISTS lspd_mdt 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE lspd_mdt;

-- ================================================================
-- TABELE GŁÓWNE - SYSTEM UŻYTKOWNIKÓW WEB
-- ================================================================

-- Tabela użytkowników głównego systemu (admin, funkcjonariusze web)
CREATE TABLE IF NOT EXISTS web_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    fullname VARCHAR(200) NOT NULL,
    avatar VARCHAR(500) DEFAULT 'avatars/default.jpeg',
    email VARCHAR(255) UNIQUE,
    role ENUM('admin', 'superadmin', 'creator', 'officer', 'user') DEFAULT 'user',
    rank VARCHAR(100) DEFAULT 'Recruit',
    active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    ip_address VARCHAR(45),
    session_token VARCHAR(255),
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_role (role),
    INDEX idx_active (active)
);

-- Tabela resetowania haseł
CREATE TABLE IF NOT EXISTS password_reset_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    token VARCHAR(255) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    used BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES web_users(id) ON DELETE CASCADE,
    INDEX idx_token (token),
    INDEX idx_expires (expires_at)
);

-- Tabela sesji użytkowników
CREATE TABLE IF NOT EXISTS user_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    session_token VARCHAR(255) UNIQUE NOT NULL,
    user_agent TEXT,
    ip_address VARCHAR(45),
    expires_at TIMESTAMP NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES web_users(id) ON DELETE CASCADE,
    INDEX idx_session_token (session_token),
    INDEX idx_user_id (user_id),
    INDEX idx_expires (expires_at),
    INDEX idx_active (is_active)
);

-- ================================================================
-- SYSTEM MAILBOX LSPD - KOMUNIKACJA WEWNĘTRZNA
-- ================================================================

-- Tabela użytkowników systemu mailbox (funkcjonariusze LSPD)
CREATE TABLE IF NOT EXISTS lspd_mailbox_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    badge_number VARCHAR(20) UNIQUE NOT NULL,
    fullname VARCHAR(200) NOT NULL,
    rank VARCHAR(100),
    department VARCHAR(100),
    email VARCHAR(255) UNIQUE,
    password VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    hr_access TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Dostęp do funkcji HR',
    psb_access TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Dostęp do funkcji PSB', 
    command_access TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Dostęp do funkcji Command',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    INDEX idx_badge (badge_number),
    INDEX idx_email (email),
    INDEX idx_department (department),
    INDEX idx_active (is_active),
    INDEX idx_hr_access (hr_access),
    INDEX idx_psb_access (psb_access),
    INDEX idx_command_access (command_access)
);

-- Tabela wiadomości systemu mailbox
CREATE TABLE IF NOT EXISTS lspd_mailbox (
    id INT AUTO_INCREMENT PRIMARY KEY,
    from_officer VARCHAR(200) NOT NULL,
    from_badge VARCHAR(20) NOT NULL,
    to_officer VARCHAR(200) NOT NULL,
    to_badge VARCHAR(20) NOT NULL,
    subject VARCHAR(500) NOT NULL,
    content TEXT NOT NULL,
    message TEXT,  -- dla kompatybilności z starszym kodem
    priority ENUM('normal', 'high', 'urgent') DEFAULT 'normal',
    category VARCHAR(100) DEFAULT 'general',
    reply_to INT NULL,  -- ID wiadomości na którą to jest odpowiedź
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    read_at TIMESTAMP NULL,
    is_read BOOLEAN DEFAULT FALSE,
    is_deleted_sender BOOLEAN DEFAULT FALSE,
    is_deleted_receiver BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (reply_to) REFERENCES lspd_mailbox(id) ON DELETE SET NULL,
    INDEX idx_to_badge (to_badge),
    INDEX idx_from_badge (from_badge),
    INDEX idx_created_at (created_at),
    INDEX idx_priority (priority),
    INDEX idx_category (category),
    INDEX idx_reply_to (reply_to)
);

-- Tabela załączników do wiadomości mailbox
CREATE TABLE IF NOT EXISTS lspd_mail_attachments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mail_id INT NOT NULL,
    filename VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INT NOT NULL,
    mime_type VARCHAR(100),
    uploaded_by VARCHAR(20) NOT NULL,  -- badge użytkownika
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (mail_id) REFERENCES lspd_mailbox(id) ON DELETE CASCADE,
    INDEX idx_mail_id (mail_id),
    INDEX idx_uploaded_by (uploaded_by),
    INDEX idx_created_at (created_at)
);

-- ================================================================
-- SYSTEM CYWILNY - PODANIA I KOMUNIKACJA Z OBYWATELAMI
-- ================================================================

-- Tabela użytkowników cywilnych
CREATE TABLE IF NOT EXISTS civilian_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone VARCHAR(20),
    password VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    ip_address VARCHAR(45),
    last_ip VARCHAR(45),
    email_verified BOOLEAN DEFAULT FALSE,
    verification_token VARCHAR(255),
    INDEX idx_email (email),
    INDEX idx_name (first_name, last_name),
    INDEX idx_active (is_active)
);

-- Tabela podań cywilnych
CREATE TABLE IF NOT EXISTS civilian_applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    civilian_id INT NOT NULL,
    department ENUM('hr', 'psb', 'command', 'general') NOT NULL,
    type ENUM('recruitment', 'complaint', 'request', 'report', 'other') NOT NULL,
    subject VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    contact_preference ENUM('email', 'phone', 'both') DEFAULT 'email',
    video_path VARCHAR(500) NULL,
    status ENUM('pending', 'under-review', 'approved', 'rejected', 'needs-info') DEFAULT 'pending',
    priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
    admin_response TEXT NULL,
    assigned_to VARCHAR(100) NULL,  -- badge funkcjonariusza
    reviewed_by VARCHAR(100) NULL,  -- badge funkcjonariusza który przejrzał
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (civilian_id) REFERENCES civilian_users(id) ON DELETE CASCADE,
    INDEX idx_civilian_id (civilian_id),
    INDEX idx_department (department),
    INDEX idx_type (type),
    INDEX idx_status (status),
    INDEX idx_priority (priority),
    INDEX idx_created_at (created_at),
    INDEX idx_assigned (assigned_to)
);

-- Tabela odpowiedzi na podania cywilne
CREATE TABLE IF NOT EXISTS civ_application_responses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    application_id INT NOT NULL,
    civilian_id INT NOT NULL,
    response_content TEXT NOT NULL,
    status ENUM('pending', 'approved', 'rejected', 'needs_info', 'closed') DEFAULT 'pending',
    responded_by VARCHAR(255),  -- badge funkcjonariusza
    responded_by_name VARCHAR(255),  -- imię i nazwisko funkcjonariusza
    response_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    read_by_civilian BOOLEAN DEFAULT FALSE,
    read_by_admin BOOLEAN DEFAULT FALSE,
    is_deleted_by_civilian BOOLEAN DEFAULT FALSE,
    is_deleted_by_admin BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (application_id) REFERENCES civilian_applications(id) ON DELETE CASCADE,
    FOREIGN KEY (civilian_id) REFERENCES civilian_users(id) ON DELETE CASCADE,
    INDEX idx_application_id (application_id),
    INDEX idx_civilian_id (civilian_id),
    INDEX idx_status (status),
    INDEX idx_responded_by (responded_by),
    INDEX idx_response_date (response_date)
);

-- ================================================================
-- SYSTEM CMS - STRONY I TREŚCI
-- ================================================================

-- Tabela stron CMS
CREATE TABLE IF NOT EXISTS pages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE,
    content LONGTEXT,
    meta_description TEXT,
    meta_keywords TEXT,
    image VARCHAR(500),
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    author_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES web_users(id) ON DELETE SET NULL,
    INDEX idx_slug (slug),
    INDEX idx_status (status),
    INDEX idx_author (author_id)
);

-- ================================================================
-- SYSTEM RAPORTÓW
-- ================================================================

-- Tabela raportów
CREATE TABLE IF NOT EXISTS raports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content LONGTEXT NOT NULL,
    location VARCHAR(255),
    officers TEXT,  -- JSON lub lista funkcjonariuszy
    callers TEXT,   -- JSON lub lista wzywających
    suspect TEXT,   -- JSON lub lista podejrzanych
    actions TEXT,   -- JSON lub lista podjętych działań
    vehicles TEXT,  -- JSON lub lista pojazdów
    evidence TEXT,  -- JSON lub lista dowodów
    author VARCHAR(100) NOT NULL,  -- badge autora
    author_name VARCHAR(200),
    status ENUM('draft', 'submitted', 'approved', 'rejected') DEFAULT 'draft',
    incident_date TIMESTAMP NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- dla kompatybilności
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_author (author),
    INDEX idx_status (status),
    INDEX idx_incident_date (incident_date),
    INDEX idx_created_at (created_at),
    INDEX idx_createdAt (createdAt)
);

-- ================================================================
-- SYSTEM LOGOWANIA AKTYWNOŚCI
-- ================================================================

-- Tabela logów systemu mailbox
CREATE TABLE IF NOT EXISTS mailbox_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(50) NOT NULL,
    action TEXT NOT NULL,
    user VARCHAR(100),  -- badge użytkownika
    ip_address VARCHAR(45),
    user_agent TEXT,
    details JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_type (type),
    INDEX idx_user (user),
    INDEX idx_created_at (created_at),
    INDEX idx_ip (ip_address)
);

-- Tabela logów aktywności admin
CREATE TABLE IF NOT EXISTS admin_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    action VARCHAR(100) NOT NULL,
    target_user VARCHAR(100),
    admin_user VARCHAR(100) NOT NULL,
    details TEXT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_action (action),
    INDEX idx_admin_user (admin_user),
    INDEX idx_target_user (target_user),
    INDEX idx_created_at (created_at)
);

-- ================================================================
-- DODATKOWE TABELE SYSTEMOWE
-- ================================================================

-- Tabela ustawień systemu
CREATE TABLE IF NOT EXISTS system_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_key (setting_key),
    INDEX idx_category (category),
    INDEX idx_public (is_public)
);

-- Tabela uprawnień dla funkcjonariuszy
CREATE TABLE IF NOT EXISTS officer_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    badge_number VARCHAR(20) NOT NULL,
    permission_name VARCHAR(100) NOT NULL,
    permission_value BOOLEAN DEFAULT TRUE,
    granted_by VARCHAR(20),
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (badge_number) REFERENCES lspd_mailbox_users(badge_number) ON DELETE CASCADE,
    INDEX idx_badge (badge_number),
    INDEX idx_permission (permission_name),
    INDEX idx_active (is_active),
    INDEX idx_expires (expires_at),
    UNIQUE KEY unique_badge_permission (badge_number, permission_name)
);

-- Tabela powiadomień systemu
CREATE TABLE IF NOT EXISTS system_notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    recipient_type ENUM('user', 'officer', 'civilian', 'all') NOT NULL,
    recipient_id VARCHAR(100),  -- badge, user_id lub null dla all
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    notification_type ENUM('info', 'warning', 'error', 'success') DEFAULT 'info',
    priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
    is_read BOOLEAN DEFAULT FALSE,
    is_dismissed BOOLEAN DEFAULT FALSE,
    action_url VARCHAR(500),
    action_text VARCHAR(100),
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    INDEX idx_recipient (recipient_type, recipient_id),
    INDEX idx_type (notification_type),
    INDEX idx_priority (priority),
    INDEX idx_read (is_read),
    INDEX idx_created_at (created_at),
    INDEX idx_expires (expires_at)
);

-- ================================================================
-- DANE PRZYKŁADOWE - UŻYTKOWNICY TESTOWI
-- ================================================================

-- Domyślny administrator (hasło: admin123)
INSERT INTO web_users (username, password, fullname, role, rank, active) VALUES 
('admin', '$2b$10$2TNifrz3GcGR.EDFP8b4SuGq7hfZpE70K3ixxe6mu6lGViJWVJIuC', 'Administrator Systemu', 'superadmin', 'Administrator', TRUE)
ON DUPLICATE KEY UPDATE password=VALUES(password);

-- Użytkownik creator (hasło: creator123)
INSERT INTO web_users (username, password, fullname, role, rank, active) VALUES 
('creator', '$2b$10$EeWowqkvnnYfDDELmACFpu6BxpqrYK7XuVBp8sA6AeB1Bke2gE/tO', 'Twórca Systemu', 'creator', 'Creator', TRUE)
ON DUPLICATE KEY UPDATE password=VALUES(password);

-- Przykładowy funkcjonariusz mailbox (hasło: officer123)
INSERT INTO lspd_mailbox_users (badge_number, fullname, rank, department, email, password, is_active, hr_access, psb_access, command_access) VALUES 
('1001', 'John Smith', 'Officer', 'Patrol', 'john.smith@lspd.gov', '$2b$10$iyZjrvvVf1dtLxEecqnKRupKgFqFQkLFnUcaIxOSXUc0Ifg4UW1om', TRUE, 0, 0, 0),
('2001', 'Sarah Johnson', 'Sergeant', 'HR', 'sarah.johnson@lspd.gov', '$2b$10$iyZjrvvVf1dtLxEecqnKRupKgFqFQkLFnUcaIxOSXUc0Ifg4UW1om', TRUE, 1, 0, 0),
('3001', 'Mike Davis', 'Lieutenant', 'PSB', 'mike.davis@lspd.gov', '$2b$10$iyZjrvvVf1dtLxEecqnKRupKgFqFQkLFnUcaIxOSXUc0Ifg4UW1om', TRUE, 0, 1, 0),
('4001', 'Robert Wilson', 'Captain', 'Command', 'robert.wilson@lspd.gov', '$2b$10$iyZjrvvVf1dtLxEecqnKRupKgFqFQkLFnUcaIxOSXUc0Ifg4UW1om', TRUE, 1, 1, 1),
('5001', 'Lisa Brown', 'Detective', 'Investigation', 'lisa.brown@lspd.gov', '$2b$10$iyZjrvvVf1dtLxEecqnKRupKgFqFQkLFnUcaIxOSXUc0Ifg4UW1om', TRUE, 0, 0, 0)
ON DUPLICATE KEY UPDATE password=VALUES(password);

-- Przykładowy obywatel (hasło: citizen123)
INSERT INTO civilian_users (first_name, last_name, email, phone, password, is_active) VALUES 
('Jan', 'Kowalski', 'jankowalski@LS.com', '555-123-456', '$2b$10$7.xkZVmiKi917OZl/DReQu4yOowKzNsveYzYPh8WBU1Q0AAMHChXe', TRUE),
('Anna', 'Nowak', 'annnowak@LS.com', '555-789-012', '$2b$10$7.xkZVmiKi917OZl/DReQu4yOowKzNsveYzYPh8WBU1Q0AAMHChXe', TRUE)
ON DUPLICATE KEY UPDATE password=VALUES(password);

-- Podstawowe ustawienia systemu
INSERT INTO system_settings (setting_key, setting_value, setting_type, description, category, is_public) VALUES 
('site_name', 'Citizen System', 'string', 'Nazwa systemu', 'general', TRUE),
('site_version', '2.0.1', 'string', 'Wersja systemu', 'general', TRUE),
('maintenance_mode', 'false', 'boolean', 'Tryb konserwacji', 'system', FALSE),
('max_file_upload_size', '52428800', 'number', 'Maksymalny rozmiar pliku (bytes)', 'uploads', FALSE),
('allowed_video_formats', '["mp4", "avi", "mov", "wmv"]', 'json', 'Dozwolone formaty video', 'uploads', FALSE),
('civilian_registration_enabled', 'true', 'boolean', 'Czy rejestracja obywateli jest włączona', 'civilian', TRUE),
('email_notifications_enabled', 'true', 'boolean', 'Czy powiadomienia email są włączone', 'notifications', FALSE),
('session_timeout_minutes', '480', 'number', 'Czas wygaśnięcia sesji w minutach', 'security', FALSE),
('password_min_length', '6', 'number', 'Minimalna długość hasła', 'security', TRUE),
('backup_retention_days', '30', 'number', 'Ile dni przechowywać kopie zapasowe', 'system', FALSE)
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value);

-- ================================================================
-- TRIGERY I PROCEDURY
-- ================================================================

-- Trigger do automatycznego ustawiania slug dla stron
DELIMITER //
CREATE TRIGGER IF NOT EXISTS set_page_slug 
BEFORE INSERT ON pages 
FOR EACH ROW 
BEGIN 
    IF NEW.slug IS NULL OR NEW.slug = '' THEN 
        SET NEW.slug = LOWER(REPLACE(REPLACE(REPLACE(NEW.title, ' ', '-'), 'ą', 'a'), 'ę', 'e')); 
    END IF; 
END//
DELIMITER ;

-- Trigger do aktualizacji statusu podania przy odpowiedzi
DELIMITER //
CREATE TRIGGER IF NOT EXISTS update_application_status 
AFTER INSERT ON civ_application_responses 
FOR EACH ROW 
BEGIN 
    UPDATE civilian_applications 
    SET status = NEW.status, updated_at = CURRENT_TIMESTAMP 
    WHERE id = NEW.application_id; 
END//
DELIMITER ;

-- ================================================================
-- INDEKSY DODATKOWE DLA WYDAJNOŚCI
-- ================================================================

-- Indeksy dla wiadomości mailbox
CREATE INDEX IF NOT EXISTS idx_mailbox_unread ON lspd_mailbox (to_badge, is_read, created_at);
CREATE INDEX IF NOT EXISTS idx_mailbox_sender_deleted ON lspd_mailbox (from_badge, is_deleted_sender);
CREATE INDEX IF NOT EXISTS idx_mailbox_receiver_deleted ON lspd_mailbox (to_badge, is_deleted_receiver);

-- Indeksy dla podań cywilnych
CREATE INDEX IF NOT EXISTS idx_applications_status_date ON civilian_applications (status, created_at);
CREATE INDEX IF NOT EXISTS idx_applications_department_status ON civilian_applications (department, status);

-- Indeksy dla odpowiedzi cywilnych
CREATE INDEX IF NOT EXISTS idx_responses_unread_civilian ON civ_application_responses (civilian_id, read_by_civilian);
CREATE INDEX IF NOT EXISTS idx_responses_unread_admin ON civ_application_responses (responded_by, read_by_admin);

-- ================================================================
-- WIDOKI DLA UŁATWIENIA ZAPYTAŃ
-- ================================================================

-- Widok aktywnych wiadomości (nie usuniętych)
CREATE OR REPLACE VIEW active_mailbox_messages AS
SELECT 
    m.*,
    from_user.fullname as from_fullname,
    from_user.rank as from_rank,
    to_user.fullname as to_fullname,
    to_user.rank as to_rank
FROM lspd_mailbox m
LEFT JOIN lspd_mailbox_users from_user ON m.from_badge = from_user.badge_number
LEFT JOIN lspd_mailbox_users to_user ON m.to_badge = to_user.badge_number
WHERE m.is_deleted_sender = FALSE AND m.is_deleted_receiver = FALSE;

-- Widok podań z danymi obywateli
CREATE OR REPLACE VIEW applications_with_users AS
SELECT 
    a.*,
    CONCAT(u.first_name, ' ', u.last_name) as civilian_name,
    u.email as civilian_email,
    u.phone as civilian_phone
FROM civilian_applications a
JOIN civilian_users u ON a.civilian_id = u.id;

-- Widok statystyk podań
CREATE OR REPLACE VIEW application_stats AS
SELECT 
    department,
    status,
    COUNT(*) as count,
    AVG(TIMESTAMPDIFF(HOUR, created_at, updated_at)) as avg_processing_hours
FROM civilian_applications 
GROUP BY department, status;

-- ================================================================
-- PROCEDURY SKŁADOWANE
-- ================================================================

-- Procedura do czyszczenia starych tokenów resetowania hasła
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS CleanupExpiredTokens()
BEGIN
    DELETE FROM password_reset_tokens 
    WHERE expires_at < NOW() OR used = TRUE;
END//
DELIMITER ;

-- Procedura do statystyk mailbox
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS GetMailboxStats(IN badge VARCHAR(20))
BEGIN
    SELECT 
        (SELECT COUNT(*) FROM lspd_mailbox WHERE to_badge = badge AND is_deleted_receiver = FALSE) as total_received,
        (SELECT COUNT(*) FROM lspd_mailbox WHERE to_badge = badge AND is_read = FALSE AND is_deleted_receiver = FALSE) as unread_count,
        (SELECT COUNT(*) FROM lspd_mailbox WHERE from_badge = badge AND is_deleted_sender = FALSE) as total_sent,
        (SELECT COUNT(*) FROM lspd_mailbox WHERE to_badge = badge AND priority = 'urgent' AND is_read = FALSE AND is_deleted_receiver = FALSE) as urgent_unread;
END//
DELIMITER ;

-- Procedura do czyszczenia starych sesji
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS CleanupExpiredSessions()
BEGIN
    DELETE FROM user_sessions 
    WHERE expires_at < NOW() OR is_active = FALSE;
END//
DELIMITER ;

-- Procedura do generowania raportu aktywności
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS GetActivityReport(IN days_back INT)
BEGIN
    SELECT 
        'Messages' as activity_type,
        COUNT(*) as count,
        DATE(created_at) as activity_date
    FROM lspd_mailbox 
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL days_back DAY)
    GROUP BY DATE(created_at)
    
    UNION ALL
    
    SELECT 
        'Applications' as activity_type,
        COUNT(*) as count,
        DATE(created_at) as activity_date
    FROM civilian_applications 
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL days_back DAY)
    GROUP BY DATE(created_at)
    
    UNION ALL
    
    SELECT 
        'Reports' as activity_type,
        COUNT(*) as count,
        DATE(created_at) as activity_date
    FROM raports 
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL days_back DAY)
    GROUP BY DATE(created_at)
    
    ORDER BY activity_date DESC, activity_type;
END//
DELIMITER ;

-- Funkcja do sprawdzania uprawnień
DELIMITER //
CREATE FUNCTION IF NOT EXISTS CheckOfficerPermission(badge VARCHAR(20), permission VARCHAR(100))
RETURNS BOOLEAN
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE has_permission BOOLEAN DEFAULT FALSE;
    
    SELECT COUNT(*) > 0 INTO has_permission
    FROM officer_permissions 
    WHERE badge_number = badge 
      AND permission_name = permission 
      AND is_active = TRUE 
      AND (expires_at IS NULL OR expires_at > NOW());
      
    RETURN has_permission;
END//
DELIMITER ;

-- ================================================================
-- EVENTS - AUTOMATYCZNE ZADANIA
-- ================================================================

-- Event do automatycznego czyszczenia starych tokenów (uruchamia się codziennie)
SET GLOBAL event_scheduler = ON;

CREATE EVENT IF NOT EXISTS cleanup_expired_tokens
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
  CALL CleanupExpiredTokens();

-- Event do czyszczenia starych sesji (uruchamia się co godzinę)
CREATE EVENT IF NOT EXISTS cleanup_expired_sessions
ON SCHEDULE EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP
DO
  CALL CleanupExpiredSessions();

-- Event do czyszczenia starych logów (uruchamia się co tydzień)
CREATE EVENT IF NOT EXISTS cleanup_old_logs
ON SCHEDULE EVERY 1 WEEK
STARTS CURRENT_TIMESTAMP
DO
BEGIN
  DELETE FROM mailbox_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
  DELETE FROM admin_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 180 DAY);
END;

-- ================================================================
-- KOMENTARZE I DOKUMENTACJA
-- ================================================================

/*
INSTRUKCJA UŻYCIA:

1. Importuj ten plik do MySQL:
   mysql -u root -p lspd_mdt < complete_database.sql

2. Lub w phpMyAdmin/Workbench:
   - Utwórz bazę danych 'lspd_mdt'
   - Wybierz bazę danych
   - Otwórz ten plik i wykonaj wszystkie zapytania

3. Sprawdź czy wszystkie tabele zostały utworzone:
   SHOW TABLES;

4. Sprawdź przykładowych użytkowników:
   SELECT * FROM web_users;
   SELECT * FROM lspd_mailbox_users;
   SELECT * FROM civilian_users;

DOMYŚLNE LOGINY:
- Web Admin: admin / admin123
- Web Creator: creator / creator123  
- Mailbox Officer: 1001 / officer123 (John Smith - Patrol)
- Mailbox HR: 2001 / officer123 (Sarah Johnson - HR, ma dostęp HR)
- Mailbox PSB: 3001 / officer123 (Mike Davis - PSB, ma dostęp PSB)
- Mailbox Command: 4001 / officer123 (Robert Wilson - Captain, ma pełny dostęp)
- Civilian: jankowalski@LS.com / citizen123

STRUKTURA BAZY - KOMPLETNE TABELE:

GŁÓWNE SYSTEMY:
- web_users: użytkownicy głównego systemu (admin panel)
- user_sessions: sesje użytkowników web
- password_reset_tokens: tokeny resetowania haseł

SYSTEM MAILBOX LSPD:
- lspd_mailbox_users: funkcjonariusze z dostępem do mailbox
- lspd_mailbox: wiadomości wewnętrzne LSPD
- lspd_mail_attachments: załączniki do wiadomości
- officer_permissions: uprawnienia funkcjonariuszy

SYSTEM CYWILNY:
- civilian_users: zarejestrowani obywatele
- civilian_applications: podania od obywateli
- civ_application_responses: odpowiedzi na podania

SYSTEM TREŚCI:
- pages: strony CMS
- raports: raporty incydentów

SYSTEM LOGOWANIA:
- mailbox_logs: logi aktywności mailbox
- admin_logs: logi aktywności administracyjne

SYSTEM POWIADOMIEŃ:
- system_notifications: powiadomienia systemowe
- system_settings: ustawienia konfiguracyjne

FUNKCJE I PROCEDURY:
- CleanupExpiredTokens(): czyszczenie starych tokenów
- CleanupExpiredSessions(): czyszczenie nieaktywnych sesji
- GetMailboxStats(badge): statystyki mailbox dla użytkownika
- GetActivityReport(days): raport aktywności z ostatnich dni
- CheckOfficerPermission(badge, permission): sprawdzenie uprawnień

AUTOMATYCZNE ZADANIA (EVENTS):
- cleanup_expired_tokens: codzienne czyszczenie tokenów
- cleanup_expired_sessions: co godzinę czyszczenie sesji  
- cleanup_old_logs: cotygodniowe czyszczenie starych logów

UPRAWNIENIA DOSTĘPU:
- hr_access: dostęp do funkcji HR
- psb_access: dostęp do funkcji PSB (Professional Standards Bureau)
- command_access: dostęp do funkcji dowodzenia

WAŻNE UWAGI:
- Wszystkie hasła są zahashowane przez bcrypt ($2b$10$...)
- System obsługuje upload video do 50MB (domyślnie)
- Sesje wygasają po 8 godzinach (480 minut)
- Logi są automatycznie czyszczone (mailbox: 90 dni, admin: 180 dni)
- Event scheduler musi być włączony: SET GLOBAL event_scheduler = ON;

BEZPIECZEŃSTWO:
- Zmień wszystkie domyślne hasła po instalacji
- Skonfiguruj odpowiednie uprawnienia MySQL
- Włącz SSL dla połączeń z bazą danych
- Regularnie twórz kopie zapasowe
- Monitoruj logi bezpieczeństwa

ROZWÓJ:
- Wszystkie tabele mają odpowiednie indeksy dla wydajności
- Foreign keys zapewniają integralność danych
- Triggery automatyzują niektóre operacje
- Widoki ułatwiają złożone zapytania
*/

-- ================================================================
-- KONIEC PLIKU
-- ================================================================

SELECT 'Database structure created successfully!' as status;
