/* SPARK relational schema */

drop table if exists proc;
create table proc (
  ProcID   varchar(100) not null,
  Name     varchar(250) not null,
  SourceID varchar(100),
  Recorded varchar(20),

  /* LAPDOG: */
  DemoID   varchar(100),  /* If learned by LapDog, 
                             pointer to demo */
  Learner  varchar(100),  /* If learned, the CALO
                             subsystem responsible */

  /* Fields for Tailor modifications */
  Modification varchar(250),
  ModificationDate varchar(20),
  ModificationTime varchar(20),
  ModificationInstruction varchar(250),
  ModificationAuthor varchar(20),
  ModificationUser varchar(20),
  ModificationComment varchar(250),

  primary key(ProcID,Recorded),

  foreign key (SourceID) references source(SourceID)
    on delete cascade
    on update cascade

  /* LAPDOG */
  foreign key (DemoID) references demonstration(DemoID)
    on delete cascade
    on update cascade

  );

drop table if exists sub_procedure;
create table sub_procedure(

  SubProcID   varchar(100) not null,
  SuperProcID varchar(100),
  Defines     varchar(250),
  hasTemporalDependency char, /* {"seq","parallel"},*/
  hasAction char, /* {"do","select", "wait", "context"},*/
  hasPosition int,
  Recorded    varchar(20),

  /* LAPDOG */
  demonstratedP char, /* (T if step demonstrated) */

  primary key(SubProcID,Recorded),

  foreign key (SuperProcID) references proc(ProcID)
    on delete cascade
    on update cascade,

  foreign key (Defines) references sub_procedure(SubProcID)
    on delete cascade
    on update cascade
  );

drop table if exists procedure_instance;
create table procedure_instance (
  ProcInstID  varchar(100) not null,
  ProcID      varchar(100) not null,
  Name        varchar(250) not null,
  TS_start    TimeStamp not null,
  TS_end      TimeStamp,
  Established varchar(250) not null,
  Recorded    varchar(20),

  primary key(ProcInstID,Recorded),

  foreign key (ProcID) references proc(ProcID)
    on delete cascade
    on update cascade,

  foreign key (Established) references intention(IntentionID)
    on delete cascade
    on update cascade

  );
drop table if exists intention;
create table intention (
  IntentionID varchar(100) not null,
  Predicate   varchar(250),
  TS_start    TimeStamp not null,
  TS_end      TimeStamp,
  State       varchar(20),
  SourceID    varchar(100),
  Recorded    varchar(20),

  primary key(IntentionID,Recorded),

  foreign key (SourceID) references source(SourceID)
    on delete cascade
    on update cascade

  );

drop table if exists intention_order;
create table intention_order (
  BeforeID   varchar(100) not null,
  AfterID    varchar(100) not null,
  Recorded   varchar(20),

  primary key(BeforeID,AfterID,Recorded),
  unique index AfterIdx(AfterID,BeforeID,Recorded),

  foreign key (BeforeID) references intention(IntentionID)
    on delete cascade
    on update cascade,

  foreign key (AfterID) references intention(IntentionID)
    on delete cascade
    on update cascade
  );

drop table if exists implement;
create table implement (
  IntentionID   varchar(100) not null,
  ProcInstID    varchar(100) not null,
  Recorded      varchar(20),

  primary key(IntentionID,ProcInstID,Recorded),

  unique index ProcInstanceIdx(ProcInstID,IntentionID,Recorded),

  foreign key (IntentionID) references intention(IntentionID)
    on delete cascade
    on update cascade,

  foreign key (ProcInstanceID) references procedure_instance(ProcInstID)
    on delete cascade
    on update cascade

  );

/* Auxiliary tables */
drop table if exists precondition;
create table precondition (
  ProcID          varchar(100) not null,
  Content         varchar(250) not null,
  isWaitCondition varchar(1) not null,
  status          varchar(1),
  Recorded        varchar(20),

  primary key(ProcID,Content,Recorded),

  foreign key (ProcID) references proc(ProcID)
    on delete cascade
    on update cascade

  );

