CREATE CACHED TABLE Groups (
  Id int PRIMARY KEY NOT NULL IDENTITY,
  Label VARCHAR(200)
);

CREATE CACHED TABLE Groups_Users (
  Group_Id int NOT NULL,
  Login VARCHAR (200) NOT NULL,
  PRIMARY KEY (Group_Id, Login)
 );

CREATE CACHED TABLE Users (
  login varchar(64) PRIMARY KEY NOT NULL,
  firstname varchar(64) default NULL,
  lastname varchar(64) NOT NULL,
  email varchar(64) DEFAULT NULL,
  password varchar(128)  NOT NULL
);

CREATE CACHED TABLE Newsletter_Subscribers (
        Email VARCHAR(255) not null,
        Site_Name VARCHAR(48) NOT NULL,
        Category VARCHAR(64) NOT NULL,
        Subscribed_At date not null,
        Token VARCHAR(64) NOT NULL,
        PRIMARY KEY(Email, Site_Name, Category)
);

CREATE CACHED TABLE Rights_Profile (
        Id int PRIMARY KEY NOT NULL IDENTITY,
        Label VARCHAR(200),
        Context VARCHAR(200)
);

CREATE CACHED TABLE Rights_ProfileRights (
        Profile_Id int NOT NULL,
        Right_Id VARCHAR(200) NOT NULL,
        PRIMARY KEY(Profile_Id, Right_Id)
);

CREATE CACHED TABLE Rights_GroupRights (
        Profile_Id int NOT NULL,
        Group_Id VARCHAR(200) NOT NULL,
        Context VARCHAR(200) NOT NULL,
        PRIMARY KEY(Profile_Id, Group_Id, Context)
);

CREATE CACHED TABLE Rights_UserRights (
        Profile_Id int NOT NULL,
        Login VARCHAR(200) NOT NULL,
        Context VARCHAR(200) NOT NULL,
        PRIMARY KEY(Profile_Id, Login, Context)
);

CREATE CACHED TABLE UserPreferences (
  login varchar(32) NOT NULL,
  context varchar(128) NOT NULL,
  data BLOB,
  PRIMARY KEY(login, context)
);

CREATE CACHED TABLE Cache_RA_HTTPServer (
  Unique_Id VARCHAR(255) NOT NULL,
  Site VARCHAR(255) NOT NULL,
  Request_Date TIMESTAMP NOT NULL,
  Method VARCHAR(255) NOT NULL,
  Path_Hash VARCHAR(255) NOT NULL,
  Path VARCHAR(4000) NOT NULL,
  Query_String VARCHAR(4000) NOT NULL,
  Ori_Status_Code CHAR(3) NOT NULL,
  Ret_Status_Code CHAR(3) NOT NULL,
  Cache_Hit SMALLINT NOT NULL,
  Created_At TIMESTAMP NOT NULL,
  Processed SMALLINT DEFAULT 0 NOT NULL,
  PRIMARY KEY (Unique_Id)
);

CREATE CACHED TABLE Cache_RA_Front (
  Unique_Id VARCHAR(255) NOT NULL,
  Internal_Uuid VARCHAR(255) NOT NULL,
  Site VARCHAR(255) NOT NULL,
  Ametys_Path_Hash VARCHAR(255) NOT NULL,
  Ametys_Path VARCHAR(4000) NOT NULL,
  Cacheable SMALLINT NOT NULL,
  Cache_Hit_1 SMALLINT NOT NULL,
  Cache_Hit_2 SMALLINT NOT NULL,
  Created_At TIMESTAMP NOT NULL,
  Processed SMALLINT DEFAULT 0 NOT NULL
);

CREATE CACHED TABLE Cache_RA_Back (
  Internal_Uuid VARCHAR(255) NOT NULL,
  Page_Id VARCHAR(255) NOT NULL,
  Page_Path_Hash VARCHAR(255) NOT NULL,
  Page_Path VARCHAR(4000) NOT NULL,
  Rendering_Context VARCHAR(255) NOT NULL,
  Workspace_JCR VARCHAR(255) NOT NULL,
  Cacheable SMALLINT NOT NULL,
  Created_At TIMESTAMP NOT NULL,
  Processed SMALLINT DEFAULT 0 NOT NULL
);

