CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS agents (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id text NOT NULL,
    hostname text NOT NULL,
    api_key_hash char(64) NOT NULL,
    enabled boolean NOT NULL DEFAULT true,
    last_ip text NOT NULL DEFAULT '',
    first_seen timestamptz NOT NULL DEFAULT now(),
    last_seen timestamptz NOT NULL DEFAULT now(),
    UNIQUE (tenant_id, hostname)
);
CREATE INDEX IF NOT EXISTS agents_last_seen_idx ON agents(tenant_id,last_seen DESC);

CREATE TABLE IF NOT EXISTS rule_sets (
    tenant_id text NOT NULL,
    id text NOT NULL,
    name text NOT NULL,
    description text NOT NULL DEFAULT '',
    version integer NOT NULL DEFAULT 1,
    enabled boolean NOT NULL DEFAULT true,
    source text NOT NULL DEFAULT 'builtin',
    locked boolean NOT NULL DEFAULT false,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (tenant_id,id)
);

CREATE TABLE IF NOT EXISTS detection_rules (
    tenant_id text NOT NULL,
    id text NOT NULL,
    rule_set_id text NOT NULL,
    title text NOT NULL,
    severity text NOT NULL,
    score double precision NOT NULL DEFAULT 0,
    enabled boolean NOT NULL DEFAULT true,
    source text NOT NULL DEFAULT 'builtin',
    definition jsonb NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (tenant_id,id),
    FOREIGN KEY (tenant_id,rule_set_id) REFERENCES rule_sets(tenant_id,id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS detection_rules_set_idx ON detection_rules(tenant_id,rule_set_id,enabled);
CREATE INDEX IF NOT EXISTS detection_rules_severity_idx ON detection_rules(tenant_id,severity,enabled);

CREATE TABLE IF NOT EXISTS detection_suppressions (
    id bigserial PRIMARY KEY,
    tenant_id text NOT NULL,
    rule_id text NOT NULL DEFAULT '',
    host_pattern text NOT NULL DEFAULT '',
    user_pattern text NOT NULL DEFAULT '',
    source_ip_pattern text NOT NULL DEFAULT '',
    reason text NOT NULL DEFAULT '',
    enabled boolean NOT NULL DEFAULT true,
    expires_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS detection_suppressions_active_idx ON detection_suppressions(tenant_id,enabled,expires_at);

CREATE TABLE IF NOT EXISTS detections (
    id bigserial PRIMARY KEY,
    tenant_id text NOT NULL,
    fingerprint char(64) NOT NULL,
    rule_id text NOT NULL DEFAULT '',
    rule_set_id text NOT NULL DEFAULT '',
    rule_name text NOT NULL,
    severity text NOT NULL,
    status text NOT NULL DEFAULT 'open',
    hostname text NOT NULL DEFAULT '',
    user_name text NOT NULL DEFAULT '',
    source_ip text NOT NULL DEFAULT '',
    workstation text NOT NULL DEFAULT '',
    event_code integer NOT NULL DEFAULT 0,
    score double precision NOT NULL DEFAULT 0,
    window_start timestamptz NOT NULL,
    window_end timestamptz NOT NULL,
    summary text NOT NULL,
    hit_count bigint NOT NULL DEFAULT 1,
    tags jsonb NOT NULL DEFAULT '[]'::jsonb,
    mitre jsonb NOT NULL DEFAULT '[]'::jsonb,
    first_seen timestamptz NOT NULL DEFAULT now(),
    last_seen timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (tenant_id, fingerprint)
);
ALTER TABLE detections ADD COLUMN IF NOT EXISTS rule_id text NOT NULL DEFAULT '';
ALTER TABLE detections ADD COLUMN IF NOT EXISTS rule_set_id text NOT NULL DEFAULT '';
ALTER TABLE detections ADD COLUMN IF NOT EXISTS tags jsonb NOT NULL DEFAULT '[]'::jsonb;
ALTER TABLE detections ADD COLUMN IF NOT EXISTS mitre jsonb NOT NULL DEFAULT '[]'::jsonb;
CREATE INDEX IF NOT EXISTS detections_open_idx ON detections(tenant_id,status,last_seen DESC);
CREATE INDEX IF NOT EXISTS detections_rule_idx ON detections(tenant_id,rule_name,last_seen DESC);
CREATE INDEX IF NOT EXISTS detections_rule_id_idx ON detections(tenant_id,rule_id,last_seen DESC);
