CREATE TABLE Metadata (
  idMetadata SERIAL  NOT NULL ,
  Name VARCHAR    ,
  Description TEXT    ,
  Hoover VARCHAR    ,
  Obligation BOOL      ,
PRIMARY KEY(idMetadata));


CREATE TABLE Format (
  idFormat SERIAL  NOT NULL ,
  Nom VARCHAR    ,
  Extension VARCHAR      ,
PRIMARY KEY(idFormat));


CREATE TABLE Resolution (
  idResolution SERIAL  NOT NULL ,
  Nom VARCHAR    ,
  Description TEXT      ,
PRIMARY KEY(idResolution));


CREATE TABLE Rights (
  idRights SERIAL  NOT NULL ,
  Description VARCHAR    ,
  Name VARCHAR      ,
PRIMARY KEY(idRights));


CREATE TABLE Type (
  idType SERIAL  NOT NULL ,
  Name VARCHAR    ,
  Description TEXT      ,
PRIMARY KEY(idType));


CREATE TABLE Workflow (
  idWorkflow SERIAL  NOT NULL ,
  Name VARCHAR    ,
  Description VARCHAR      ,
PRIMARY KEY(idWorkflow));


CREATE TABLE Rule (
  idRule SERIAL  NOT NULL ,
  Name VARCHAR    ,
  Description VARCHAR      ,
PRIMARY KEY(idRule));


CREATE TABLE Role (
  idRole SERIAL  NOT NULL ,
  Nom VARCHAR    ,
  Authlvl INTEGER      ,
PRIMARY KEY(idRole));


CREATE TABLE Site (
  idSite SERIAL  NOT NULL ,
  Rule INTEGER   NOT NULL   ,
PRIMARY KEY(idSite)  ,
  FOREIGN KEY(Rule)
    REFERENCES Rule(idRule));

CREATE INDEX SFK_Rule ON Site (Rule);

CREATE TABLE Wanda_User (
  Certificate SERIAL  NOT NULL ,
  Role INTEGER   NOT NULL ,
  Name VARCHAR    ,
  Forename VARCHAR    ,
  Creator INTEGER    ,
  Mail VARCHAR      ,
PRIMARY KEY(Certificate)  ,
  FOREIGN KEY(Role)
    REFERENCES Role(idRole),
  FOREIGN KEY (Creator)
    REFERENCES Wanda_User(Certificate));

CREATE INDEX WUFK_Role ON Wanda_User(Role);


CREATE TABLE SiteMeta (
  Site INTEGER   NOT NULL ,
  Metadata INTEGER   NOT NULL ,
  Content TEXT      ,
PRIMARY KEY(Site, Metadata)    ,
  FOREIGN KEY(Site)
    REFERENCES Site(idSite),
  FOREIGN KEY(Metadata)
    REFERENCES Metadata(idMetadata));


CREATE INDEX SMFK_Site ON SiteMeta (Site);
CREATE INDEX SMFK_Meta ON SiteMeta (Metadata);

CREATE TABLE Annotation (
  idAnnotation SERIAL  NOT NULL ,
  Link VARCHAR NOT NULL,
  Workflow INTEGER   NOT NULL ,
  Owner INTEGER   NOT NULL ,
  Related INTEGER    ,
  LinkKey INTEGER   NOT NULL   ,
PRIMARY KEY(idAnnotation)      ,
  FOREIGN KEY(Owner)
    REFERENCES Wanda_User(Certificate),
  FOREIGN KEY(Workflow)
    REFERENCES Workflow(idWorkflow));


CREATE INDEX AFK_Owner ON Annotation (Owner);
CREATE INDEX AFK_Workflow ON Annotation (Workflow);
CREATE INDEX AFK_LinkKey ON Annotation (LinkKey);



CREATE TABLE Corpus (
  idCorpus SERIAL  NOT NULL ,
  Rule INTEGER   NOT NULL ,
  Site INTEGER   NOT NULL ,
  Owner INTEGER   NOT NULL   ,
PRIMARY KEY(idCorpus)      ,
  FOREIGN KEY(Owner)
    REFERENCES Wanda_User(Certificate),
  FOREIGN KEY(Site)
    REFERENCES Site(idSite),
  FOREIGN KEY(Rule)
    REFERENCES Rule(idRule));