CREATE CACHED TABLE Cache_RA_Back_Page_Element (
  Internal_Uuid VARCHAR(255) NOT NULL,
  Page_Element_Id VARCHAR(255) NOT NULL,
  Page_Element_Type VARCHAR(255) NOT NULL,
  Page_Id VARCHAR(255) NOT NULL,
  Rendering_Context VARCHAR(255) NOT NULL,
  Workspace_JCR VARCHAR(255) NOT NULL,
  Cacheable SMALLINT NOT NULL,
  Cache_Hit SMALLINT NOT NULL,
  Created_At TIMESTAMP NOT NULL,
  Processed SMALLINT DEFAULT 0 NOT NULL
);

CREATE CACHED TABLE Cache_Stats_Front (
  Server_Site VARCHAR(255) DEFAULT '-' NOT NULL,
  Server_Path_Hash VARCHAR(255) DEFAULT '-' NOT NULL,
  Server_Path VARCHAR(4000),
  Server_Hits INT DEFAULT 0 NOT NULL,
  Server_Cache_Hits INT DEFAULT 0 NOT NULL,
  Front_Site VARCHAR(255) DEFAULT '-' NOT NULL,
  Front_Path_Hash VARCHAR(255) DEFAULT '-' NOT NULL,
  Front_Path VARCHAR(4000),
  Front_Cacheable SMALLINT DEFAULT 0 NOT NULL,
  Front_Hits INT DEFAULT 0 NOT NULL,
  Front_Cache_Hits_1 INT DEFAULT 0 NOT NULL,
  Front_Cache_Hits_2 INT DEFAULT 0 NOT NULL,
  Created_At TIMESTAMP NOT NULL,
  Updated_At TIMESTAMP NOT NULL,
  UNIQUE (Server_Site, Server_Path_Hash, Front_Site, Front_Path_Hash)
);

CREATE CACHED TABLE Cache_Stats_Back (
  Page_Id VARCHAR(255) NOT NULL,
  Page_Path_Hash VARCHAR(255) NOT NULL,
  Page_Path VARCHAR(4000) NOT NULL,
  Rendering_Context VARCHAR(255) NOT NULL,
  Workspace_JCR VARCHAR(255) NOT NULL,
  Cacheable SMALLINT NOT NULL,
  Hits INT NOT NULL,
  Created_At TIMESTAMP NOT NULL,
  Updated_At TIMESTAMP NOT NULL,
  PRIMARY KEY (Page_Id, Rendering_Context, Workspace_JCR)
);

CREATE CACHED TABLE Cache_Stats_Back_Page_Element (
  Page_Element_Id VARCHAR(255) NOT NULL,
  Page_Id VARCHAR(255) NOT NULL,
  Rendering_Context VARCHAR(255) NOT NULL,
  Workspace_JCR VARCHAR(255) NOT NULL,
  Cacheable SMALLINT NOT NULL,
  Hits INT NOT NULL,
  Cache_Hits INT NOT NULL,
  Created_At TIMESTAMP NOT NULL,
  Updated_At TIMESTAMP NOT NULL,
  PRIMARY KEY (Page_Element_Id, Page_Id, Rendering_Context, Workspace_JCR)
);

CREATE CACHED TABLE FOUsers (
  login varchar(255) PRIMARY KEY NOT NULL,
  firstname varchar(64) default NULL,
  lastname varchar(64) NOT NULL,
  email varchar(64) DEFAULT NULL,
  password varchar(128)  NOT NULL
);

CREATE CACHED TABLE FOUserPreferences (
  login varchar(255) NOT NULL, 
  context varchar(128) NOT NULL,
  data BLOB,
  PRIMARY KEY(login, context)
);

CREATE CACHED TABLE Users_Temp (
  site varchar(255) NOT NULL,
  email varchar(255) NOT NULL,
  firstname varchar(64) NOT NULL,
  lastname varchar(64) NOT NULL,
  subscription_date TIMESTAMP NOT NULL,
  token varchar(128) NOT NULL,
  PRIMARY KEY (site, email),
  UNIQUE (token)
);

CREATE CACHED TABLE Users_PasswordChange (
  site varchar(255) NOT NULL,
  login varchar(255) NOT NULL,
  request_date TIMESTAMP NOT NULL,
  token varchar(128) NOT NULL,
  PRIMARY KEY(site, login)
);