-- ================================================================
-- PC CITIZEN SYSTEM - KOMPLETNA BAZA DANYCH
-- Data utworzenia: 2 grudnia 2025
-- Opis: Pełna struktura wszystkich tabel w jednym pliku
-- ================================================================

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

USE pc_citizen;

-- ================================================================
-- 1. TABELE UŻYTKOWNIKÓW I AUTORYZACJI
-- ================================================================

-- Tabela głównych użytkowników systemu (cywile)
CREATE TABLE IF NOT EXISTS web_users_new (
    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),
    
    -- 2FA Support
    two_factor_enabled BOOLEAN DEFAULT FALSE,
    two_factor_secret VARCHAR(255) DEFAULT NULL,
    backup_codes TEXT DEFAULT NULL,
    
    -- Theme customization
    theme_settings TEXT DEFAULT NULL,
    
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_role (role),
    INDEX idx_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela użytkowników PD (Police Department)
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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela sesji mailbox
CREATE TABLE IF NOT EXISTS mailbox_sessions (
    id VARCHAR(255) PRIMARY KEY,
    user_badge VARCHAR(50) NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    
    INDEX idx_badge (user_badge),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela historii logowań
CREATE TABLE IF NOT EXISTS login_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    username VARCHAR(50),
    ip_address VARCHAR(45),
    user_agent TEXT,
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    logout_time TIMESTAMP NULL,
    session_duration INT COMMENT 'Duration in seconds',
    login_status ENUM('success', 'failed', 'blocked') DEFAULT 'success',
    failure_reason VARCHAR(255),
    
    INDEX idx_user_id (user_id),
    INDEX idx_username (username),
    INDEX idx_login_time (login_time),
    INDEX idx_status (login_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 2. SYSTEM MAILBOX - KOMUNIKACJA WEWNĘTRZNA
-- ================================================================

-- Tabela wiadomości mailbox LSPD
CREATE TABLE IF NOT EXISTS lspd_mailbox (
    id INT AUTO_INCREMENT PRIMARY KEY,
    from_badge VARCHAR(20) NOT NULL,
    to_badge VARCHAR(20) NOT NULL,
    subject VARCHAR(500) NOT NULL,
    message TEXT NOT NULL,
    priority ENUM('normal', 'high', 'urgent') DEFAULT 'normal',
    category VARCHAR(50) DEFAULT 'general',
    is_read BOOLEAN DEFAULT FALSE,
    is_deleted_sender BOOLEAN DEFAULT FALSE,
    is_deleted_receiver BOOLEAN DEFAULT FALSE,
    read_at TIMESTAMP NULL,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Enhanced features
    parent_message_id INT NULL COMMENT 'For threading',
    draft BOOLEAN DEFAULT FALSE,
    scheduled_send TIMESTAMP NULL,
    tags VARCHAR(255) DEFAULT NULL,
    attachments TEXT DEFAULT NULL,
    
    INDEX idx_from (from_badge),
    INDEX idx_to (to_badge),
    INDEX idx_sent_at (sent_at),
    INDEX idx_is_read (is_read),
    INDEX idx_priority (priority),
    INDEX idx_category (category),
    INDEX idx_parent (parent_message_id),
    INDEX idx_draft (draft)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela szablonów wiadomości
CREATE TABLE IF NOT EXISTS message_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_badge VARCHAR(20) NOT NULL,
    template_name VARCHAR(100) NOT NULL,
    subject VARCHAR(500),
    message TEXT,
    category VARCHAR(50),
    priority ENUM('normal', 'high', 'urgent') DEFAULT 'normal',
    is_shared BOOLEAN DEFAULT FALSE,
    usage_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_user (user_badge),
    INDEX idx_shared (is_shared)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela zaplanowanych wiadomości
CREATE TABLE IF NOT EXISTS scheduled_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    from_badge VARCHAR(20) NOT NULL,
    to_badge VARCHAR(20) NOT NULL,
    subject VARCHAR(500) NOT NULL,
    message TEXT NOT NULL,
    priority ENUM('normal', 'high', 'urgent') DEFAULT 'normal',
    category VARCHAR(50) DEFAULT 'general',
    scheduled_for TIMESTAMP NOT NULL,
    sent BOOLEAN DEFAULT FALSE,
    sent_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_scheduled (scheduled_for),
    INDEX idx_sent (sent),
    INDEX idx_from (from_badge)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 3. SYSTEM APLIKACJI CYWILNYCH
-- ================================================================

-- Tabela aplikacji cywilnych (podania)
CREATE TABLE IF NOT EXISTS civilian_applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    application_type VARCHAR(100) NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    status ENUM('pending', 'approved', 'rejected', 'under_review') DEFAULT 'pending',
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    reviewed_at TIMESTAMP NULL,
    reviewed_by VARCHAR(100),
    reviewer_notes TEXT,
    
    INDEX idx_user (user_id),
    INDEX idx_type (application_type),
    INDEX idx_status (status),
    INDEX idx_submitted (submitted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela odpowiedzi na aplikacje
CREATE TABLE IF NOT EXISTS application_responses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    application_id INT NOT NULL,
    responder_badge VARCHAR(20) NOT NULL,
    response_message TEXT NOT NULL,
    response_status ENUM('approved', 'rejected', 'needs_info', 'pending') NOT NULL,
    responded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_app (application_id),
    INDEX idx_responder (responder_badge),
    FOREIGN KEY (application_id) REFERENCES civilian_applications(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 4. SYSTEM DOKUMENTÓW I NOTATEK
-- ================================================================

-- Tabela dokumentów Word
CREATE TABLE IF NOT EXISTS word_documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    content LONGTEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT FALSE,
    
    INDEX idx_user (user_id),
    INDEX idx_created (created_at),
    INDEX idx_deleted (is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela notatek
CREATE TABLE IF NOT EXISTS notepad_notes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL DEFAULT 'Untitled Note',
    content LONGTEXT,
    color VARCHAR(20) DEFAULT '#fff59d',
    position_x INT DEFAULT 100,
    position_y INT DEFAULT 100,
    width INT DEFAULT 300,
    height INT DEFAULT 200,
    z_index INT DEFAULT 1,
    is_minimized BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_user (user_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 5. SYSTEM BODY CAM I STREAMING
-- ================================================================

-- Tabela streamów body cam
CREATE TABLE IF NOT EXISTS bodycam_streams (
    id INT AUTO_INCREMENT PRIMARY KEY,
    officer_badge VARCHAR(20) NOT NULL,
    officer_name VARCHAR(200) NOT NULL,
    peer_id VARCHAR(100) UNIQUE NOT NULL,
    stream_status ENUM('active', 'paused', 'ended') DEFAULT 'active',
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ended_at TIMESTAMP NULL,
    viewers INT DEFAULT 0,
    location VARCHAR(255),
    incident_id VARCHAR(100),
    video_path VARCHAR(500) DEFAULT NULL,
    
    INDEX idx_officer (officer_badge),
    INDEX idx_peer (peer_id),
    INDEX idx_status (stream_status),
    INDEX idx_started (started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela dowodów wideo
CREATE TABLE IF NOT EXISTS video_evidence (
    id INT AUTO_INCREMENT PRIMARY KEY,
    officer_badge VARCHAR(20) NOT NULL,
    case_number VARCHAR(100) NOT NULL,
    video_filename VARCHAR(500) NOT NULL,
    video_path VARCHAR(500) NOT NULL,
    duration_seconds INT,
    file_size_mb DECIMAL(10,2),
    upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    incident_date DATETIME NULL,
    location VARCHAR(255),
    description TEXT,
    tags VARCHAR(500),
    status ENUM('pending_review', 'approved', 'archived') DEFAULT 'pending_review',
    
    INDEX idx_officer (officer_badge),
    INDEX idx_case (case_number),
    INDEX idx_upload (upload_date),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 6. SYSTEM BEZPIECZEŃSTWA
-- ================================================================

-- Tabela ustawień bezpieczeństwa
CREATE TABLE IF NOT EXISTS security_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    setting_type ENUM('boolean', 'string', 'number', 'json') DEFAULT 'string',
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updated_by VARCHAR(100),
    
    INDEX idx_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela kodów instalacyjnych (2FA backup codes)
CREATE TABLE IF NOT EXISTS install_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    code_hash VARCHAR(255) NOT NULL,
    used BOOLEAN DEFAULT FALSE,
    used_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user (user_id),
    INDEX idx_used (used),
    FOREIGN KEY (user_id) REFERENCES web_users_new(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- DANE POCZĄTKOWE
-- ================================================================

-- Dodaj domyślne ustawienia bezpieczeństwa
INSERT IGNORE INTO security_settings (setting_key, setting_value, setting_type, description) VALUES
('discord_webhooks_enabled', 'true', 'boolean', 'Enable Discord webhook notifications'),
('discord_webhook_url', '', 'string', 'Discord webhook URL for notifications'),
('rate_limit_enabled', 'true', 'boolean', 'Enable rate limiting'),
('max_login_attempts', '5', 'number', 'Maximum login attempts before lockout'),
('lockout_duration_minutes', '15', 'number', 'Account lockout duration in minutes'),
('session_timeout_hours', '8', 'number', 'Session timeout in hours'),
('2fa_required_for_admin', 'false', 'boolean', 'Require 2FA for admin accounts'),
('bot_detection_enabled', 'true', 'boolean', 'Enable bot detection'),
('ip_whitelist', '[]', 'json', 'List of whitelisted IP addresses'),
('maintenance_mode', 'false', 'boolean', 'Enable maintenance mode');

-- ================================================================
-- KONIEC SKRYPTU
-- ================================================================

-- Pokaż utworzone tabele
SHOW TABLES;

SELECT 'Database setup completed successfully!' as Status;
