CREATE TABLE IF NOT EXISTS hardware_network_links (
    id SERIAL PRIMARY KEY,
    source_hardware_id INTEGER NOT NULL REFERENCES hardware_assets(id) ON DELETE CASCADE,
    source_port VARCHAR(100) NOT NULL,
    target_hardware_id INTEGER NOT NULL REFERENCES hardware_assets(id) ON DELETE CASCADE,
    target_port VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMP,
    CHECK (source_hardware_id <> target_hardware_id)
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_hardware_network_links_source_port_active
    ON hardware_network_links(source_hardware_id, source_port)
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_hardware_network_links_target_active
    ON hardware_network_links(target_hardware_id)
    WHERE deleted_at IS NULL;