CREATE DATABASE PROBLEM;
USE PROBLEM;

CREATE TABLE LIST (
  ID INTEGER PRIMARY KEY AUTO_INCREMENT
);
CREATE TABLE TBL_DATASET (
  ID INTEGER PRIMARY KEY AUTO_INCREMENT
);
CREATE TABLE TBL_INSTANCE (
  ID INTEGER PRIMARY KEY AUTO_INCREMENT
);
CREATE TABLE TBL_ENTRY(
  ID INTEGER PRIMARY KEY AUTO_INCREMENT,
  TBL_DATASET_ID INTEGER NOT NULL,
  CONSTRAINT "TBL_ENTRY_TBL_DATASET_FK" FOREIGN KEY ("TBL_DATASET_ID") REFERENCES "TBL_DATASET" ("ID") ON DELETE CASCADE
);
CREATE TABLE TBL_ROW(
  ID INTEGER PRIMARY KEY AUTO_INCREMENT,
  LIST_ID INTEGER NOT NULL,
  TBL_DATASET_ID INTEGER NOT NULL,
  CONSTRAINT "SEQ_TBL_DATASET_FK" FOREIGN KEY ("TBL_DATASET_ID") REFERENCES "TBL_ROW" ("ID"),
  CONSTRAINT "LIST_FK" FOREIGN KEY ("LIST_ID") REFERENCES "LIST" ("ID")
);
CREATE TABLE TBL_REGION(
  ID INTEGER PRIMARY KEY AUTO_INCREMENT,
  TOP INTEGER NOT NULL,
  BOTTOM INTEGER NOT NULL,
  TBL_DATASET_ID INTEGER DEFAULT NULL,
  CONSTRAINT "TOP_FK" FOREIGN KEY ("TOP") REFERENCES "TBL_ROW" ("ID"),
  CONSTRAINT "BOTTOM_FK" FOREIGN KEY ("BOTTOM") REFERENCES "TBL_ROW" ("ID"),
  CONSTRAINT "TBL_REGION_TBL_DATASET_ID_FK" FOREIGN KEY ("TBL_DATASET_ID") REFERENCES "TBL_DATASET" ("ID") ON DELETE CASCADE  
);
CREATE TABLE STATUS (
  TBL_ROW_ID INTEGER DEFAULT NULL,
  TBL_DATASET_ID INTEGER DEFAULT NULL,
  TBL_ENTRY_ID INTEGER NOT NULL,
  TBL_INSTANCE_ID INTEGER NOT NULL,
  PRIMARY KEY ("TBL_ENTRY_ID","TBL_INSTANCE_ID"),
  KEY "STATUS_IDX2" ("TBL_ROW_ID"),
  KEY "STATUS_IDX3" ("TBL_DATASET_ID"),
  KEY "STATUS_TBL_INSTANCE" ("TBL_INSTANCE_ID"),
  CONSTRAINT "TBL_INSTANCE_FK" FOREIGN KEY ("TBL_INSTANCE_ID") REFERENCES "TBL_INSTANCE" ("ID"),
  CONSTRAINT "STATUS_TBL_ENTRY_FK" FOREIGN KEY ("TBL_ENTRY_ID") REFERENCES "TBL_ENTRY" ("ID") ON DELETE CASCADE,
  CONSTRAINT "STATUS_TBL_DATASET_FK1" FOREIGN KEY ("TBL_DATASET_ID") REFERENCES "TBL_DATASET" ("ID") ON DELETE CASCADE,
  CONSTRAINT "STATUS_TBL_ROW_FK1" FOREIGN KEY ("TBL_ROW_ID") REFERENCES "TBL_ROW" ("ID") ON DELETE CASCADE
);