DROP TABLE IF EXISTS `CARD`;
CREATE TABLE `CARD` (
  `ID_INTERNAL` int(11) unsigned NOT NULL auto_increment,
  `ACK_OPT_LOCK` int(11) unsigned NOT NULL default '1',
  `CARD_ID` int(11) unsigned NOT NULL,
  `TYPE` VARCHAR(50) NOT NULL,
   KEY_ROOT_DOMAIN_OBJECT int(11) not null default 1,
   PRIMARY KEY  (`ID_INTERNAL`),
   UNIQUE KEY `u1` (`CARD_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE IDENTIFICATION change column USERNAME USERNAME varchar(50) default NULL;

DROP TABLE IF EXISTS `JUSTIFICATION_MOTIVE`;
CREATE TABLE `JUSTIFICATION_MOTIVE` (
  `ID_INTERNAL` int(11) unsigned NOT NULL auto_increment,
  `ACK_OPT_LOCK` int(11) unsigned NOT NULL default '1',
  `ACRONYM` VARCHAR(10) NOT NULL,
  `DESCRIPTION` VARCHAR(50) NOT NULL,
  `JUSTIFICATION_TYPE` VARCHAR(50),
  `DAY_TYPE` VARCHAR(50),
  `JUSTIFICATION_GROUP` VARCHAR(50),
  `LAST_MODIFIED_DATE` TIMESTAMP,
  `KEY_MODIFIED_BY` int(11) unsigned,
  `KEY_ROOT_DOMAIN_OBJECT` int(11) not null default 1,
   PRIMARY KEY  (`ID_INTERNAL`),
   UNIQUE KEY `u1` (`ACRONYM`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `ASSIDUOUSNESS`;
CREATE TABLE `ASSIDUOUSNESS` (
  `ID_INTERNAL` int(11) unsigned NOT NULL auto_increment,
  `ACK_OPT_LOCK` int(11) unsigned NOT NULL default '1',
  `KEY_EMPLOYEE` int(11) unsigned NOT NULL,
  KEY_ROOT_DOMAIN_OBJECT int(11) not null default 1,
  PRIMARY KEY  (`ID_INTERNAL`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `ASSIDUOUSNESS_RECORD`;
CREATE TABLE `ASSIDUOUSNESS_RECORD` (
    `ID_INTERNAL` int(11) unsigned NOT NULL auto_increment,
    `ACK_OPT_LOCK` int(11) unsigned NOT NULL default '1',
    `KEY_ASSIDUOUSNESS` int(11) unsigned NOT NULL,
	`DATE` TIMESTAMP NOT NULL,
	`DURATION` BIGINT,
	`NOTES` VARCHAR(255),
	`OJB_CONCRETE_CLASS` varchar(255),
    `KEY_JUSTIFICATION_MOTIVE` int(11) unsigned,
    `LAST_MODIFIED_DATE` TIMESTAMP,
	`KEY_MODIFIED_BY` int(11) unsigned,
    `KEY_CLOCK_UNIT` int(11) unsigned,
    KEY_ROOT_DOMAIN_OBJECT int(11) not null default 1,
    PRIMARY KEY  (`ID_INTERNAL`)
)  ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `CLOCK_UNIT`;
CREATE TABLE `CLOCK_UNIT` (
    `ID_INTERNAL` int(11) unsigned NOT NULL auto_increment,
    `ACK_OPT_LOCK` int(11) unsigned NOT NULL default '1',
    `ACRONYM` VARCHAR(10) default NULL,
    `NAME` VARCHAR(50) default NULL,
    KEY_ROOT_DOMAIN_OBJECT int(11) not null default 1,
    PRIMARY KEY  (`ID_INTERNAL`)
)  ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `ASSIDUOUSNESS_STATUS`;
CREATE TABLE ASSIDUOUSNESS_STATUS (
    `ID_INTERNAL` int(11) unsigned NOT NULL auto_increment,
    `ACK_OPT_LOCK` int(11) unsigned NOT NULL default '1',
    `DESCRIPTION` VARCHAR(50) default NULL,
    `STATE` VARCHAR(50) NOT NULL,
    `LAST_MODIFIED_DATE` TIMESTAMP,
    `KEY_MODIFIED_BY` int(11) unsigned,
    KEY_ROOT_DOMAIN_OBJECT int(11) not null default 1,
    PRIMARY KEY  (`ID_INTERNAL`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
	

DROP TABLE IF EXISTS `ASSIDUOUSNESS_STATUS_HISTORY`;
CREATE TABLE ASSIDUOUSNESS_STATUS_HISTORY (
    `ID_INTERNAL` int(11) unsigned NOT NULL auto_increment,
    `ACK_OPT_LOCK` int(11) unsigned NOT NULL default '1',
	`KEY_ASSIDUOUSNESS_STATUS` int(11) unsigned NOT NULL,
	`KEY_ASSIDUOUSNESS` int(11) unsigned NOT NULL,
	`BEGIN_DATE` VARCHAR(10) NOT NULL,
	`END_DATE` VARCHAR(10),
    `LAST_MODIFIED_DATE` DATETIME,
    `KEY_MODIFIED_BY` int(11) unsigned,
    `KEY_ROOT_DOMAIN_OBJECT` int(11) not null default 1,
    PRIMARY KEY  (`ID_INTERNAL`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
	
ALTER TABLE ass_FUNCIONARIO ADD COLUMN KEY_ASSIDUOUSNESS int(11) unsigned NOT NULL;	

DROP TABLE IF EXISTS `SCHEDULE`;
CREATE TABLE SCHEDULE (
  `ID_INTERNAL` int(11) unsigned NOT NULL auto_increment,
  `ACK_OPT_LOCK` int(11) unsigned NOT NULL default '1',
  `KEY_ASSIDUOUSNESS` int(11) unsigned NOT NULL,
  `BEGIN_DATE` VARCHAR(50) NOT NULL,
  `END_DATE` VARCHAR(50),
  `EXCEPTION` tinyint(1) NOT NULL DEFAULT 0,  
  `LAST_MODIFIED_DATE` DATETIME,
  `KEY_MODIFIED_BY` int(11) unsigned,  
  `KEY_ROOT_DOMAIN_OBJECT` int(11) not null default 1,
  PRIMARY KEY  (`ID_INTERNAL`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `SCHEDULE_WORK_SCHEDULE`;
CREATE TABLE SCHEDULE_WORK_SCHEDULE (
    `ID_INTERNAL` int(11) unsigned NOT NULL auto_increment,
    `ACK_OPT_LOCK` int(11) unsigned NOT NULL default '1',
    `KEY_SCHEDULE` int(11) unsigned NOT NULL,
    `KEY_WORK_SCHEDULE` int(11) unsigned NOT NULL,
    `KEY_ROOT_DOMAIN_OBJECT` int(11) not null default 1,
    PRIMARY KEY  (`ID_INTERNAL`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `WORK_SCHEDULE`;
CREATE TABLE WORK_SCHEDULE (
    `ID_INTERNAL` int(11) unsigned NOT NULL auto_increment,
    `ACK_OPT_LOCK` int(11) unsigned NOT NULL default '1',
    `KEY_PERIODICITY` int(11) unsigned NOT NULL,
    `KEY_WORK_SCHEDULE_TYPE` int(11) unsigned NOT NULL,
    `WORK_WEEK` text NOT NULL,
    `KEY_ROOT_DOMAIN_OBJECT` int(11) not null default 1,
    PRIMARY KEY  (`ID_INTERNAL`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `PERIODICITY`;
CREATE TABLE PERIODICITY (
  `ID_INTERNAL` int(11) unsigned NOT NULL auto_increment,
  `ACK_OPT_LOCK` int(11) unsigned NOT NULL default '1',
  `WORK_WEEK_NUMBER` int(2) unsigned NOT NULL default '1',
  `KEY_ROOT_DOMAIN_OBJECT` int(11) not null default 1,
  PRIMARY KEY  (`ID_INTERNAL`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `WORK_PERIOD`;
CREATE TABLE WORK_PERIOD (
    `ID_INTERNAL` int(11) unsigned NOT NULL auto_increment,
    `ACK_OPT_LOCK` int(11) unsigned NOT NULL default '1',
    `FIRST_PERIOD` TIME NOT NULL,
    `FIRST_PERIOD_DURATION` BIGINT NOT NULL,
    `SECOND_PERIOD` TIME,
    `SECOND_PERIOD_DURATION` BIGINT,
    `KEY_ROOT_DOMAIN_OBJECT` int(11) not null default 1,
    PRIMARY KEY  (`ID_INTERNAL`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `MEAL`;
CREATE TABLE MEAL (
    `ID_INTERNAL` int(11) unsigned NOT NULL auto_increment,
    `ACK_OPT_LOCK` int(11) unsigned NOT NULL default '1',
    `BEGIN_MEAL_BREAK` TIME NOT NULL,
    `END_MEAL_BREAK` TIME NOT NULL,
    `MANDATORY_MEAL_DISCOUNT` BIGINT,
    `MINIMUM_MEAL_BREAK_INTERVAL` BIGINT,
    `KEY_ROOT_DOMAIN_OBJECT` int(11) not null default 1,
    PRIMARY KEY  (`ID_INTERNAL`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `WORK_SCHEDULE_TYPE`;
CREATE TABLE WORK_SCHEDULE_TYPE (
    `ID_INTERNAL` int(11) unsigned NOT NULL auto_increment,
    `ACK_OPT_LOCK` int(11) unsigned NOT NULL default '1',
    `OJB_CONCRETE_CLASS` varchar(255),
    `ACRONYM` VARCHAR(10) default NULL,
    `WORK_TIME` TIME NOT NULL,
    `WORK_TIME_DURATION` BIGINT NOT NULL,
    `CLOCKING_TIME` TIME NOT NULL,
    `CLOCKING_TIME_DURATION` BIGINT NOT NULL,
    `KEY_NORMAL_WORK_PERIOD` int(11) unsigned,
    `KEY_FIXED_WORK_PERIOD` int(11) unsigned,
    `KEY_MEAL` int(11) unsigned,
    `BEGIN_VALID_DATE` VARCHAR(10) NOT NULL,
    `END_VALID_DATE` VARCHAR(10) NOT NULL,
    `LAST_MODIFIED_DATE` TIMESTAMP,
	`KEY_MODIFIED_BY` int(11) unsigned,
    `KEY_ROOT_DOMAIN_OBJECT` int(11) not null default 1,
    PRIMARY KEY  (`ID_INTERNAL`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

