-- DROP TABLE IF EXISTS nodeLinkT ;

CREATE TABLE IF NOT EXISTS nodeLinkT (
  _id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  nodeLinkTypeId INT UNSIGNED NOT NULL,
  srcNodeId INT UNSIGNED NOT NULL,
  dstNodeId INT UNSIGNED NOT NULL,
  metric INT UNSIGNED NOT NULL DEFAULT 1,
  intermediate INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'if this link has way around it via other intermediate nodes',
  added TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (_id),
  CONSTRAINT fk_nodeLinkT_nodeLinkTypeT1
    FOREIGN KEY (nodeLinkTypeId)
    REFERENCES nodeLinkTypeT (id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT fk_nodeLinkT_nodeT1
    FOREIGN KEY (srcNodeId)
    REFERENCES nodeT (id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT fk_nodeLinkT_nodeT2
    FOREIGN KEY (dstNodeId)
    REFERENCES nodeT (id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE UNIQUE INDEX nodelinktypeid_srcnodeid_dstnodeid_uq ON nodeLinkT (nodeLinkTypeId ASC, srcNodeId ASC, dstNodeId ASC);

CREATE INDEX fk_nodeLinkT_nodeT1_idx ON nodeLinkT (srcNodeId ASC);

CREATE INDEX fk_nodeLinkT_nodeT2_idx ON nodeLinkT (dstNodeId ASC);


/*
CREATE INDEX nodelinktypeid_dstnodeid_srcnodeid_idx ON nodeLinkT (nodeLinkTypeId ASC, dstNodeId ASC, srcNodeId ASC);

CREATE INDEX nodelinktypeid_srcnodeid_metric_idx ON nodeLinkT (nodeLinkTypeId ASC, srcNodeId ASC, metric ASC);

CREATE INDEX nodelinktypeid_dstnodeid_metric_idx ON nodeLinkT (nodeLinkTypeId ASC, dstNodeId ASC, metric ASC);

CREATE INDEX nodelinktypeid_srcnodeid_intermediate_idx ON nodeLinkT (nodeLinkTypeId ASC, srcNodeId ASC, intermediate ASC);

CREATE INDEX nodelinktypeid_dstnodeid_intermediate_idx ON nodeLinkT (nodeLinkTypeId ASC, dstNodeId ASC, intermediate ASC);
*/

CREATE INDEX nodelinktypeid_dstnodeid_srcnodeid_metric_idx ON nodeLinkT (nodeLinkTypeId ASC, dstNodeId ASC, srcNodeId ASC, metric ASC);
CREATE INDEX nodelinktypeid_srcnodeid_dstnodeid_metric_idx ON nodeLinkT (nodeLinkTypeId ASC, srcNodeId ASC, dstNodeId ASC, metric ASC);

/*
CREATE INDEX nodelinktypeid_dstnodeid_srcnodeid_intermediate_idx ON nodeLinkT (nodeLinkTypeId ASC, dstNodeId ASC, srcNodeId ASC, intermediate ASC);
CREATE INDEX nodelinktypeid_srcnodeid_dstnodeid_intermediate_idx ON nodeLinkT (nodeLinkTypeId ASC, srcNodeId ASC, dstNodeId ASC, intermediate ASC);
*/