-- Phase 2A mothereve.ritter.my test-only schema.
-- Use cPanel/MySQL credentials supplied manually from config/config.template.php.

CREATE TABLE IF NOT EXISTS me_node_ids (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    node_id VARCHAR(128) NOT NULL UNIQUE,
    cid VARCHAR(128) NOT NULL,
    node_status VARCHAR(32) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS me_installation_certificates (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    certificate_id VARCHAR(96) NOT NULL UNIQUE,
    node_id VARCHAR(128) NOT NULL,
    cid VARCHAR(128) NOT NULL,
    assigned_node_id VARCHAR(128) NOT NULL,
    certificate_payload JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS me_local_licence_certificates (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    certificate_id VARCHAR(96) NOT NULL UNIQUE,
    node_id VARCHAR(128) NOT NULL,
    eve_user_id VARCHAR(128) NULL,
    status VARCHAR(32) NOT NULL,
    certificate_payload JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS me_handshake_records (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    request_id VARCHAR(96) NOT NULL,
    node_id VARCHAR(128) NULL,
    cid VARCHAR(128) NULL,
    handshake_type VARCHAR(64) NOT NULL,
    handshake_payload JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS me_audit_intake_records (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    request_id VARCHAR(96) NOT NULL,
    event_name VARCHAR(128) NOT NULL,
    event_payload JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);