DROP TABLE IF EXISTS cmAction;
DROP TABLE IF EXISTS cmActionDefinition;
DROP TABLE IF EXISTS cmActor;
DROP TABLE IF EXISTS cmConsequence;
DROP TABLE IF EXISTS cmConsequenceDefinition;
DROP TABLE IF EXISTS cmFunction;
DROP TABLE IF EXISTS cmWorkflow;

ALTER TABLE cmContentTypeDefinition CHANGE COLUMN name name varchar(50) NOT NULL;

ALTER TABLE cmRole CHANGE COLUMN name roleName varchar(200) NOT NULL;

ALTER TABLE cmSystemUserRole DROP COLUMN systemUserRoleId;
ALTER TABLE cmSystemUserRole ADD COLUMN userName varchar(100) NOT NULL;
ALTER TABLE cmSystemUserRole ADD COLUMN roleName varchar(200) NOT NULL;

update cmSystemUserRole, cmSystemUser set cmSystemUserRole.userName = cmSystemUser.userName where cmSystemUserRole.systemUserId = cmSystemUser.systemUserId;
update cmSystemUserRole, cmRole set cmSystemUserRole.roleName = cmRole.roleName where cmSystemUserRole.roleId = cmRole.roleId;

ALTER TABLE cmContent ADD COLUMN creator varchar(255) NOT NULL;
update cmContent, cmSystemUser set cmContent.creator = cmSystemUser.userName where cmContent.systemUserId = cmSystemUser.systemUserId;
ALTER TABLE cmContent DROP COLUMN systemUserId;

ALTER TABLE cmContentVersion ADD COLUMN versionModifier varchar(255) NOT NULL;
update cmContentVersion, cmSystemUser set cmContentVersion.versionModifier = cmSystemUser.userName where cmContentVersion.systemUserId = cmSystemUser.systemUserId;
ALTER TABLE cmContentVersion DROP COLUMN systemUserId;

ALTER TABLE cmEvent ADD COLUMN creator varchar(255) NOT NULL;
update cmEvent, cmSystemUser set cmEvent.creator = cmSystemUser.userName where cmEvent.systemUserId = cmSystemUser.systemUserId;
ALTER TABLE cmEvent DROP COLUMN systemUserId;

ALTER TABLE cmSiteNode ADD COLUMN creator varchar(255) NOT NULL;
update cmSiteNode, cmSystemUser set cmSiteNode.creator = cmSystemUser.userName where cmSiteNode.systemUserId = cmSystemUser.systemUserId;
ALTER TABLE cmSiteNode DROP COLUMN systemUserId;

ALTER TABLE cmSiteNodeVersion ADD COLUMN versionModifier varchar(255) NOT NULL;
update cmSiteNodeVersion, cmSystemUser set cmSiteNodeVersion.versionModifier = cmSystemUser.userName where cmSiteNodeVersion.systemUserId = cmSystemUser.systemUserId;
ALTER TABLE cmSiteNodeVersion DROP COLUMN systemUserId;

ALTER TABLE cmPublication ADD COLUMN publisher varchar(255) NOT NULL;
update cmPublication, cmSystemUser set cmPublication.publisher = cmSystemUser.userName where cmPublication.systemUserId = cmSystemUser.systemUserId;
ALTER TABLE cmPublication DROP COLUMN systemUserId;

ALTER TABLE cmPublicationDetail ADD COLUMN publisher varchar(255) NOT NULL;
update cmPublicationDetail, cmSystemUser set cmPublicationDetail.publisher = cmSystemUser.userName where cmPublicationDetail.systemUserId = cmSystemUser.systemUserId;
ALTER TABLE cmPublicationDetail DROP COLUMN systemUserId;


ALTER TABLE cmSystemUserRole DROP COLUMN systemUserId;
ALTER TABLE cmSystemUserRole DROP COLUMN roleId;

ALTER TABLE cmSystemUser DROP COLUMN systemUserId;
ALTER TABLE cmRole DROP COLUMN roleId;

CREATE TABLE cmAccessRight (
  accessRightId int(11) NOT NULL auto_increment,
  parameters text NULL,
  roleName text NOT NULL,
  interceptionPointId int(11) NOT NULL,
  PRIMARY KEY  (accessRightId)
) TYPE=MyISAM;