CREATE INDEX CFK_Creator ON Corpus (Owner);
CREATE INDEX CFK_Site ON Corpus (Site);
CREATE INDEX CFK_Rule ON Corpus (Rule);


CREATE TABLE UserCorpusAccess (
  Wanda_User INTEGER   NOT NULL ,
  Corpus INTEGER   NOT NULL ,
  Rights INTEGER   NOT NULL   ,
PRIMARY KEY(Wanda_User, Corpus)      ,
  FOREIGN KEY(Wanda_User)
    REFERENCES Wanda_User(Certificate),
  FOREIGN KEY(Corpus)
    REFERENCES Corpus(idCorpus),
  FOREIGN KEY(Rights)
    REFERENCES Rights(idRights));


CREATE INDEX UCAFK_Wanda_User ON UserCorpusAccess (Wanda_User);
CREATE INDEX UCAFK_Corpus ON UserCorpusAccess (Corpus);
CREATE INDEX UCAFK_Rights ON UserCorpusAccess (Rights);


CREATE TABLE UserAnnotationAccess (
  Wanda_User INTEGER   NOT NULL ,
  Annotation INTEGER   NOT NULL ,
  Rights INTEGER   NOT NULL ,
  LastAcess DATE      ,
PRIMARY KEY(Wanda_User, Annotation)      ,
  FOREIGN KEY(Wanda_User)
    REFERENCES Wanda_User(Certificate),
  FOREIGN KEY(Annotation)
    REFERENCES Annotation(idAnnotation),
  FOREIGN KEY(Rights)
    REFERENCES Rights(idRights));


CREATE INDEX UAAFK_Wanda_User ON UserAnnotationAccess (Wanda_User);
CREATE INDEX UAAFK_Annotation ON UserAnnotationAccess (Annotation);
CREATE INDEX UAAFK_Rights ON UserAnnotationAccess (Rights);



CREATE TABLE Session (
  idSession SERIAL  NOT NULL ,
  Rule INTEGER   NOT NULL ,
  Corpus INTEGER   NOT NULL ,
  Owner INTEGER   NOT NULL   ,
PRIMARY KEY(idSession)      ,
  FOREIGN KEY(Owner)
    REFERENCES Wanda_User(Certificate),
  FOREIGN KEY(Corpus)
    REFERENCES Corpus(idCorpus),
  FOREIGN KEY(Rule)
    REFERENCES Rule(idRule));


CREATE INDEX SSFK_Creator ON Session (Owner);
CREATE INDEX SSFK_Corpus ON Session (Corpus);
CREATE INDEX SSFK_Rule ON Session (Rule);




CREATE TABLE UserSiteAccess (
  Wanda_User INTEGER   NOT NULL ,
  Site INTEGER   NOT NULL ,
  Rights INTEGER   NOT NULL   ,
PRIMARY KEY(Wanda_User, Site)      ,
  FOREIGN KEY(Wanda_User)
    REFERENCES Wanda_User(Certificate),
  FOREIGN KEY(Site)
    REFERENCES Site(idSite),
  FOREIGN KEY(Rights)
    REFERENCES Rights(idRights));


CREATE INDEX USAFK_Wanda_User ON UserSiteAccess (Wanda_User);
CREATE INDEX USAFK_Site ON UserSiteAccess (Site);
CREATE INDEX USAFK_Rights ON UserSiteAccess (Rights);



CREATE TABLE UserSessionAccess (
  Wanda_User INTEGER   NOT NULL ,
  Session INTEGER   NOT NULL ,
  Rights INTEGER   NOT NULL   ,
PRIMARY KEY(Wanda_User, Session)      ,
  FOREIGN KEY(Wanda_User)
    REFERENCES Wanda_User(Certificate),
  FOREIGN KEY(Session)
    REFERENCES Session(idSession),
  FOREIGN KEY(Rights)
    REFERENCES Rights(idRights));


CREATE INDEX USSAFK_Wanda_User ON UserSessionAccess (Wanda_User);
CREATE INDEX USSAFK_Session ON UserSessionAccess (Session);
CREATE INDEX USSAFK_Rights ON UserSessionAccess (Rights);