drop table if exists wait_condition;
create table wait_condition (  

  /* this is a many-to-many relation between preconditions that are */
  /* wait-conditions and procedure instances. The wait-conditions   */
  /* themselves are store as preconditions.                         */

  ProcID     varchar(100) not null,
  Content    varchar(250) not null,
  ProcInstID varchar(100) not null,
  Recorded   varchar(20),

  primary key(ProcID,Content,ProcInstID,Recorded),

  foreign key (ProcID,Content) references precondition(ProcID,Content)
    on delete cascade
    on update cascade,

  foreign key (ProcInstID) references procedure_instance(ProcInstID)
    on delete cascade
    on update cascade
  );

drop table if exists source;
create table source (
  SourceID   varchar(100) not null,
  Name       varchar(250) not null,
  Recorded   varchar(20),

  primary key(SourceID,Recorded)

  );
drop table if exists argument;
create table argument (
  ProcID     varchar(100) not null,
  Name       varchar(250) not null,
  Type       varchar(250) not null,
  Recorded   varchar(20),

  primary key(ProcID,Name,Recorded),

  foreign key (ProcID) references proc(ProcID)
    on delete cascade
    on update cascade

  );

drop table if exists value;
create table value (
  ProcInstID varchar(100) not null,
  varName    varchar(250) not null,
  content    varchar(250) not null,
  Recorded   varchar(20),

  primary key(ProcInstID,varName,Recorded),

  foreign key (ProcInstID) references procedure_instance(ProcInstID)
    on delete cascade
    on update cascade

  );
drop table if exists value;
create table value (
  ProcInstID varchar(100) not null,
  varName    varchar(250) not null,
  content    varchar(250) not null,
  Recorded   varchar(20),

  primary key(ProcInstID,varName,Recorded),

  foreign key (ProcInstID) references procedure_instance(ProcInstID)
    on delete cascade
    on update cascade

  );

drop table if exists modification;
create table modification (
  ModID    varchar(100) not null,  /* Identifier for the modification */
  ModType  varchar(250) not null,  /* ADDCONDITION, ADDSTEP, CREATEPROC, 
                                      REMOVESTEP, CHANGESTEP, or
                                      REMOVECONDITION */

  /* Right now, we can only get NewProcID; OldProcID included for when
     Spark stores them */
  NewProcID varchar(100),          /* The new procedure produced by the mod */
  OldProcID varchar(100),          /* The old procedure that was modified */

  Condition varchar(250),          /* Content of new condition if 
                                      ADDCONDITION, old condition if
                                      REMOVECONDITION, null otherwise */

  OldStep   varchar(250),          /* Content of the old step if 
                                      anything except CREATEPROC or
                                      ADDCONDITION*/

  NewStep   varchar(250),          /* Content of the new step if 
                                      anything except CREATEPROC or
                                      REMOVECONDITION */

  StepParent varchar(250),         /* non-leaf clause (including sequence, 
                                      parallel, select, etc.) that contains
                                      the modified step, null if top-level */

  StepIndex varchar(3),            /* Integer representing the step's place
                                      in the list of children */

  Recorded  varchar(20),

  primary key(ModID,Recorded),

  foreign key (NewProcID) references proc(ProcID)
    on delete cascade
    on update cascade

  foreign key (OldProcID) references proc(ProcID)
    on delete cascade
    on update cascade

  );

/* LAPDOG */
drop table if exists demonstration;
create table demonstration (
  DemoID    varchar(100) not null,  /* Identifier for the learning by 
                                       demonstration episode */
  Learner   varchar(100) not null,  /* For now, always "LAPDOG" */
  Teacher   varchar(250) not null,  /* User performing demonstration */

  RecordStartDate varchar(20),      /* Date/time when the demo began */
  RecordEndDate varchar(20),        /* Date/time when the demo ended */

  DataReference varchar(1000),      /* Representation of user demonstration */

  Comments varchar(100),            /* Optional space for user-provided 
                                       comments */

  primary key(DemoID)

  );

/* LAPDOG */
drop table if exists demovarbinding;
create table demovarbinding (
  DemoID    varchar(100) not null,  
  varName    varchar(250) not null,
  content    varchar(250) not null,

  primary key(DemoID,varName),

  foreign key (DemoID) references demonstration(DemoID)
    on delete cascade
    on update cascade

  );


/* LAPDOG */
drop table if exists democonstant;
create table democonstant (
  DemoID    varchar(100) not null,  
  constant  varchar(250) not null,

  primary key(DemoID,constant),

  foreign key (DemoID) references demonstration(DemoID)
    on delete cascade
    on update cascade

  );