-- ClickHouse Schema for PhishProtect Telemetry Backend
-- Optimized for millions of devices with low cost and latency
-- Uses ReplicatedMergeTree for high availability

-- =============================================================================
-- DATABASE SETUP
-- =============================================================================

CREATE DATABASE IF NOT EXISTS phishprotect;

USE phishprotect;

-- =============================================================================
-- DETECTION EVENTS TABLE
-- Stores all threat detection events with aggregated data
-- =============================================================================

CREATE TABLE IF NOT EXISTS detection_events
(
    -- Time partitioning key (hourly aggregation for privacy)
    event_hour DateTime CODEC(DoubleDelta, LZ4),

    -- Device identification (hashed for privacy)
    device_id String CODEC(ZSTD(1)),
    organization_id String CODEC(ZSTD(1)),

    -- Detection metadata
    risk_score UInt8,
    risk_level Enum8('low' = 1, 'medium' = 2, 'high' = 3, 'critical' = 4),
    finding_count UInt16,
    matched Bool,

    -- Detectors that triggered
    detectors Array(String) CODEC(ZSTD(1)),

    -- Attack categories
    categories Array(String) CODEC(ZSTD(1)),

    -- MITRE ATT&CK techniques
    techniques Array(String) CODEC(ZSTD(1)),

    -- Severities distribution
    severity_critical UInt16 DEFAULT 0,
    severity_high UInt16 DEFAULT 0,
    severity_medium UInt16 DEFAULT 0,
    severity_low UInt16 DEFAULT 0,

    -- Session context (hashed for privacy)
    session_id String CODEC(ZSTD(1)),

    -- Insertion timestamp
    inserted_at DateTime DEFAULT now() CODEC(DoubleDelta, LZ4)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/detection_events', '{replica}')
PARTITION BY toYYYYMM(event_hour)
ORDER BY (organization_id, device_id, event_hour)
TTL event_hour + INTERVAL 90 DAY  -- 90 day retention
SETTINGS index_granularity = 8192;

-- =============================================================================
-- POLICY DECISIONS TABLE
-- Stores policy evaluation results
-- =============================================================================

CREATE TABLE IF NOT EXISTS policy_decisions
(
    event_hour DateTime CODEC(DoubleDelta, LZ4),
    device_id String CODEC(ZSTD(1)),
    organization_id String CODEC(ZSTD(1)),

    -- Policy information
    policy_id String CODEC(ZSTD(1)),
    action Enum8('allow' = 1, 'report' = 2, 'warn' = 3, 'quarantine' = 4, 'block' = 5),

    -- Risk context
    risk_score UInt8,
    risk_level Enum8('low' = 1, 'medium' = 2, 'high' = 3, 'critical' = 4),

    -- Enforcement
    enforcement_type Nullable(String) CODEC(ZSTD(1)),
    matched_rule_count UInt16,

    -- User override tracking
    user_override Bool DEFAULT false,

    -- Session context
    session_id String CODEC(ZSTD(1)),

    inserted_at DateTime DEFAULT now() CODEC(DoubleDelta, LZ4)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/policy_decisions', '{replica}')
PARTITION BY toYYYYMM(event_hour)
ORDER BY (organization_id, action, event_hour)
TTL event_hour + INTERVAL 90 DAY
SETTINGS index_granularity = 8192;

-- =============================================================================
-- DETECTOR PERFORMANCE TABLE
-- Tracks detector execution performance metrics
-- =============================================================================

CREATE TABLE IF NOT EXISTS detector_performance
(
    event_hour DateTime CODEC(DoubleDelta, LZ4),
    device_id String CODEC(ZSTD(1)),
    organization_id String CODEC(ZSTD(1)),

    -- Detector identification
    detector_name String CODEC(ZSTD(1)),

    -- Performance metrics
    latency_ms UInt32,
    matched Bool,

    -- Aggregation fields (pre-aggregated per hour)
    execution_count UInt32 DEFAULT 1,
    hit_count UInt32 DEFAULT 0,

    inserted_at DateTime DEFAULT now() CODEC(DoubleDelta, LZ4)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/detector_performance', '{replica}')
PARTITION BY toYYYYMM(event_hour)
ORDER BY (organization_id, detector_name, event_hour)
TTL event_hour + INTERVAL 90 DAY
SETTINGS index_granularity = 8192;

-- =============================================================================
-- AUDIT LOGS TABLE
-- Compliance and forensic audit trail
-- =============================================================================

CREATE TABLE IF NOT EXISTS audit_logs
(
    event_time DateTime CODEC(DoubleDelta, LZ4),
    device_id String CODEC(ZSTD(1)),
    organization_id String CODEC(ZSTD(1)),

    -- Log entry metadata
    log_id String CODEC(ZSTD(1)),
    log_type Enum8('policy_decision' = 1, 'policy_change' = 2, 'enforcement' = 3, 'detector_performance' = 4),

    -- Action taken
    action Nullable(String) CODEC(ZSTD(1)),

    -- Risk information
    risk_score UInt8 DEFAULT 0,
    detector_count UInt16 DEFAULT 0,

    -- Policy context
    policy_id Nullable(String) CODEC(ZSTD(1)),
    matched_rules Array(String) CODEC(ZSTD(1)),

    -- Categories and techniques
    categories Array(String) CODEC(ZSTD(1)),
    techniques Array(String) CODEC(ZSTD(1)),

    -- Session context (hashed)
    session_id Nullable(String) CODEC(ZSTD(1)),

    inserted_at DateTime DEFAULT now() CODEC(DoubleDelta, LZ4)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/audit_logs', '{replica}')
PARTITION BY toYYYYMM(event_time)
ORDER BY (organization_id, log_type, event_time)
TTL event_time + INTERVAL 365 DAY  -- 1 year retention for compliance
SETTINGS index_granularity = 8192;

-- =============================================================================
-- DEVICE REGISTRY TABLE
-- Tracks registered devices
-- =============================================================================

CREATE TABLE IF NOT EXISTS devices
(
    device_id String CODEC(ZSTD(1)),
    organization_id String CODEC(ZSTD(1)),

    -- Device information
    extension_version String CODEC(ZSTD(1)),
    browser_name String CODEC(ZSTD(1)),
    browser_version String CODEC(ZSTD(1)),

    -- MDM attributes (if available)
    serial_number Nullable(String) CODEC(ZSTD(1)),
    asset_id Nullable(String) CODEC(ZSTD(1)),
    annotated_location Nullable(String) CODEC(ZSTD(1)),
    hostname Nullable(String) CODEC(ZSTD(1)),

    -- Enrollment status
    enrollment_status Enum8('unmanaged' = 1, 'managed' = 2, 'unknown' = 3),

    -- Timestamps
    registered_at DateTime CODEC(DoubleDelta, LZ4),
    last_seen DateTime CODEC(DoubleDelta, LZ4),

    inserted_at DateTime DEFAULT now() CODEC(DoubleDelta, LZ4),
    updated_at DateTime DEFAULT now() CODEC(DoubleDelta, LZ4)
)
ENGINE = ReplicatedReplacingMergeTree(updated_at)
PARTITION BY toYYYYMM(registered_at)
ORDER BY (organization_id, device_id)
SETTINGS index_granularity = 8192;

-- =============================================================================
-- MATERIALIZED VIEWS FOR REAL-TIME AGGREGATION
-- =============================================================================

-- Hourly detection statistics per organization
CREATE MATERIALIZED VIEW IF NOT EXISTS detection_stats_hourly
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(event_hour)
ORDER BY (organization_id, event_hour, risk_level)
AS SELECT
    toStartOfHour(event_hour) AS event_hour,
    organization_id,
    risk_level,
    count() AS detection_count,
    sum(finding_count) AS total_findings,
    countIf(matched) AS matched_count,
    uniqExact(device_id) AS unique_devices
FROM detection_events
GROUP BY event_hour, organization_id, risk_level;

-- Detector performance aggregation
CREATE MATERIALIZED VIEW IF NOT EXISTS detector_stats_hourly
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(event_hour)
ORDER BY (organization_id, detector_name, event_hour)
AS SELECT
    toStartOfHour(event_hour) AS event_hour,
    organization_id,
    detector_name,
    count() AS execution_count,
    avgState(latency_ms) AS avg_latency,
    quantilesState(0.5, 0.95, 0.99)(latency_ms) AS latency_percentiles,
    countIf(matched) AS hit_count,
    uniqExact(device_id) AS unique_devices
FROM detector_performance
GROUP BY event_hour, organization_id, detector_name;

-- Policy action distribution
CREATE MATERIALIZED VIEW IF NOT EXISTS policy_action_stats_hourly
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(event_hour)
ORDER BY (organization_id, event_hour, action)
AS SELECT
    toStartOfHour(event_hour) AS event_hour,
    organization_id,
    action,
    count() AS action_count,
    avgIf(risk_score, risk_score > 0) AS avg_risk_score,
    countIf(user_override) AS override_count,
    uniqExact(device_id) AS unique_devices
FROM policy_decisions
GROUP BY event_hour, organization_id, action;

-- =============================================================================
-- USEFUL QUERY EXAMPLES
-- =============================================================================

-- Top 10 most triggered detectors in last 24 hours
-- SELECT
--     detector_name,
--     sum(execution_count) AS total_executions,
--     sum(hit_count) AS total_hits,
--     round(sum(hit_count) / sum(execution_count) * 100, 2) AS hit_rate_percent,
--     round(avg(avg_latency), 2) AS avg_latency_ms
-- FROM detector_stats_hourly
-- WHERE event_hour >= now() - INTERVAL 24 HOUR
--     AND organization_id = 'your_org_id'
-- GROUP BY detector_name
-- ORDER BY total_hits DESC
-- LIMIT 10;

-- Risk distribution over last 7 days
-- SELECT
--     toDate(event_hour) AS date,
--     risk_level,
--     sum(detection_count) AS detections
-- FROM detection_stats_hourly
-- WHERE event_hour >= now() - INTERVAL 7 DAY
--     AND organization_id = 'your_org_id'
-- GROUP BY date, risk_level
-- ORDER BY date DESC, risk_level;

-- Policy action trends
-- SELECT
--     toDate(event_hour) AS date,
--     action,
--     sum(action_count) AS count
-- FROM policy_action_stats_hourly
-- WHERE event_hour >= now() - INTERVAL 30 DAY
--     AND organization_id = 'your_org_id'
-- GROUP BY date, action
-- ORDER BY date DESC;

-- Device activity (last seen)
-- SELECT
--     device_id,
--     extension_version,
--     browser_name,
--     enrollment_status,
--     last_seen,
--     dateDiff('hour', last_seen, now()) AS hours_since_last_seen
-- FROM devices
-- WHERE organization_id = 'your_org_id'
-- ORDER BY last_seen DESC
-- LIMIT 100;

-- =============================================================================
-- PERFORMANCE OPTIMIZATION NOTES
-- =============================================================================

-- 1. Partitioning by month reduces query scan size
-- 2. TTL automatically removes old data (90 days for events, 365 for audit)
-- 3. ZSTD(1) compression provides 5-10x compression ratio
-- 4. DoubleDelta codec optimized for timestamp columns
-- 5. Materialized views provide pre-aggregated data for dashboards
-- 6. ReplicatedMergeTree ensures high availability
-- 7. Order by (organization_id, ...) enables org-level query optimization
-- 8. Array columns allow efficient storage of detector/category lists
-- 9. Enum columns save space vs strings for fixed values
-- 10. index_granularity=8192 balances query performance and storage
