-- Dependency DAG: feature-level nodes and edges within items.
-- Migration: 014_dag_nodes_edges
-- Date: 2026-02

BEGIN;

--------------------------------------------------------------------------------
-- DAG Nodes (feature-level nodes within an item's revision)
--------------------------------------------------------------------------------

CREATE TABLE dag_nodes (
    id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    item_id          UUID NOT NULL REFERENCES items(id) ON DELETE CASCADE,
    revision_number  INTEGER NOT NULL,
    node_key         TEXT NOT NULL,
    node_type        TEXT NOT NULL,
    properties_hash  TEXT,
    validation_state TEXT NOT NULL DEFAULT 'clean',
    validation_msg   TEXT,
    metadata         JSONB DEFAULT '{}',
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE(item_id, revision_number, node_key)
);

CREATE INDEX idx_dag_nodes_item ON dag_nodes(item_id);
CREATE INDEX idx_dag_nodes_item_rev ON dag_nodes(item_id, revision_number);
CREATE INDEX idx_dag_nodes_state ON dag_nodes(validation_state)
    WHERE validation_state != 'clean';
CREATE INDEX idx_dag_nodes_type ON dag_nodes(node_type);

--------------------------------------------------------------------------------
-- DAG Edges (dependencies between nodes within a single item)
-- Direction: source → target means "target depends on source"
--------------------------------------------------------------------------------

CREATE TABLE dag_edges (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    source_node_id  UUID NOT NULL REFERENCES dag_nodes(id) ON DELETE CASCADE,
    target_node_id  UUID NOT NULL REFERENCES dag_nodes(id) ON DELETE CASCADE,
    edge_type       TEXT NOT NULL DEFAULT 'depends_on',
    metadata        JSONB DEFAULT '{}',
    UNIQUE(source_node_id, target_node_id, edge_type),
    CONSTRAINT no_self_edge CHECK (source_node_id != target_node_id)
);

CREATE INDEX idx_dag_edges_source ON dag_edges(source_node_id);
CREATE INDEX idx_dag_edges_target ON dag_edges(target_node_id);

--------------------------------------------------------------------------------
-- Cross-item DAG edges (linking feature nodes across BOM boundaries)
--------------------------------------------------------------------------------

CREATE TABLE dag_cross_edges (
    id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    source_node_id   UUID NOT NULL REFERENCES dag_nodes(id) ON DELETE CASCADE,
    target_node_id   UUID NOT NULL REFERENCES dag_nodes(id) ON DELETE CASCADE,
    relationship_id  UUID REFERENCES relationships(id) ON DELETE SET NULL,
    edge_type        TEXT NOT NULL DEFAULT 'assembly_ref',
    metadata         JSONB DEFAULT '{}',
    UNIQUE(source_node_id, target_node_id)
);

CREATE INDEX idx_dag_cross_source ON dag_cross_edges(source_node_id);
CREATE INDEX idx_dag_cross_target ON dag_cross_edges(target_node_id);

COMMIT;