CREATE TABLE IF NOT EXISTS teams (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    color VARCHAR(7) DEFAULT '#0d6efd',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    team_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    severity ENUM('info','low','medium','high','critical') DEFAULT 'info',
    event_type VARCHAR(50) DEFAULT 'general',
    occurred_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_id INT NOT NULL,
    author VARCHAR(100) NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS network_nodes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    label VARCHAR(255) NOT NULL,
    ip_address VARCHAR(45),
    node_type ENUM('host','server','router','firewall','switch','cloud','endpoint','other') DEFAULT 'host',
    status ENUM('online','offline','unknown','compromised','monitoring') DEFAULT 'unknown',
    group_name VARCHAR(100) DEFAULT 'default',
    notes VARCHAR(1000) DEFAULT '',
    pos_x FLOAT DEFAULT 0,
    pos_y FLOAT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS network_links (
    id INT AUTO_INCREMENT PRIMARY KEY,
    source_id INT NOT NULL,
    target_id INT NOT NULL,
    link_type ENUM('direct','vpn','wireless','monitored') DEFAULT 'direct',
    label VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (source_id) REFERENCES network_nodes(id) ON DELETE CASCADE,
    FOREIGN KEY (target_id) REFERENCES network_nodes(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS network_shapes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    label VARCHAR(255) NOT NULL DEFAULT '',
    shape_type ENUM('rectangle','ellipse','zone') DEFAULT 'rectangle',
    pos_x FLOAT DEFAULT 0,
    pos_y FLOAT DEFAULT 0,
    width FLOAT DEFAULT 200,
    height FLOAT DEFAULT 150,
    color VARCHAR(7) DEFAULT '#1e3a5f',
    border_color VARCHAR(7) DEFAULT '#3b82f6',
    opacity FLOAT DEFAULT 0.15,
    z_index INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    doc_type VARCHAR(50) NOT NULL,
    team_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    occurred_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS file_attachments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_id INT NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    stored_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(100),
    file_size INT,
    uploaded_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS event_tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_id INT NOT NULL,
    tag VARCHAR(50) NOT NULL,
    UNIQUE KEY unique_event_tag (event_id, tag),
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
);

INSERT IGNORE INTO teams (name, color) VALUES
    ('Blue Team', '#0d6efd'),
    ('Red Team', '#dc3545'),
    ('SOC', '#ffc107'),
    ('Threat Intel', '#198754');

-- Ensure the neptune user has all privileges on neptune database
GRANT ALL PRIVILEGES ON neptune.* TO 'neptune'@'%';
FLUSH PRIVILEGES;