-- ========================================
-- 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 UŻYTKOWNIKÓW CYWILNYCH
-- ========================================
CREATE TABLE civilian_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Dane osobowe
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    phone VARCHAR(20),
    password VARCHAR(255) NOT NULL,
    
    -- Avatar
    avatar VARCHAR(255) DEFAULT NULL,
    
    -- Status konta
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE,
    is_banned BOOLEAN DEFAULT FALSE,
    ban_reason TEXT NULL,
    banned_at TIMESTAMP NULL,
    banned_by INT NULL,
    
    -- Dane weryfikacyjne
    verification_token VARCHAR(255) NULL,
    verification_expires TIMESTAMP NULL,
    
    -- Reset hasła
    reset_token VARCHAR(255) NULL,
    reset_token_expires TIMESTAMP NULL,
    
    -- 2FA
    two_factor_enabled BOOLEAN DEFAULT FALSE,
    two_factor_secret VARCHAR(255) NULL,
    
    -- Dane logowania
    last_login TIMESTAMP NULL,
    last_ip VARCHAR(45) NULL COMMENT 'Ostatnie IP logowania',
    login_count INT DEFAULT 0,
    failed_login_attempts INT DEFAULT 0,
    locked_until TIMESTAMP NULL,
    
    -- Dane rejestracji
    ip_address VARCHAR(45) NULL COMMENT 'IP z rejestracji',
    registration_country VARCHAR(50) NULL,
    registration_isp VARCHAR(100) NULL,
    
    -- Preferencje (JSON)
    preferences JSON NULL COMMENT 'Ustawienia użytkownika',
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indeksy
    INDEX idx_email (email),
    INDEX idx_phone (phone),
    INDEX idx_is_active (is_active),
    INDEX idx_created_at (created_at),
    INDEX idx_last_login (last_login)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- TABELA SESJI UŻYTKOWNIKÓW
-- ========================================
CREATE TABLE civilian_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    
    session_token VARCHAR(255) NOT NULL UNIQUE,
    
    ip_address VARCHAR(45),
    user_agent VARCHAR(500),
    device_type VARCHAR(50) COMMENT 'mobile/desktop/tablet',
    
    is_active BOOLEAN DEFAULT TRUE,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_session_token (session_token),
    INDEX idx_is_active (is_active),
    
    FOREIGN KEY (user_id) REFERENCES civilian_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- ========================================
-- PRZYKŁADOWE KONTA TESTOWE
-- ========================================
-- Hasło dla wszystkich: admin123
-- Hash: $2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam

INSERT INTO civilian_users (first_name, last_name, email, phone, password, is_active, is_verified, created_at) VALUES
('Jan', 'Kowalski', 'jan.kowalski@LS.com', '555-1001', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 1, 1, NOW()),
('Anna', 'Nowak', 'anna.nowak@LS.com', '555-1002', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 1, 1, NOW()),
('Piotr', 'Wiśniewski', 'piotr.wisniewski@LS.com', '555-1003', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 1, 1, NOW()),
('Maria', 'Zielińska', 'maria.zielinska@LS.com', '555-1004', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 1, 1, NOW()),
('Tomasz', 'Lewandowski', 'tomasz.lewandowski@LS.com', '555-1005', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 1, 1, NOW()),
('Katarzyna', 'Wójcik', 'katarzyna.wojcik@LS.com', '555-1006', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 1, 1, NOW()),
('Michał', 'Kamiński', 'michal.kaminski@LS.com', '555-1007', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 1, 1, NOW()),
('Agnieszka', 'Dąbrowska', 'agnieszka.dabrowska@LS.com', '555-1008', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 1, 1, NOW()),
('Robert', 'Szymański', 'robert.szymanski@LS.com', '555-1009', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 1, 1, NOW()),
('Ewa', 'Jankowska', 'ewa.jankowska@LS.com', '555-1010', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 1, 1, NOW()),
-- Testowe konto zbanowane
('Banned', 'User', 'banned.user@LS.com', '555-9999', '$2b$10$HQez60kLRvOGhKLhNZfskeVCnvUC5N9Kwp/EPWq.wOgJQtc5/aOam', 1, 1, NOW())
ON DUPLICATE KEY UPDATE email = email;

-- Ustaw jedno konto jako zbanowane
UPDATE civilian_users SET is_banned = 1, ban_reason = 'Naruszenie regulaminu - testowe konto', banned_at = NOW() WHERE email = 'banned.user@LS.com';

-- ========================================
-- ZAKOŃCZENIE
-- ========================================
SELECT 'Civilian users database setup completed!' AS message;
SELECT COUNT(*) AS users_count FROM civilian_users;
