-- Migration: Phase 6 Infrastructure Hardening Tables
-- Date: 2026-05-16
-- Systems: Dead Man Switch, Merkle Anchor, Canary Tokens,
--          Email Deception, OSINT Cache, Bloom Trust

-- Merkle tree anchors for audit log integrity verification
CREATE TABLE IF NOT EXISTS merkle_anchors (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merkle_root    CHAR(64)     NOT NULL COMMENT 'SHA-256 root hash',
    first_event_id INT UNSIGNED NOT NULL,
    last_event_id  INT UNSIGNED NOT NULL,
    leaf_count     INT UNSIGNED NOT NULL DEFAULT 0,
    tx_id          VARCHAR(100) DEFAULT NULL COMMENT 'Bitcoin TX ID (OpenTimestamps)',
    block_height   INT UNSIGNED DEFAULT NULL,
    created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_event_range (first_event_id, last_event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Deception email tracking tokens
CREATE TABLE IF NOT EXISTS deception_tokens (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    token          CHAR(64)     NOT NULL UNIQUE,
    target_ip      VARCHAR(45)  NOT NULL,
    deception_type VARCHAR(50)  NOT NULL DEFAULT 'breach_notice',
    status         ENUM('active','triggered','expired') NOT NULL DEFAULT 'active',
    created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    triggered_at   DATETIME     DEFAULT NULL,
    INDEX idx_status (status),
    INDEX idx_ip (target_ip)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- OSINT enrichment cache
CREATE TABLE IF NOT EXISTS threat_intel_cache (
    ip_address VARCHAR(45) NOT NULL PRIMARY KEY,
    data       TEXT        NOT NULL COMMENT 'JSON enrichment payload',
    updated_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_updated (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
