CREATE DATABASE IF NOT EXISTS engine CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE engine;
CREATE TABLE IF NOT EXISTS artifacts (
id              BIGINT AUTO_INCREMENT PRIMARY KEY,
signal_type     VARCHAR(32) NOT NULL,
payload         JSON NOT NULL,
hmac            VARCHAR(64) NOT NULL,
nonce           VARCHAR(64) NOT NULL,
source_node     VARCHAR(6) NOT NULL,
status          ENUM('pending','processing','completed','failed') DEFAULT 'pending',
created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
processed_at    TIMESTAMP NULL,
result          JSON NULL,
INDEX idx_signal_type (signal_type),
INDEX idx_source_node (source_node),
INDEX idx_status (status),
INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS payments (
id                      BIGINT AUTO_INCREMENT PRIMARY KEY,
artifact_id             BIGINT NOT NULL,
stripe_payment_intent_id VARCHAR(64) NULL,
amount                  DECIMAL(10,2) NOT NULL,
currency                CHAR(3) NOT NULL DEFAULT 'CAD',
status                  ENUM('pending','processing','completed','failed','refunded','cancelled') DEFAULT 'pending',
card_fingerprint        VARCHAR(64) NULL,
fraud_score             DECIMAL(3,2) DEFAULT 0.00,
vault_blob              TEXT NULL,
email_hash              VARCHAR(64) NULL,
customer_hash           VARCHAR(64) NULL,
created_at              TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
completed_at            TIMESTAMP NULL,
INDEX idx_status (status),
INDEX idx_stripe_id (stripe_payment_intent_id),
INDEX idx_customer_hash (customer_hash),
CONSTRAINT fk_payments_artifact FOREIGN KEY (artifact_id) REFERENCES artifacts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS payment_methods (
id          BIGINT AUTO_INCREMENT PRIMARY KEY,
payment_id  BIGINT NOT NULL,
method      ENUM('stripe','xmr','usdt-trc','usdc-poly','etransfer') NOT NULL,
method_data JSON NULL,
status      ENUM('active','expired','revoked') DEFAULT 'active',
CONSTRAINT fk_pm_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS email_queue (
id              BIGINT AUTO_INCREMENT PRIMARY KEY,
artifact_id     BIGINT NOT NULL,
email_hash      VARCHAR(64) NOT NULL,
subject         VARCHAR(500) NOT NULL,
body            TEXT NOT NULL,
dkim_selector   VARCHAR(50) DEFAULT 'default',
dkim_domain     VARCHAR(100) NOT NULL,
status          ENUM('queued','signed','sent','failed','bounced') DEFAULT 'queued',
canary_id       VARCHAR(32) NULL,
sent_at         TIMESTAMP NULL,
created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_status (status),
INDEX idx_created_at (created_at),
CONSTRAINT fk_email_artifact FOREIGN KEY (artifact_id) REFERENCES artifacts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS fulfillment (
id                  BIGINT AUTO_INCREMENT PRIMARY KEY,
payment_id          BIGINT NOT NULL,
order_ref           VARCHAR(32) NOT NULL UNIQUE,
status              ENUM('pending','processing','shipped','delivered','cancelled') DEFAULT 'pending',
shipping_method     ENUM('canada_post_express','canada_post_xpresspost','canada_post_regular') NOT NULL,
tracking_number     VARCHAR(64) NULL,
canada_post_api_response JSON NULL,
shipping_address    JSON NOT NULL,
estimated_delivery  DATE NULL,
shipped_at          TIMESTAMP NULL,
delivered_at        TIMESTAMP NULL,
created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_order_ref (order_ref),
INDEX idx_tracking (tracking_number),
INDEX idx_status (status),
CONSTRAINT fk_fulfillment_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS canada_post_tracking (
id              BIGINT AUTO_INCREMENT PRIMARY KEY,
fulfillment_id  BIGINT NOT NULL,
tracking_number VARCHAR(64) NOT NULL,
event_code      VARCHAR(20) NOT NULL,
event_description VARCHAR(200) NOT NULL,
location        VARCHAR(200) NULL,
event_timestamp TIMESTAMP NOT NULL,
created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_tracking_number (tracking_number),
INDEX idx_event_timestamp (event_timestamp),
CONSTRAINT fk_cpt_fulfillment FOREIGN KEY (fulfillment_id) REFERENCES fulfillment(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS sessions (
id                      VARCHAR(64) PRIMARY KEY,
node_id                 VARCHAR(6) NOT NULL,
ip                      VARCHAR(45) NOT NULL,
user_agent_hash         VARCHAR(64) NULL,
trust_score             DECIMAL(3,2) DEFAULT 0.10,
email_verified          BOOLEAN DEFAULT FALSE,
purchase_count          INT DEFAULT 0,
failed_payment_count    INT DEFAULT 0,
suspicious_activity_count INT DEFAULT 0,
max_lifetime            TIMESTAMP NOT NULL,
created_at              TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_activity           TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_trust_score (trust_score),
INDEX idx_ip (ip),
INDEX idx_max_lifetime (max_lifetime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS strikes (
id              BIGINT AUTO_INCREMENT PRIMARY KEY,
ip              VARCHAR(45) NOT NULL,
fingerprint     VARCHAR(64) NULL,
count           INT DEFAULT 1,
action          ENUM('log_popup','blackhole_7d','permanent_blackhole') DEFAULT 'log_popup',
expires_at      TIMESTAMP NOT NULL,
created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY unique_ip_fingerprint (ip, fingerprint),
INDEX idx_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS ip_reputation (
id              BIGINT AUTO_INCREMENT PRIMARY KEY,
ip              VARCHAR(45) NOT NULL,
reputation_score DECIMAL(3,2) DEFAULT 0.50,
first_seen      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_seen       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
request_count   INT DEFAULT 0,
blocked_count   INT DEFAULT 0,
UNIQUE KEY unique_ip (ip)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS node_health (
id              BIGINT AUTO_INCREMENT PRIMARY KEY,
node_id         VARCHAR(6) NOT NULL,
cpu_pct         DECIMAL(5,2) NULL,
mem_used_mb     INT NULL,
mem_total_mb    INT NULL,
disk_used_gb    INT NULL,
disk_total_gb   INT NULL,
network_rx_mb   BIGINT NULL,
network_tx_mb   BIGINT NULL,
wg_status       ENUM('up','down','degraded') DEFAULT 'up',
service_status  JSON NULL,
boot_time       TIMESTAMP NULL,
reported_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_node_id (node_id),
INDEX idx_reported_at (reported_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS logs (
id          BIGINT AUTO_INCREMENT,
node_id     VARCHAR(6) NOT NULL,
level       ENUM('debug','info','warn','error','critical') NOT NULL,
category    VARCHAR(32) NOT NULL,
message     TEXT NOT NULL,
metadata    JSON NULL,
created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id, created_at),
INDEX idx_node_id (node_id),
INDEX idx_level (level),
INDEX idx_category (category),
INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
PARTITION p202506 VALUES LESS THAN (UNIX_TIMESTAMP('2025-07-01 00:00:00')),
PARTITION p202507 VALUES LESS THAN (UNIX_TIMESTAMP('2025-08-01 00:00:00')),
PARTITION p202508 VALUES LESS THAN (UNIX_TIMESTAMP('2025-09-01 00:00:00')),
PARTITION p202509 VALUES LESS THAN (UNIX_TIMESTAMP('2025-10-01 00:00:00')),
PARTITION p202510 VALUES LESS THAN (UNIX_TIMESTAMP('2025-11-01 00:00:00')),
PARTITION p202511 VALUES LESS THAN (UNIX_TIMESTAMP('2025-12-01 00:00:00')),
PARTITION p202512 VALUES LESS THAN (UNIX_TIMESTAMP('2026-01-01 00:00:00')),
PARTITION p202601 VALUES LESS THAN (UNIX_TIMESTAMP('2026-02-01 00:00:00')),
PARTITION p202602 VALUES LESS THAN (UNIX_TIMESTAMP('2026-03-01 00:00:00')),
PARTITION p202603 VALUES LESS THAN (UNIX_TIMESTAMP('2026-04-01 00:00:00')),
PARTITION p202604 VALUES LESS THAN (UNIX_TIMESTAMP('2026-05-01 00:00:00')),
PARTITION p202605 VALUES LESS THAN (UNIX_TIMESTAMP('2026-06-01 00:00:00')),
PARTITION p202606 VALUES LESS THAN (UNIX_TIMESTAMP('2026-07-01 00:00:00')),
PARTITION p202607 VALUES LESS THAN (UNIX_TIMESTAMP('2026-08-01 00:00:00')),
PARTITION p202608 VALUES LESS THAN (UNIX_TIMESTAMP('2026-09-01 00:00:00')),
PARTITION pfuture   VALUES LESS THAN MAXVALUE
);
CREATE TABLE IF NOT EXISTS security_events (
id              BIGINT AUTO_INCREMENT PRIMARY KEY,
node_id         VARCHAR(6) NOT NULL,
event_type      ENUM('intrusion_attempt','anomaly','canary_trigger','key_rotation','wg_failover','blacklist','strike') NOT NULL,
severity        ENUM('low','medium','high','critical') NOT NULL,
source_ip       VARCHAR(45) NULL,
description     TEXT NOT NULL,
metadata        JSON NULL,
acknowledged    BOOLEAN DEFAULT FALSE,
created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_node_id (node_id),
INDEX idx_event_type (event_type),
INDEX idx_severity (severity),
INDEX idx_acknowledged (acknowledged)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS waf_rules (
id          BIGINT AUTO_INCREMENT PRIMARY KEY,
node_id     VARCHAR(6) NOT NULL,
rule_type   ENUM('block','rate_limit','challenge','log') NOT NULL,
pattern     VARCHAR(500) NOT NULL,
hits        INT DEFAULT 0,
active      BOOLEAN DEFAULT TRUE,
created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_node_id (node_id),
INDEX idx_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS ai_inferences (
id              BIGINT AUTO_INCREMENT PRIMARY KEY,
model           VARCHAR(50) DEFAULT 'mistral-7b',
prompt_hash     VARCHAR(64) NOT NULL,
response_hash   VARCHAR(64) NULL,
inference_type  ENUM('fraud_check','support_reply','anomaly_detect','category') NOT NULL,
latency_ms      INT NULL,
created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_type (inference_type),
INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS file_integrity (
id              BIGINT AUTO_INCREMENT PRIMARY KEY,
node_id         VARCHAR(6) NOT NULL,
file_path       VARCHAR(500) NOT NULL,
expected_hash   VARCHAR(64) NOT NULL,
actual_hash     VARCHAR(64) NULL,
hash_algo       ENUM('sha256','blake2b') DEFAULT 'blake2b',
status          ENUM('ok','changed','missing') DEFAULT 'ok',
checked_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY unique_node_file (node_id, file_path),
INDEX idx_status (status),
INDEX idx_checked_at (checked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;