-- Schemat bazy danych dla systemu testów phishingowych
-- Uruchom w MySQL/MariaDB

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

-- Kampanie testowe
CREATE TABLE campaigns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    status ENUM('draft', 'active', 'completed') DEFAULT 'draft',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    started_at DATETIME NULL,
    completed_at DATETIME NULL
) ENGINE=InnoDB;

-- Cele (pracownicy)
CREATE TABLE targets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    department VARCHAR(100),
    scenario VARCHAR(50) NOT NULL,
    token CHAR(64) NOT NULL UNIQUE,
    email_sent TINYINT(1) DEFAULT 0,
    email_sent_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    INDEX idx_token (token),
    INDEX idx_scenario (scenario)
) ENGINE=InnoDB;

-- Zdarzenia (otwarcia, kliknięcia, logowania)
CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    target_id INT NOT NULL,
    event_type ENUM('email_opened', 'link_clicked', 'attachment_downloaded', 'credentials_submitted') NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    extra_data JSON NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (target_id) REFERENCES targets(id) ON DELETE CASCADE,
    INDEX idx_target_event (target_id, event_type)
) ENGINE=InnoDB;
