KSP_project/topology_api/migrations/002_tables.sql

-- topology_api/migrations/002_tables.sql
-- 8 core tables with temporal constraints

-- 1. bases
CREATE TABLE topology.bases (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    code text NOT NULL,
    name text NOT NULL,
    description text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT uq_bases_code UNIQUE (code)
);

-- 2. missions
CREATE TABLE topology.missions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    base_id uuid NOT NULL REFERENCES topology.bases(id),
    code citext NOT NULL,
    title text NOT NULL,
    occurrence_status text NOT NULL DEFAULT 'occurred'
        CHECK (occurrence_status IN ('occurred', 'planned')),
    description text,
    source_ref text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT uq_missions_base_code UNIQUE (base_id, code)
);

-- 3. mission_events
CREATE TABLE topology.mission_events (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    mission_id uuid NOT NULL REFERENCES topology.missions(id),
    effective_at timestamptz NOT NULL,
    event_order integer NOT NULL DEFAULT 0,
    event_type text NOT NULL,
    title text NOT NULL,
    details jsonb,
    source_ref text,
    recorded_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT uq_mission_event_time UNIQUE (mission_id, effective_at, event_order)
);

-- 4. components
CREATE TABLE topology.components (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    base_id uuid NOT NULL REFERENCES topology.bases(id),
    component_key text NOT NULL,
    component_type text NOT NULL
        CHECK (component_type IN ('module', 'node', 'vehicle', 'equipment')),
    introduced_by_event_id uuid NOT NULL REFERENCES topology.mission_events(id),
    retired_by_event_id uuid REFERENCES topology.mission_events(id),
    metadata jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT uq_components_base_key UNIQUE (base_id, component_key)
);

-- 5. component_versions
CREATE TABLE topology.component_versions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    component_id uuid NOT NULL REFERENCES topology.components(id),
    display_name text NOT NULL,
    summary text,
    description text,
    length_m numeric(10,3),
    width_m numeric(10,3),
    height_m numeric(10,3),
    diameter_m numeric(10,3),
    mass_t numeric(12,3),
    detail_url text,
    properties jsonb,
    valid_from timestamptz NOT NULL,
    valid_to timestamptz,
    started_by_event_id uuid NOT NULL REFERENCES topology.mission_events(id),
    ended_by_event_id uuid REFERENCES topology.mission_events(id),
    valid_period tstzrange GENERATED ALWAYS AS (
        tstzrange(valid_from, valid_to, '[)')
    ) STORED,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT ck_version_valid_range CHECK (valid_from < valid_to OR valid_to IS NULL)
);

-- 6. component_states
CREATE TABLE topology.component_states (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    component_id uuid NOT NULL REFERENCES topology.components(id),
    state_kind text NOT NULL,
    state_value text NOT NULL,
    details jsonb,
    valid_from timestamptz NOT NULL,
    valid_to timestamptz,
    started_by_event_id uuid NOT NULL REFERENCES topology.mission_events(id),
    ended_by_event_id uuid REFERENCES topology.mission_events(id),
    valid_period tstzrange GENERATED ALWAYS AS (
        tstzrange(valid_from, valid_to, '[)')
    ) STORED,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT ck_state_valid_range CHECK (valid_from < valid_to OR valid_to IS NULL)
);

-- 7. ports
CREATE TABLE topology.ports (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    component_id uuid NOT NULL REFERENCES topology.components(id),
    port_key text NOT NULL,
    display_name text NOT NULL,
    direction text,
    interface_type text NOT NULL DEFAULT 'structural',
    properties jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT uq_ports_component_key UNIQUE (component_id, port_key)
);

-- 8. topology_connections
CREATE TABLE topology.topology_connections (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    base_id uuid NOT NULL REFERENCES topology.bases(id),
    port_a_id uuid NOT NULL REFERENCES topology.ports(id),
    port_b_id uuid NOT NULL REFERENCES topology.ports(id),
    connection_type text NOT NULL
        CHECK (connection_type IN (
            'structural_mount', 'pressurized_passage', 'docking',
            'power', 'fuel', 'cooling'
        )),
    properties jsonb,
    valid_from timestamptz NOT NULL,
    valid_to timestamptz,
    established_by_event_id uuid NOT NULL REFERENCES topology.mission_events(id),
    ended_by_event_id uuid REFERENCES topology.mission_events(id),
    valid_period tstzrange GENERATED ALWAYS AS (
        tstzrange(valid_from, valid_to, '[)')
    ) STORED,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT ck_connection_valid_range CHECK (valid_from < valid_to OR valid_to IS NULL),
    CONSTRAINT ck_different_ports CHECK (port_a_id <> port_b_id)
);

-- Temporal exclusion constraints
ALTER TABLE topology.component_versions
    ADD CONSTRAINT ex_version_no_overlap
    EXCLUDE USING gist (component_id WITH =, valid_period WITH &&);

ALTER TABLE topology.component_states
    ADD CONSTRAINT ex_state_no_overlap
    EXCLUDE USING gist (component_id WITH =, state_kind WITH =, valid_period WITH &&);