CREATE TABLE View (
  idView SERIAL  NOT NULL ,
  Type INTEGER   NOT NULL ,
  Rule INTEGER   NOT NULL ,
  Wanda_User INTEGER   NOT NULL ,
  Session INTEGER   NOT NULL   ,
PRIMARY KEY(idView)        ,
  FOREIGN KEY(Session)
    REFERENCES Session(idSession),
  FOREIGN KEY(Wanda_User)
    REFERENCES Wanda_User(Certificate),
  FOREIGN KEY(Rule)
    REFERENCES Rule(idRule),
  FOREIGN KEY(Type)
    REFERENCES Type(idType));


CREATE INDEX VWFK_Session ON View (Session);
CREATE INDEX VWFK_Owner ON View (Wanda_User);
CREATE INDEX VWFK_Rule ON View (Rule);
CREATE INDEX VWFK_Type ON View (Type);



CREATE TABLE Montage (
  idMontage SERIAL  NOT NULL ,
  Owner INTEGER   NOT NULL   ,
PRIMARY KEY(idMontage)  ,
  FOREIGN KEY(Owner)
    REFERENCES Wanda_User(Certificate));


CREATE INDEX MFK_Owner ON Montage (Owner);



CREATE TABLE ViewMeta (
  View INTEGER   NOT NULL ,
  Metadata INTEGER   NOT NULL ,
  Content TEXT      ,
PRIMARY KEY(View, Metadata)    ,
  FOREIGN KEY(View)
    REFERENCES View(idView),
  FOREIGN KEY(Metadata)
    REFERENCES Metadata(idMetadata));


CREATE INDEX VWMFK_View ON ViewMeta (View);
CREATE INDEX VWMFK_Meta ON ViewMeta (Metadata);


CREATE TABLE SubCorpus (
  Pere INTEGER   NOT NULL   ,
  Fils INTEGER	 NOT NULL   ,
PRIMARY KEY(Pere, Fils)    ,
  FOREIGN KEY(Pere)
    REFERENCES Corpus(idCorpus),
  FOREIGN KEY(Fils)
    REFERENCES Corpus(idCorpus));


CREATE INDEX SCFK_Pere ON SubCorpus (Pere);
CREATE INDEX SCFK_Fils ON SubCorpus (Fils);



CREATE TABLE SessionMeta (
  Session INTEGER   NOT NULL ,
  Metadata INTEGER   NOT NULL ,
  Content TEXT      ,
PRIMARY KEY(Session, Metadata)    ,
  FOREIGN KEY(Session)
    REFERENCES Session(idSession),
  FOREIGN KEY(Metadata)
    REFERENCES Metadata(idMetadata));


CREATE INDEX SSMFK_Session ON SessionMeta (Session);
CREATE INDEX SSMFK_Meta ON SessionMeta (Metadata);



CREATE TABLE CorpusMeta (
  Corpus INTEGER   NOT NULL ,
  Metadata INTEGER   NOT NULL ,
  Content TEXT      ,
PRIMARY KEY(Corpus, Metadata)    ,
  FOREIGN KEY(Corpus)
    REFERENCES Corpus(idCorpus),
  FOREIGN KEY(Metadata)
    REFERENCES Metadata(idMetadata));


CREATE INDEX CMFK_Corpus ON CorpusMeta (Corpus);
CREATE INDEX CMFK_Meta ON CorpusMeta (Metadata);


CREATE TABLE AnnotationMeta (
  Metadata INTEGER   NOT NULL ,
  Annotation INTEGER   NOT NULL ,
  Content TEXT      ,
PRIMARY KEY(Metadata, Annotation)    ,
  FOREIGN KEY(Metadata)
    REFERENCES Metadata(idMetadata),
  FOREIGN KEY(Annotation)
    REFERENCES Annotation(idAnnotation));


CREATE INDEX AMFK_Meta ON AnnotationMeta (Metadata);
CREATE INDEX AMFK_Annotation ON AnnotationMeta (Annotation);


CREATE TABLE MontageMeta (
  Montage INTEGER   NOT NULL ,
  Metadata INTEGER   NOT NULL ,
  Content TEXT      ,
PRIMARY KEY(Montage, Metadata)    ,
  FOREIGN KEY(Montage)
    REFERENCES Montage(idMontage),
  FOREIGN KEY(Metadata)
    REFERENCES Metadata(idMetadata));


