-- ========================================
-- Citizen System DATABASE SCHEMA
-- System przechowywania wszystkich danych MDT
-- (Wersja bez foreign keys dla kompatybilności)
-- ========================================

-- Tabela: Nakazy (Warrants)
CREATE TABLE IF NOT EXISTS warrants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type ENUM('arrest', 'search') NOT NULL COMMENT 'Typ nakazu: aresztowanie lub przeszukanie',
    subject VARCHAR(255) NOT NULL COMMENT 'Osoba/miejsce którego dotyczy nakaz',
    charges TEXT NOT NULL COMMENT 'Zarzuty',
    judge VARCHAR(255) NOT NULL COMMENT 'Sędzia wydający nakaz',
    bail DECIMAL(10, 2) DEFAULT 0 COMMENT 'Wysokość kaucji w USD',
    conditions TEXT COMMENT 'Warunki wykonania nakazu',
    status ENUM('active', 'served', 'expired') DEFAULT 'active' COMMENT 'Status nakazu',
    issue_date DATE NOT NULL COMMENT 'Data wydania',
    expiry_date DATE COMMENT 'Data wygaśnięcia',
    issued_by INT COMMENT 'ID użytkownika który wystawił nakaz',
    served_by INT COMMENT 'ID użytkownika który wykonał nakaz',
    served_date DATETIME COMMENT 'Data i czas wykonania',
    notes TEXT COMMENT 'Dodatkowe notatki',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_subject (subject),
    INDEX idx_issue_date (issue_date),
    INDEX idx_issued_by (issued_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela: BOLO (Be On The Lookout)
CREATE TABLE IF NOT EXISTS bolo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type ENUM('person', 'vehicle', 'item') NOT NULL COMMENT 'Typ BOLO',
    title VARCHAR(255) NOT NULL COMMENT 'Tytuł/nazwa',
    description TEXT NOT NULL COMMENT 'Szczegółowy opis',
    priority ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium' COMMENT 'Priorytet',
    status ENUM('active', 'resolved', 'cancelled') DEFAULT 'active' COMMENT 'Status',
    
    -- Szczegóły dla osoby
    person_name VARCHAR(255) COMMENT 'Imię i nazwisko (dla type=person)',
    person_age INT COMMENT 'Wiek (dla type=person)',
    person_description TEXT COMMENT 'Opis wyglądu (dla type=person)',
    
    -- Szczegóły dla pojazdu
    vehicle_plate VARCHAR(50) COMMENT 'Tablica rejestracyjna (dla type=vehicle)',
    vehicle_model VARCHAR(100) COMMENT 'Model pojazdu (dla type=vehicle)',
    vehicle_color VARCHAR(50) COMMENT 'Kolor pojazdu (dla type=vehicle)',
    
    -- Szczegóły dla przedmiotu
    item_description TEXT COMMENT 'Opis przedmiotu (dla type=item)',
    
    last_seen_location VARCHAR(255) COMMENT 'Ostatnie znane miejsce',
    last_seen_date DATETIME COMMENT 'Data ostatniego widzenia',
    danger_level ENUM('none', 'low', 'medium', 'high', 'extreme') DEFAULT 'none' COMMENT 'Poziom zagrożenia',
    armed BOOLEAN DEFAULT FALSE COMMENT 'Czy uzbrojony',
    officer_notes TEXT COMMENT 'Notatki oficera',
    
    issued_by INT COMMENT 'ID użytkownika który wystawił BOLO',
    resolved_by INT COMMENT 'ID użytkownika który rozwiązał BOLO',
    resolved_date DATETIME COMMENT 'Data rozwiązania',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (issued_by) REFERENCES web_users(id) ON DELETE SET NULL,
    FOREIGN KEY (resolved_by) REFERENCES web_users(id) ON DELETE SET NULL,
    INDEX idx_type (type),
    INDEX idx_status (status),
    INDEX idx_priority (priority),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela: Raporty (Reports)
CREATE TABLE IF NOT EXISTS reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    report_number VARCHAR(50) UNIQUE NOT NULL COMMENT 'Numer raportu (np. RPT-2024-001)',
    type ENUM('incident', 'arrest', 'traffic', 'investigation', 'use_of_force', 'other') NOT NULL COMMENT 'Typ raportu',
    title VARCHAR(255) NOT NULL COMMENT 'Tytuł raportu',
    incident_date DATETIME NOT NULL COMMENT 'Data i czas zdarzenia',
    location VARCHAR(255) NOT NULL COMMENT 'Lokalizacja zdarzenia',
    
    -- Osoby zaangażowane
    suspects TEXT COMMENT 'JSON z listą podejrzanych',
    witnesses TEXT COMMENT 'JSON z listą świadków',
    victims TEXT COMMENT 'JSON z listą ofiar',
    
    -- Szczegóły raportu
    narrative TEXT NOT NULL COMMENT 'Opis zdarzenia',
    evidence TEXT COMMENT 'Dowody zebrane na miejscu',
    injuries TEXT COMMENT 'Obrażenia',
    property_damage TEXT COMMENT 'Szkody materialne',
    
    -- Status i zatwierdzenie
    status ENUM('draft', 'submitted', 'under_review', 'approved', 'rejected') DEFAULT 'draft' COMMENT 'Status raportu',
    priority ENUM('low', 'medium', 'high') DEFAULT 'medium' COMMENT 'Priorytet',
    
    -- Oficerowie
    reporting_officer INT NOT NULL COMMENT 'ID oficera sporządzającego raport',
    supervising_officer INT COMMENT 'ID oficera nadzorującego',
    additional_officers TEXT COMMENT 'JSON z listą dodatkowych oficerów',
    
    -- Zatwierdzenie
    reviewed_by INT COMMENT 'ID osoby przeglądającej',
    reviewed_date DATETIME COMMENT 'Data przeglądu',
    review_notes TEXT COMMENT 'Notatki z przeglądu',
    
    -- Załączniki
    attachments TEXT COMMENT 'JSON z listą załączników',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (reporting_officer) REFERENCES web_users(id) ON DELETE RESTRICT,
    FOREIGN KEY (supervising_officer) REFERENCES web_users(id) ON DELETE SET NULL,
    FOREIGN KEY (reviewed_by) REFERENCES web_users(id) ON DELETE SET NULL,
    INDEX idx_report_number (report_number),
    INDEX idx_type (type),
    INDEX idx_status (status),
    INDEX idx_incident_date (incident_date),
    INDEX idx_reporting_officer (reporting_officer)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela: Akta osób (Person Records)
CREATE TABLE IF NOT EXISTS person_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL COMMENT 'Imię',
    last_name VARCHAR(100) NOT NULL COMMENT 'Nazwisko',
    date_of_birth DATE COMMENT 'Data urodzenia',
    ssn VARCHAR(20) COMMENT 'Numer ubezpieczenia społecznego',
    phone VARCHAR(20) COMMENT 'Numer telefonu',
    email VARCHAR(255) COMMENT 'Email',
    
    -- Adres
    address_street VARCHAR(255) COMMENT 'Ulica i numer',
    address_city VARCHAR(100) COMMENT 'Miasto',
    address_state VARCHAR(50) COMMENT 'Stan',
    address_zip VARCHAR(20) COMMENT 'Kod pocztowy',
    
    -- Dane fizyczne
    height INT COMMENT 'Wzrost w cm',
    weight INT COMMENT 'Waga w kg',
    eye_color VARCHAR(50) COMMENT 'Kolor oczu',
    hair_color VARCHAR(50) COMMENT 'Kolor włosów',
    race VARCHAR(50) COMMENT 'Rasa',
    gender ENUM('M', 'F', 'Other') COMMENT 'Płeć',
    
    -- Identyfikatory
    drivers_license VARCHAR(50) COMMENT 'Prawo jazdy',
    license_status ENUM('valid', 'suspended', 'revoked', 'expired') COMMENT 'Status prawa jazdy',
    
    -- Zdjęcia i dokumenty
    photo_url VARCHAR(500) COMMENT 'URL zdjęcia',
    fingerprints TEXT COMMENT 'Dane odcisków palców',
    
    -- Status i flagi
    gang_affiliation VARCHAR(255) COMMENT 'Przynależność do gangu',
    wanted BOOLEAN DEFAULT FALSE COMMENT 'Czy poszukiwany',
    dangerous BOOLEAN DEFAULT FALSE COMMENT 'Czy niebezpieczny',
    mental_health_notes TEXT COMMENT 'Notatki dotyczące zdrowia psychicznego',
    
    -- Historia
    arrest_count INT DEFAULT 0 COMMENT 'Liczba aresztowań',
    citation_count INT DEFAULT 0 COMMENT 'Liczba mandatów',
    last_contact_date DATETIME COMMENT 'Data ostatniego kontaktu',
    last_contact_reason VARCHAR(255) COMMENT 'Powód ostatniego kontaktu',
    
    -- Meta
    notes TEXT COMMENT 'Dodatkowe notatki',
    created_by INT COMMENT 'ID użytkownika który utworzył rekord',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (created_by) REFERENCES web_users(id) ON DELETE SET NULL,
    INDEX idx_name (last_name, first_name),
    INDEX idx_dob (date_of_birth),
    INDEX idx_ssn (ssn),
    INDEX idx_wanted (wanted),
    FULLTEXT idx_fulltext (first_name, last_name, notes)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela: Akta pojazdów (Vehicle Records)
CREATE TABLE IF NOT EXISTS vehicle_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plate VARCHAR(50) NOT NULL UNIQUE COMMENT 'Tablica rejestracyjna',
    vin VARCHAR(100) COMMENT 'VIN (Vehicle Identification Number)',
    make VARCHAR(100) NOT NULL COMMENT 'Marka',
    model VARCHAR(100) NOT NULL COMMENT 'Model',
    year INT COMMENT 'Rok produkcji',
    color VARCHAR(50) COMMENT 'Kolor',
    
    -- Właściciel
    owner_person_id INT COMMENT 'ID właściciela z person_records',
    owner_name VARCHAR(255) COMMENT 'Imię i nazwisko właściciela',
    
    -- Rejestracja i status
    registration_status ENUM('valid', 'expired', 'suspended', 'revoked') DEFAULT 'valid' COMMENT 'Status rejestracji',
    registration_expiry DATE COMMENT 'Data wygaśnięcia rejestracji',
    insurance_status ENUM('valid', 'expired', 'none') DEFAULT 'none' COMMENT 'Status ubezpieczenia',
    
    -- Flagi
    stolen BOOLEAN DEFAULT FALSE COMMENT 'Czy skradziony',
    impounded BOOLEAN DEFAULT FALSE COMMENT 'Czy skonfiskowany',
    bolo_active BOOLEAN DEFAULT FALSE COMMENT 'Czy aktywny BOLO',
    
    -- Historia
    stop_count INT DEFAULT 0 COMMENT 'Liczba zatrzymań',
    citation_count INT DEFAULT 0 COMMENT 'Liczba mandatów',
    last_seen_location VARCHAR(255) COMMENT 'Ostatnie znane miejsce',
    last_seen_date DATETIME COMMENT 'Data ostatniego widzenia',
    
    notes TEXT COMMENT 'Dodatkowe notatki',
    created_by INT COMMENT 'ID użytkownika który utworzył rekord',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (owner_person_id) REFERENCES person_records(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES web_users(id) ON DELETE SET NULL,
    INDEX idx_plate (plate),
    INDEX idx_vin (vin),
    INDEX idx_owner (owner_person_id),
    INDEX idx_stolen (stolen),
    FULLTEXT idx_fulltext (make, model, owner_name, notes)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela: Mandaty (Citations)
CREATE TABLE IF NOT EXISTS citations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    citation_number VARCHAR(50) UNIQUE NOT NULL COMMENT 'Numer mandatu',
    type ENUM('traffic', 'parking', 'municipal', 'other') DEFAULT 'traffic' COMMENT 'Typ mandatu',
    
    -- Osoba ukarana
    person_id INT COMMENT 'ID osoby z person_records',
    person_name VARCHAR(255) NOT NULL COMMENT 'Imię i nazwisko',
    drivers_license VARCHAR(50) COMMENT 'Prawo jazdy',
    
    -- Pojazd (jeśli dotyczy)
    vehicle_id INT COMMENT 'ID pojazdu z vehicle_records',
    vehicle_plate VARCHAR(50) COMMENT 'Tablica rejestracyjna',
    
    -- Szczegóły wykroczenia
    violation_code VARCHAR(50) COMMENT 'Kod wykroczenia',
    violation_description TEXT NOT NULL COMMENT 'Opis wykroczenia',
    location VARCHAR(255) NOT NULL COMMENT 'Miejsce wykroczenia',
    violation_date DATETIME NOT NULL COMMENT 'Data i czas wykroczenia',
    
    -- Kara finansowa
    fine_amount DECIMAL(10, 2) NOT NULL COMMENT 'Wysokość mandatu',
    payment_status ENUM('unpaid', 'paid', 'partial', 'waived') DEFAULT 'unpaid' COMMENT 'Status płatności',
    payment_date DATE COMMENT 'Data zapłaty',
    
    -- Punkty karne
    points INT DEFAULT 0 COMMENT 'Liczba punktów karnych',
    
    -- Oficer wystawiający
    issued_by INT NOT NULL COMMENT 'ID oficera wystawiającego mandat',
    
    -- Status
    status ENUM('active', 'paid', 'contested', 'dismissed', 'warrant_issued') DEFAULT 'active' COMMENT 'Status mandatu',
    due_date DATE COMMENT 'Termin płatności',
    
    notes TEXT COMMENT 'Dodatkowe notatki',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (person_id) REFERENCES person_records(id) ON DELETE SET NULL,
    FOREIGN KEY (vehicle_id) REFERENCES vehicle_records(id) ON DELETE SET NULL,
    FOREIGN KEY (issued_by) REFERENCES web_users(id) ON DELETE RESTRICT,
    INDEX idx_citation_number (citation_number),
    INDEX idx_person (person_id),
    INDEX idx_vehicle (vehicle_id),
    INDEX idx_status (status),
    INDEX idx_violation_date (violation_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela: Aresztowania (Arrests)
CREATE TABLE IF NOT EXISTS arrests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    arrest_number VARCHAR(50) UNIQUE NOT NULL COMMENT 'Numer aresztowania',
    
    -- Aresztowany
    person_id INT COMMENT 'ID osoby z person_records',
    person_name VARCHAR(255) NOT NULL COMMENT 'Imię i nazwisko',
    
    -- Szczegóły aresztowania
    arrest_date DATETIME NOT NULL COMMENT 'Data i czas aresztowania',
    location VARCHAR(255) NOT NULL COMMENT 'Miejsce aresztowania',
    charges TEXT NOT NULL COMMENT 'Zarzuty',
    
    -- Oficerowie
    arresting_officer INT NOT NULL COMMENT 'ID oficera aresztującego',
    assisting_officers TEXT COMMENT 'JSON z listą oficerów asystujących',
    
    -- Okoliczności
    narrative TEXT COMMENT 'Opis przebiegu aresztowania',
    use_of_force BOOLEAN DEFAULT FALSE COMMENT 'Czy użyto siły',
    force_description TEXT COMMENT 'Opis użycia siły',
    injuries BOOLEAN DEFAULT FALSE COMMENT 'Czy wystąpiły obrażenia',
    injury_description TEXT COMMENT 'Opis obrażeń',
    
    -- Kaucja i sąd
    bail_amount DECIMAL(10, 2) COMMENT 'Wysokość kaucji',
    bail_posted BOOLEAN DEFAULT FALSE COMMENT 'Czy wpłacono kaucję',
    court_date DATE COMMENT 'Data rozprawy',
    
    -- Status
    status ENUM('in_custody', 'released', 'transferred', 'bailed') DEFAULT 'in_custody' COMMENT 'Status',
    release_date DATETIME COMMENT 'Data zwolnienia',
    release_reason VARCHAR(255) COMMENT 'Powód zwolnienia',
    
    -- Dowody
    evidence TEXT COMMENT 'JSON z listą dowodów',
    property_seized TEXT COMMENT 'Skonfiskowane mienie',
    
    notes TEXT COMMENT 'Dodatkowe notatki',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (person_id) REFERENCES person_records(id) ON DELETE SET NULL,
    FOREIGN KEY (arresting_officer) REFERENCES web_users(id) ON DELETE RESTRICT,
    INDEX idx_arrest_number (arrest_number),
    INDEX idx_person (person_id),
    INDEX idx_arrest_date (arrest_date),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela: Notatki służbowe (Officer Notes)
CREATE TABLE IF NOT EXISTS officer_notes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    officer_id INT NOT NULL COMMENT 'ID oficera',
    note_type ENUM('general', 'person', 'vehicle', 'location', 'investigation') DEFAULT 'general' COMMENT 'Typ notatki',
    
    -- Powiązania
    person_id INT COMMENT 'ID osoby (jeśli dotyczy)',
    vehicle_id INT COMMENT 'ID pojazdu (jeśli dotyczy)',
    report_id INT COMMENT 'ID raportu (jeśli dotyczy)',
    
    title VARCHAR(255) COMMENT 'Tytuł notatki',
    content TEXT NOT NULL COMMENT 'Treść notatki',
    priority ENUM('low', 'medium', 'high') DEFAULT 'medium' COMMENT 'Priorytet',
    
    -- Widoczność
    private BOOLEAN DEFAULT FALSE COMMENT 'Czy prywatna (tylko dla autora)',
    shared_with TEXT COMMENT 'JSON z listą ID użytkowników którzy mają dostęp',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (officer_id) REFERENCES web_users(id) ON DELETE CASCADE,
    FOREIGN KEY (person_id) REFERENCES person_records(id) ON DELETE CASCADE,
    FOREIGN KEY (vehicle_id) REFERENCES vehicle_records(id) ON DELETE CASCADE,
    FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE,
    INDEX idx_officer (officer_id),
    INDEX idx_type (note_type),
    INDEX idx_created (created_at),
    FULLTEXT idx_content (title, content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela: Logi aktywności (Activity Logs)
CREATE TABLE IF NOT EXISTS mdt_activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL COMMENT 'ID użytkownika',
    action VARCHAR(100) NOT NULL COMMENT 'Typ akcji',
    table_name VARCHAR(50) COMMENT 'Nazwa tabeli',
    record_id INT COMMENT 'ID rekordu',
    description TEXT COMMENT 'Opis akcji',
    ip_address VARCHAR(45) COMMENT 'Adres IP',
    user_agent VARCHAR(500) COMMENT 'User Agent',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES web_users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_action (action),
    INDEX idx_created (created_at),
    INDEX idx_table_record (table_name, record_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Wstaw przykładowe dane (opcjonalnie)
INSERT INTO warrants (type, subject, charges, judge, bail, conditions, status, issue_date, expiry_date) VALUES
('arrest', 'Marcus Johnson', 'Napad z bronią w ręku (PC 211), Ucieczka przed aresztowaniem (PC 148)', 'Sędzia R. Thompson', 50000.00, 'Bez kaucji do czasu stawienia się przed sądem', 'active', '2024-01-15', '2025-01-15'),
('search', 'Grove Street 104', 'Podejrzenie produkcji narkotyków', 'Sędzia K. Martinez', 0.00, 'Przeszukanie dozwolone 24/7, podejrzenie działalności przestępczej', 'active', '2024-01-10', '2024-02-10'),
('arrest', 'Trevor Phillips', 'Morderstwo (PC 187), Handel narkotykami (HS 11351)', 'Sędzia M. Davis', 0.00, 'Bez kaucji - zagrożenie dla społeczeństwa', 'active', '2023-12-20', '2024-12-20');
