PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS provenance_artifacts (
    relative_path TEXT PRIMARY KEY,
    original_filename TEXT NOT NULL,
    media_type TEXT NOT NULL,
    byte_length INTEGER NOT NULL CHECK(byte_length >= 0),
    sha256 BLOB NOT NULL CHECK(length(sha256) = 32),
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS data_provenance_nodes (
    id BLOB PRIMARY KEY CHECK(length(id) = 20),
    data TEXT,
    data_artifact_path TEXT REFERENCES provenance_artifacts(relative_path) ON DELETE RESTRICT,
    source TEXT NOT NULL CHECK(length(trim(source)) BETWEEN 1 AND 200),
    source_created_at TEXT NOT NULL,
    CHECK((data IS NULL) != (data_artifact_path IS NULL))
);

CREATE TABLE IF NOT EXISTS provenance_artifact_links (
    provenance_id BLOB NOT NULL REFERENCES data_provenance_nodes(id) ON DELETE RESTRICT,
    artifact_path TEXT NOT NULL REFERENCES provenance_artifacts(relative_path) ON DELETE RESTRICT,
    role TEXT NOT NULL CHECK(length(trim(role)) BETWEEN 1 AND 100),
    position INTEGER NOT NULL CHECK(position >= 0),
    PRIMARY KEY(provenance_id, artifact_path),
    UNIQUE(provenance_id, position)
);

CREATE TABLE IF NOT EXISTS knowledge_nodes (
    id BLOB PRIMARY KEY CHECK(length(id) = 20),
    short_name TEXT NOT NULL CHECK(length(trim(short_name)) BETWEEN 4 AND 50),
    short_description TEXT NOT NULL CHECK(length(trim(short_description)) <= 200),
    long_description TEXT NOT NULL,
    history_head_id BLOB REFERENCES data_history_nodes(id) ON DELETE RESTRICT,
    owner_node_id BLOB REFERENCES knowledge_nodes(id) ON DELETE RESTRICT
        CHECK(owner_node_id IS NULL OR length(owner_node_id) = 20),
    last_modified_by TEXT NOT NULL CHECK(length(trim(last_modified_by)) BETWEEN 1 AND 200),
    last_modified_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS data_history_nodes (
    id BLOB PRIMARY KEY CHECK(length(id) = 20),
    knowledge_node_id BLOB NOT NULL REFERENCES knowledge_nodes(id) ON DELETE RESTRICT,
    previous_history_id BLOB REFERENCES data_history_nodes(id) ON DELETE RESTRICT,
    provenance_id BLOB NOT NULL REFERENCES data_provenance_nodes(id) ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS fixed_connections (
    source_node_id BLOB NOT NULL REFERENCES knowledge_nodes(id) ON DELETE RESTRICT,
    target_node_id BLOB NOT NULL REFERENCES knowledge_nodes(id) ON DELETE RESTRICT,
    position INTEGER NOT NULL CHECK(position >= 0),
    PRIMARY KEY(source_node_id, target_node_id),
    UNIQUE(source_node_id, position)
);

CREATE TABLE IF NOT EXISTS recent_connections (
    source_node_id BLOB NOT NULL REFERENCES knowledge_nodes(id) ON DELETE RESTRICT,
    target_node_id BLOB NOT NULL REFERENCES knowledge_nodes(id) ON DELETE RESTRICT,
    position INTEGER NOT NULL CHECK(position >= 0),
    PRIMARY KEY(source_node_id, target_node_id),
    UNIQUE(source_node_id, position)
);

CREATE TABLE IF NOT EXISTS idempotency_receipts (
    id BLOB PRIMARY KEY CHECK(length(id) = 16),
    operation_kind TEXT NOT NULL CHECK(operation_kind IN ('create_provenance', 'create_node', 'update_node')),
    request_hash BLOB NOT NULL CHECK(length(request_hash) = 32),
    result_id BLOB NOT NULL CHECK(length(result_id) = 20),
    committed_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS knowledge_nodes_by_owner
ON knowledge_nodes(owner_node_id);

CREATE INDEX IF NOT EXISTS fixed_connections_by_position
ON fixed_connections(source_node_id, position);

CREATE INDEX IF NOT EXISTS recent_connections_by_position
ON recent_connections(source_node_id, position);