CREATE INDEX MMFK_Montage ON MontageMeta (Montage);
CREATE INDEX MMFK_Metadata ON MontageMeta (Metadata);



CREATE TABLE MontageParts (
  Montage INTEGER   NOT NULL ,
  View INTEGER   NOT NULL   ,
PRIMARY KEY(Montage, View)    ,
  FOREIGN KEY(Montage)
    REFERENCES Montage(idMontage),
  FOREIGN KEY(View)
    REFERENCES View(idView));


CREATE INDEX MPFK_Montage ON MontageParts (Montage);
CREATE INDEX MPFK_View ON MontageParts (View);

CREATE TABLE UserViewAccess (
  Wanda_User INTEGER   NOT NULL ,
  View INTEGER   NOT NULL ,
  Rights INTEGER   NOT NULL   ,
PRIMARY KEY(Wanda_User, View)      ,
  FOREIGN KEY(Wanda_User)
    REFERENCES Wanda_User(Certificate),
  FOREIGN KEY(View)
    REFERENCES View(idView),
  FOREIGN KEY(Rights)
    REFERENCES Rights(idRights));


CREATE INDEX UVWAFK_Wanda_User ON UserViewAccess (Wanda_User);
CREATE INDEX UVWAFK_View ON UserViewAccess (View);
CREATE INDEX UVWAFK_Rights ON UserViewAccess (Rights);


CREATE TABLE Video (
  idVideo SERIAL  NOT NULL ,
  Workflow INTEGER   NOT NULL ,
  View INTEGER   NOT NULL ,
  Wanda_User INTEGER   NOT NULL   ,
PRIMARY KEY(idVideo)      ,
  FOREIGN KEY(Wanda_User)
    REFERENCES Wanda_User(Certificate),
  FOREIGN KEY(View)
    REFERENCES View(idView),
  FOREIGN KEY(Workflow)
    REFERENCES Workflow(idWorkflow));


CREATE INDEX VFK_Owner ON Video (Wanda_User);
CREATE INDEX VFK_View ON Video (View);
CREATE INDEX VFK_Workflow ON Video (Workflow);


CREATE TABLE UserVideoAccess (
  Wanda_User INTEGER   NOT NULL ,
  Video INTEGER   NOT NULL ,
  Rights INTEGER   NOT NULL ,
  LastAccess DATE      ,
PRIMARY KEY(Wanda_User, Video)      ,
  FOREIGN KEY(Wanda_User)
    REFERENCES Wanda_User(Certificate),
  FOREIGN KEY(Video)
    REFERENCES Video(idVideo),
  FOREIGN KEY(Rights)
    REFERENCES Rights(idRights));


CREATE INDEX UVAFK_Wanda_User ON UserVideoAccess (Wanda_User);
CREATE INDEX UVAFK_Video ON UserVideoAccess (Video);
CREATE INDEX UVAFK_Rights ON UserVideoAccess (Rights);


CREATE TABLE VideoMeta (
  Metadata INTEGER   NOT NULL ,
  Video INTEGER   NOT NULL ,
  Content TEXT      ,
PRIMARY KEY(Metadata, Video)    ,
  FOREIGN KEY(Metadata)
    REFERENCES Metadata(idMetadata),
  FOREIGN KEY(Video)
    REFERENCES Video(idVideo));


CREATE INDEX VMFK_Meta ON VideoMeta (Metadata);
CREATE INDEX VMFK_Video ON VideoMeta (Video);


CREATE TABLE ConcreteVideo (
  Video INTEGER   NOT NULL ,
  Resolution INTEGER   NOT NULL ,
  Format INTEGER   NOT NULL ,
  Link VARCHAR      ,
PRIMARY KEY(Video, Resolution, Format)      ,
  FOREIGN KEY(Video)
    REFERENCES Video(idVideo),
  FOREIGN KEY(Resolution)
    REFERENCES Resolution(idResolution),
  FOREIGN KEY(Format)
    REFERENCES Format(idFormat));


CREATE INDEX CVFK_Video ON ConcreteVideo (Video);
CREATE INDEX CVFK_Resolution ON ConcreteVideo (Resolution);
CREATE INDEX CVFK_Format ON ConcreteVideo (Format);