
-- Citizen System - UPROSZCZONA STRUKTURA BAZY DANYCH (QUICK SETUP)
-- ================================================================

CREATE DATABASE IF NOT EXISTS lspd_mdt CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE lspd_mdt;

-- Wiadomości 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,
    priority ENUM('normal', 'high', 'urgent') DEFAULT 'normal',
    category VARCHAR(100) DEFAULT 'general',
    reply_to INT NULL,
    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
);

-- Podania cywilne
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,
    reviewed_by VARCHAR(100) NULL,
    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
);

-- Odpowiedzi na podania
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),
    responded_by_name VARCHAR(255),
    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
);

-- Strony CMS
CREATE TABLE IF NOT EXISTS pages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE,
    content LONGTEXT,
    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
);

-- Raporty
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,
    callers TEXT,
    suspect TEXT,
    actions TEXT,
    vehicles TEXT,
    evidence TEXT,
    author VARCHAR(100) NOT NULL,
    author_name VARCHAR(200),
    status ENUM('draft', 'submitted', 'approved', 'rejected') DEFAULT 'draft',
    incident_date TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Logi 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),
    ip_address VARCHAR(45),
    user_agent TEXT,
    details JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Logi 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
);

SELECT 'Quick database setup completed!' as status;
