-- ========================================
-- CIVILIAN USERS - TABELA UŻYTKOWNIKÓW CYWILNYCH
-- ========================================
-- Uruchom ten skrypt w phpMyAdmin lub MySQL CLI
-- ========================================

-- Użyj istniejącej bazy danych
-- USE PC_citizen;

-- ========================================
-- USUNIĘCIE STARYCH TABEL (w odpowiedniej kolejności - FK)
-- ========================================

-- ========================================
-- TABELA LOGÓW AKTYWNOŚCI
-- ========================================
CREATE TABLE civilian_activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    
    action VARCHAR(100) NOT NULL COMMENT 'login, logout, register, password_change, etc.',
    description TEXT,
    
    ip_address VARCHAR(45),
    user_agent VARCHAR(500),
    
    -- Dodatkowe dane (JSON)
    metadata JSON NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- TABELA PRÓB LOGOWANIA (BEZPIECZEŃSTWO)
-- ========================================
CREATE TABLE civilian_login_attempts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL COMMENT 'NULL jeśli nieznany użytkownik',
    email VARCHAR(150) NOT NULL,
    
    ip_address VARCHAR(45),
    user_agent VARCHAR(500),
    
    success BOOLEAN DEFAULT FALSE,
    reason VARCHAR(100) COMMENT 'ok, wrong_password, not_found, banned, locked',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_email (email),
    INDEX idx_ip_address (ip_address),
    INDEX idx_success (success),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- TABELA POWIADOMIEŃ UŻYTKOWNIKÓW
-- ========================================
CREATE TABLE civilian_notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    
    title VARCHAR(100) NOT NULL,
    message TEXT NOT NULL,
    
    type ENUM('info', 'success', 'warning', 'error', 'system') DEFAULT 'info',
    
    is_read BOOLEAN DEFAULT FALSE,
    read_at TIMESTAMP NULL,
    
    -- Link do akcji (opcjonalnie)
    action_url VARCHAR(255) NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_is_read (is_read),
    INDEX idx_created_at (created_at),
    
    FOREIGN KEY (user_id) REFERENCES civilian_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- ZAKOŃCZENIE
-- ========================================
SELECT 'Civilian users database setup completed!' AS message;
