-- Database initialization script

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- CVEs table
CREATE TABLE cves (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    cve_id VARCHAR(20) UNIQUE NOT NULL,
    description TEXT,
    cvss_score DECIMAL(3,1),
    severity VARCHAR(20),
    published_date TIMESTAMP,
    modified_date TIMESTAMP,
    affected_products TEXT[],
    reference_urls TEXT[],
    -- Bulk processing fields
    data_source VARCHAR(20) DEFAULT 'nvd_api',
    nvd_json_version VARCHAR(10) DEFAULT '2.0',
    bulk_processed BOOLEAN DEFAULT FALSE,
    -- nomi-sec PoC fields
    poc_count INTEGER DEFAULT 0,
    poc_data JSON,
    -- Reference data fields
    reference_data JSON,
    reference_sync_status VARCHAR(20) DEFAULT 'pending',
    reference_last_synced TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- SIGMA rules table
CREATE TABLE sigma_rules (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    cve_id VARCHAR(20) REFERENCES cves(cve_id),
    rule_name VARCHAR(255) NOT NULL,
    rule_content TEXT NOT NULL,
    detection_type VARCHAR(50),
    log_source VARCHAR(100),
    confidence_level VARCHAR(20),
    auto_generated BOOLEAN DEFAULT TRUE,
    exploit_based BOOLEAN DEFAULT FALSE,
    github_repos TEXT[],
    exploit_indicators TEXT,
    -- Enhanced fields for new data sources
    poc_source VARCHAR(20) DEFAULT 'github_search',
    poc_quality_score INTEGER DEFAULT 0,
    nomi_sec_data JSON,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Rule templates table
CREATE TABLE rule_templates (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    template_name VARCHAR(255) NOT NULL,
    template_content TEXT NOT NULL,
    applicable_product_patterns TEXT[],
    description TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Bulk processing jobs table
CREATE TABLE bulk_processing_jobs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    job_type VARCHAR(50) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    year INTEGER,
    total_items INTEGER DEFAULT 0,
    processed_items INTEGER DEFAULT 0,
    failed_items INTEGER DEFAULT 0,
    error_message TEXT,
    job_metadata JSON,
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    cancelled_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Insert some basic rule templates
INSERT INTO rule_templates (template_name, template_content, applicable_product_patterns, description) VALUES 
(
    'Windows Process Execution',
    'title: {title}
description: {description}
id: {rule_id}
status: experimental
author: CVE-SIGMA Auto Generator
date: {date}
references:
    - {cve_url}
tags:
    - {tags}
logsource:
    category: process_creation
    product: windows
detection:
    selection:
        Image|contains: {suspicious_processes}
    condition: selection
falsepositives:
    - Legitimate use of the software
level: {level}',
    ARRAY['windows', 'microsoft'],
    'Template for Windows process execution detection'
),
(
    'Network Connection',
    'title: {title}
description: {description}
id: {rule_id}
status: experimental
author: CVE-SIGMA Auto Generator
date: {date}
references:
    - {cve_url}
tags:
    - {tags}
logsource:
    category: network_connection
    product: windows
detection:
    selection:
        Initiated: true
        DestinationPort: {suspicious_ports}
    condition: selection
falsepositives:
    - Legitimate network connections
level: {level}',
    ARRAY['network', 'connection', 'remote'],
    'Template for network connection detection'
),
(
    'File Modification',
    'title: {title}
description: {description}
id: {rule_id}
status: experimental
author: CVE-SIGMA Auto Generator
date: {date}
references:
    - {cve_url}
tags:
    - {tags}
logsource:
    category: file_event
    product: windows
detection:
    selection:
        EventType: creation
        TargetFilename|contains: {file_patterns}
    condition: selection
falsepositives:
    - Legitimate file operations
level: {level}',
    ARRAY['file', 'filesystem', 'modification'],
    'Template for file modification detection'
),
(
    'PowerShell Execution',
    'title: {title}
description: {description}
id: {rule_id}
status: experimental
author: CVE-SIGMA Auto Generator
date: {date}
references:
    - {cve_url}
tags:
    - {tags}
logsource:
    product: windows
    category: ps_script
detection:
    selection:
        ScriptBlockText|contains: {suspicious_processes}
    condition: selection
falsepositives:
    - Legitimate PowerShell scripts
level: {level}',
    ARRAY['powershell', 'script', 'ps1'],
    'Template for PowerShell script execution detection'
);

-- Create indexes
CREATE INDEX idx_cves_cve_id ON cves(cve_id);
CREATE INDEX idx_cves_published_date ON cves(published_date);
CREATE INDEX idx_cves_severity ON cves(severity);
CREATE INDEX idx_cves_reference_sync_status ON cves(reference_sync_status);
CREATE INDEX idx_cves_reference_last_synced ON cves(reference_last_synced);
CREATE INDEX idx_sigma_rules_cve_id ON sigma_rules(cve_id);
CREATE INDEX idx_sigma_rules_detection_type ON sigma_rules(detection_type);