PRAGMA foreign_keys = ON;

-- =========================================================
-- node_info
-- One row per RobinNet node instance
-- =========================================================
CREATE TABLE IF NOT EXISTS node_info (
    id INTEGER PRIMARY KEY CHECK (id = 1),

    node_uuid TEXT NOT NULL UNIQUE,
    node_name TEXT NOT NULL UNIQUE,

    operator_name TEXT NOT NULL DEFAULT '',
    operator_callsign TEXT NOT NULL DEFAULT '',
    location_text TEXT NOT NULL DEFAULT '',

    transport_profile TEXT NOT NULL DEFAULT 'lan',

    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

-- =========================================================
-- peers
-- Known neighboring RobinNet nodes
-- =========================================================
CREATE TABLE IF NOT EXISTS peers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,

    peer_uuid TEXT,
    peer_name TEXT NOT NULL,
    base_url TEXT NOT NULL,
    transport TEXT NOT NULL DEFAULT 'lan',

    enabled INTEGER NOT NULL DEFAULT 1 CHECK (enabled IN (0,1)),

    last_seen_at TEXT,
    last_sync_at TEXT,

    notes TEXT NOT NULL DEFAULT '',

    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,

    UNIQUE(base_url)
);

CREATE INDEX IF NOT EXISTS idx_peers_enabled
    ON peers(enabled);

CREATE INDEX IF NOT EXISTS idx_peers_name
    ON peers(peer_name);

-- =========================================================
-- messages
-- Core store-and-forward message table
-- =========================================================
CREATE TABLE IF NOT EXISTS messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,

    msg_uuid TEXT NOT NULL UNIQUE,
    msg_hash TEXT NOT NULL,

    msg_type TEXT NOT NULL,         -- bulletin, direct, status
    scope TEXT NOT NULL DEFAULT 'network', -- local, regional, network

    origin_node_uuid TEXT NOT NULL,
    origin_node_name TEXT NOT NULL,
    origin_time TEXT NOT NULL,

    author TEXT NOT NULL DEFAULT '',
    author_callsign TEXT NOT NULL DEFAULT '',

    destination TEXT NOT NULL DEFAULT '',   -- freeform for v0.1
    title TEXT NOT NULL,
    body TEXT NOT NULL,

    created_at TEXT NOT NULL,
    expires_at TEXT,

    priority INTEGER NOT NULL DEFAULT 2 CHECK (priority BETWEEN 0 AND 9),

    status TEXT NOT NULL DEFAULT 'active',  -- active, expired, cancelled

    is_local_only INTEGER NOT NULL DEFAULT 0 CHECK (is_local_only IN (0,1)),
    is_rf_eligible INTEGER NOT NULL DEFAULT 0 CHECK (is_rf_eligible IN (0,1)),

    imported_from_peer_uuid TEXT,
    imported_from_peer_name TEXT,

    first_seen_at TEXT NOT NULL,
    last_seen_at TEXT NOT NULL,

    CHECK (length(trim(title)) > 0),
    CHECK (length(msg_uuid) > 0),
    CHECK (length(msg_hash) > 0)
);

CREATE INDEX IF NOT EXISTS idx_messages_type
    ON messages(msg_type);

CREATE INDEX IF NOT EXISTS idx_messages_scope
    ON messages(scope);

CREATE INDEX IF NOT EXISTS idx_messages_created_at
    ON messages(created_at DESC);

CREATE INDEX IF NOT EXISTS idx_messages_origin_node_uuid
    ON messages(origin_node_uuid);

CREATE INDEX IF NOT EXISTS idx_messages_status
    ON messages(status);

CREATE INDEX IF NOT EXISTS idx_messages_expires_at
    ON messages(expires_at);

-- Helpful when checking integrity collisions or reimports
CREATE INDEX IF NOT EXISTS idx_messages_hash
    ON messages(msg_hash);

-- =========================================================
-- message_trace
-- Per-message trail of where a message was seen/imported
-- Useful for troubleshooting and future RF accountability
-- =========================================================
CREATE TABLE IF NOT EXISTS message_trace (
    id INTEGER PRIMARY KEY AUTOINCREMENT,

    msg_uuid TEXT NOT NULL,
    event_type TEXT NOT NULL,   -- created, imported, exported, synced, rejected

    event_time TEXT NOT NULL,

    local_node_uuid TEXT NOT NULL,
    local_node_name TEXT NOT NULL,

    peer_uuid TEXT,
    peer_name TEXT,

    detail TEXT NOT NULL DEFAULT '',

    FOREIGN KEY (msg_uuid) REFERENCES messages(msg_uuid) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_message_trace_msg_uuid
    ON message_trace(msg_uuid);

CREATE INDEX IF NOT EXISTS idx_message_trace_event_time
    ON message_trace(event_time DESC);

-- =========================================================
-- sync_state
-- Tracks per-peer sync knowledge so you can avoid repeating work
-- =========================================================
CREATE TABLE IF NOT EXISTS sync_state (
    id INTEGER PRIMARY KEY AUTOINCREMENT,

    peer_id INTEGER NOT NULL,
    msg_uuid TEXT NOT NULL,

    delivery_state TEXT NOT NULL DEFAULT 'pending', -- pending, sent, failed, skipped
    attempt_count INTEGER NOT NULL DEFAULT 0,

    first_attempt_at TEXT,
    last_attempt_at TEXT,
    delivered_at TEXT,

    last_error TEXT NOT NULL DEFAULT '',

    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,

    FOREIGN KEY (peer_id) REFERENCES peers(id) ON DELETE CASCADE,
    FOREIGN KEY (msg_uuid) REFERENCES messages(msg_uuid) ON DELETE CASCADE,

    UNIQUE(peer_id, msg_uuid)
);

CREATE INDEX IF NOT EXISTS idx_sync_state_peer_id
    ON sync_state(peer_id);

CREATE INDEX IF NOT EXISTS idx_sync_state_msg_uuid
    ON sync_state(msg_uuid);

CREATE INDEX IF NOT EXISTS idx_sync_state_delivery_state
    ON sync_state(delivery_state);

-- =========================================================
-- local_settings
-- Small key/value table for future expansion
-- =========================================================
CREATE TABLE IF NOT EXISTS local_settings (
    setting_key TEXT PRIMARY KEY,
    setting_value TEXT NOT NULL,
    updated_at TEXT NOT NULL
);