DROP TABLE IF EXISTS cmRoleContentTypeDefinition;

CREATE TABLE cmRoleContentTypeDefinition (
  roleContentTypeDefinitionId int(11) NOT NULL auto_increment,
  roleName text NOT NULL,
  contentTypeDefinitionId int(11) NOT NULL default '0',
  PRIMARY KEY  (roleContentTypeDefinitionId)
) TYPE=MyISAM;


DROP TABLE IF EXISTS cmRoleProperties;

CREATE TABLE cmRoleProperties (
  rolePropertiesId int(11) NOT NULL auto_increment,
  roleName text NOT NULL,
  contentTypeDefinitionId int(11) NOT NULL default '0',
  value text NOT NULL,
  languageId int(11) NOT NULL default '0',
  PRIMARY KEY  (rolePropertiesId)
) TYPE=MyISAM;


DROP TABLE IF EXISTS cmUserContentTypeDefinition;

CREATE TABLE cmUserContentTypeDefinition (
  userContentTypeDefinitionId int(11) NOT NULL auto_increment,
  userName text NOT NULL,
  contentTypeDefinitionId int(11) NOT NULL default '0',
  PRIMARY KEY  (userContentTypeDefinitionId)
) TYPE=MyISAM;


DROP TABLE IF EXISTS cmUserProperties;

CREATE TABLE cmUserProperties (
  userPropertiesId int(11) NOT NULL auto_increment,
  userName text NOT NULL,
  contentTypeDefinitionId int(11) NOT NULL default '0',
  value text NOT NULL,
  languageId int(11) NOT NULL default '0',
  PRIMARY KEY  (userPropertiesId)
) TYPE=MyISAM;

CREATE TABLE cmInterceptionPoint (
  interceptionPointId int(11) NOT NULL auto_increment,
  category text NOT NULL,
  name text NOT NULL,
  description text NOT NULL,
  usesExtraDataForAccessControl int(11) default '0' NULL,
  PRIMARY KEY  (interceptionPointId)
) TYPE=MyISAM;


CREATE TABLE cmInterceptionPointInterceptor (
  interceptionPointId int(11) NOT NULL,
  interceptorId int(11) NOT NULL,
  PRIMARY KEY  (interceptionPointId, interceptorId)
) TYPE=MyISAM;


CREATE TABLE cmInterceptor (
  interceptorId int(11) NOT NULL auto_increment,
  name text NOT NULL,
  className text NOT NULL,
  description text NOT NULL,
  PRIMARY KEY  (interceptorId)
) TYPE=MyISAM;




CREATE TABLE OS_PROPERTYENTRY
(
	GLOBAL_KEY varchar(150) NOT NULL,
	ITEM_KEY varchar(150) NOT NULL,
	ITEM_TYPE tinyint,
	STRING_VALUE varchar(255),
	DATE_VALUE datetime,
	DATA_VALUE blob,
	FLOAT_VALUE float,
	NUMBER_VALUE numeric,
	primary key (GLOBAL_KEY, ITEM_KEY)
)TYPE=MyISAM;


CREATE TABLE OS_WFENTRY
(
    ID bigint NOT NULL,
    NAME varchar(60),
    STATE integer,
    primary key (ID)
)TYPE=MyISAM;


CREATE TABLE OS_CURRENTSTEP
(
    ID bigint NOT NULL,
    ENTRY_ID bigint,
    STEP_ID integer,
    ACTION_ID integer,
    OWNER varchar(35),
    START_DATE datetime,
    FINISH_DATE datetime,
    DUE_DATE datetime,
    STATUS varchar(40),
	CALLER varchar(35),
    primary key (ID),
    index (ENTRY_ID),
    foreign key (ENTRY_ID) references OS_WFENTRY(ID),
    index (OWNER),
    index (CALLER)
)TYPE=MyISAM;

CREATE TABLE OS_HISTORYSTEP
(
    ID bigint NOT NULL,
    ENTRY_ID bigint,
    STEP_ID integer,
    ACTION_ID integer,
    OWNER varchar(35),
    START_DATE datetime,
    FINISH_DATE datetime,
    DUE_DATE datetime,
    STATUS varchar(40),
    CALLER varchar(35),

    primary key (ID),
    index (ENTRY_ID),
    foreign key (ENTRY_ID) references OS_WFENTRY(ID),
    index (OWNER),
    index (CALLER)
)TYPE=MyISAM;

