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

-- Główni użytkownicy systemu
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
);

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

-- Użytkownicy mailbox 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,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL
);

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

-- Użytkownicy cywilni
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)
);

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

-- Dane testowe
INSERT INTO web_users (username, password, fullname, role, rank, active) VALUES 
('admin', '$2b$10$K8gF2J3mN4oP6qR8sT1uVe.LxY9zA2bC3dE4fG5hI6jK7lM8nO9pQ', 'Administrator', 'superadmin', 'Administrator', TRUE),
('creator', '$2b$10$K8gF2J3mN4oP6qR8sT1uVe.LxY9zA2bC3dE4fG5hI6jK7lM8nO9pQ', 'Creator', 'creator', 'Creator', TRUE)
ON DUPLICATE KEY UPDATE id=id;

INSERT INTO lspd_mailbox_users (badge_number, fullname, rank, department, email, password, is_active) VALUES 
('1001', 'John Smith', 'Officer', 'Patrol', 'john.smith@lspd.gov', '$2b$10$K8gF2J3mN4oP6qR8sT1uVe.LxY9zA2bC3dE4fG5hI6jK7lM8nO9pQ', TRUE),
('2001', 'Sarah Johnson', 'Sergeant', 'HR', 'sarah.johnson@lspd.gov', '$2b$10$K8gF2J3mN4oP6qR8sT1uVe.LxY9zA2bC3dE4fG5hI6jK7lM8nO9pQ', TRUE)
ON DUPLICATE KEY UPDATE id=id;

SELECT 'Quick database setup completed!' as status;