CREATE TABLE OS_CURRENTSTEP_PREV
(
    ID bigint NOT NULL,
    PREVIOUS_ID bigint NOT NULL,
    primary key (ID, PREVIOUS_ID),
    index (ID),
    foreign key (ID) references OS_CURRENTSTEP(ID),
    index (PREVIOUS_ID),
    foreign key (PREVIOUS_ID) references OS_HISTORYSTEP(ID)
)TYPE=MyISAM;

CREATE TABLE OS_HISTORYSTEP_PREV
(
    ID bigint NOT NULL,
    PREVIOUS_ID bigint NOT NULL,
    primary key (ID, PREVIOUS_ID),
    index (ID),
    foreign key (ID) references OS_HISTORYSTEP(ID),
    index (PREVIOUS_ID),
    foreign key (PREVIOUS_ID) references OS_HISTORYSTEP(ID)
)TYPE=MyISAM;

CREATE TABLE OS_STEPIDS
(
	 ID bigint NOT NULL AUTO_INCREMENT,
	 PRIMARY KEY (id)
 )TYPE=MyISAM;
 

INSERT INTO cmRole (roleName, description) VALUES
  ('anonymous','Must be present to model the default anonymous extranet role.');


INSERT INTO cmSystemUser (userName, password, firstName, lastName, email) VALUES
  ('anonymous','anonymous','Anonymous','User','anonymous@infoglue.org');


INSERT INTO cmSystemUserRole (userName, roleName) VALUES
  ('anonymous','anonymous');


INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (1,'Repository','Repository.Read','Gives a user access to look at a repository',1);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (2,'ManagementTool','ManagementTool.Read','Gives a user access to the management tool',0);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (3,'ContentTool','ContentTool.Read','Gives a user access to the content tool',0);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (4,'StructureTool','StructureTool.Read','Gives a user access to the structure tool',0);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (5,'PublishingTool','PublishingTool.Read','Gives a user access to the publishing tool',0);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (6,'Content','Content.Read','Intercepts the read of a content',1);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (7,'Content','Content.Write','Intercepts the write of a content',1);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (8,'SiteNodeVersion','SiteNodeVersion.Read','Intercepts the read of a SiteNodeVersion',1);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (9,'SiteNodeVersion','SiteNodeVersion.Write','Intercepts the write of a SiteNodeVersion',1);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (10,'Content','Content.Create','Intercepts the creation of a new content or folder',1);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (11,'Content','Content.Delete','Intercepts the deletion of a content',1);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (12,'Content','Content.Move','Intercepts the movement of a content',1);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (13,'Content','Content.SubmitToPublish','Intercepts the submittance to publish of all content versions',1);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (14,'Content','Content.ChangeAccessRights','Intercepts the attempt to change access rights',1);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (15,'Content','Content.CreateVersion','Intercepts the creation of a new contentversion',1);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (16,'ContentVersion','ContentVersion.Delete','Intercepts the deletion of a contentversion',1);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (17,'ContentVersion','ContentVersion.Write','Intercepts the editing of a contentversion',1);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (18,'ContentVersion','ContentVersion.Read','Intercepts the read of a contentversion',1);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (19,'SiteNodeVersion','SiteNodeVersion.CreateSiteNode','Intercepts the creation of a new sitenode',1);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (20,'SiteNodeVersion','SiteNodeVersion.DeleteSiteNode','Intercepts the deletion of a sitenode',1);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (21,'SiteNodeVersion','SiteNodeVersion.MoveSiteNode','Intercepts the movement of a sitenode',1);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (22,'SiteNodeVersion','SiteNodeVersion.SubmitToPublish','Intercepts the submittance to publish of all content versions',1);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (23,'SiteNodeVersion','SiteNodeVersion.ChangeAccessRights','Intercepts the attempt to change access rights',1);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES
  (24,'ContentVersion','ContentVersion.Publish','Intercepts the direct publishing of a content version',1);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES
  (25,'SiteNodeVersion','SiteNodeVersion.Publish','Intercepts the direct publishing of a siteNode version',1);

INSERT INTO cmInterceptionPoint (interceptionPointId, category, name, description, usesExtraDataForAccessControl) VALUES 
  (26,'MyDesktopTool','MyDesktopTool.Read','Gives a user access to the MyDesktop tool',0);



INSERT INTO cmInterceptor (interceptorId, name, className, description) VALUES
  (1,'InfoGlue Common Access Rights Interceptor','org.infoglue.cms.security.interceptors.InfoGlueCommonAccessRightsInterceptor','Takes care of bla');


INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (1, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (2, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (3, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (4, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (5, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (6, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (7, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (8, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (9, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (10, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (11, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (12, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (13, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (14, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (15, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (16, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (17, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (18, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (19, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (20, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (21, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (22, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (23, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (24, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (25, 1);

INSERT INTO cmInterceptionPointInterceptor (interceptionPointId, interceptorId) VALUES
  (26, 1);


INSERT INTO cmAccessRight (accessRightId, parameters, roleName, interceptionPointId) VALUES
  (1, '1', 'administrators', 1);

INSERT INTO cmAccessRight (accessRightId, parameters, roleName, interceptionPointId) VALUES
  (2, '1', 'cmsUser', 1);

INSERT INTO cmAccessRight (accessRightId, parameters, roleName, interceptionPointId) VALUES
  (3, '2', 'administrators', 1);

INSERT INTO cmAccessRight (accessRightId, parameters, roleName, interceptionPointId) VALUES
  (4, '2', 'cmsUser', 1);

INSERT INTO cmAccessRight (accessRightId, parameters, roleName, interceptionPointId) VALUES
  (5, '3', 'administrators', 1);

INSERT INTO cmAccessRight (accessRightId, parameters, roleName, interceptionPointId) VALUES
  (6, '3', 'cmsUser', 1);

INSERT INTO cmAccessRight (accessRightId, parameters, roleName, interceptionPointId) VALUES
  (7, '4', 'administrators', 1);

INSERT INTO cmAccessRight (accessRightId, parameters, roleName, interceptionPointId) VALUES
  (8, '4', 'cmsUser', 1);

INSERT INTO cmAccessRight (accessRightId, parameters, roleName, interceptionPointId) VALUES
  (9, NULL, 'administrators', 3);

INSERT INTO cmAccessRight (accessRightId, parameters, roleName, interceptionPointId) VALUES
  (10, NULL, 'cmsUser', 3);

INSERT INTO cmAccessRight (accessRightId, parameters, roleName, interceptionPointId) VALUES
  (11, NULL, 'administrators', 2);

INSERT INTO cmAccessRight (accessRightId, parameters, roleName, interceptionPointId) VALUES
  (12, NULL, 'cmsUser', 2);

INSERT INTO cmAccessRight (accessRightId, parameters, roleName, interceptionPointId) VALUES
  (13, NULL, 'administrators', 4);

INSERT INTO cmAccessRight (accessRightId, parameters, roleName, interceptionPointId) VALUES
  (14, NULL, 'cmsUser', 4);

INSERT INTO cmAccessRight (accessRightId, parameters, roleName, interceptionPointId) VALUES
  (15, NULL, 'administrators', 5);

INSERT INTO cmAccessRight (accessRightId, parameters, roleName, interceptionPointId) VALUES
  (16, NULL, 'cmsUser', 5);

INSERT INTO cmAccessRight (accessRightId, parameters, roleName, interceptionPointId) VALUES
  (17, NULL, 'administrators', 26);

INSERT INTO cmAccessRight (accessRightId, parameters, roleName, interceptionPointId) VALUES
  (18, NULL, 'cmsUser', 26);



CREATE INDEX serviceBindingId ON cmQualifyer(serviceBindingId);
CREATE INDEX serviceDefinitionId ON cmServiceBinding(serviceDefinitionId);
CREATE INDEX availableServiceBindingId ON cmServiceBinding(availableServiceBindingId);
CREATE INDEX siteNodeVersionId ON cmServiceBinding(siteNodeVersionId);
CREATE INDEX name ON cmContentTypeDefinition(name);
CREATE INDEX contentId ON cmContentVersion(contentId);
CREATE INDEX siteNodeId ON cmSiteNodeVersion(siteNodeId);

COMMIT;