CREATE TABLE IF NOT EXISTS `acl_classes` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `class_type` VARCHAR(200) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_69DD750638A36066` (`class_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `acl_entries` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `class_id` INT(10) UNSIGNED NOT NULL,
  `object_identity_id` INT(10) UNSIGNED DEFAULT NULL,
  `security_identity_id` INT(10) UNSIGNED NOT NULL,
  `field_name` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ace_order` SMALLINT(5) UNSIGNED NOT NULL,
  `mask` INT(11) NOT NULL,
  `granting` tinyint(1) NOT NULL,
  `granting_strategy` VARCHAR(30) COLLATE utf8_unicode_ci NOT NULL,
  `audit_success` tinyint(1) NOT NULL,
  `audit_failure` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_46C8B806EA000B103D9AB4A64DEF17BCE4289BF4` (`class_id`,`object_identity_id`,`field_name`,`ace_order`),
  KEY `IDX_46C8B806EA000B103D9AB4A6DF9183C9` (`class_id`,`object_identity_id`,`security_identity_id`),
  KEY `IDX_46C8B806EA000B10` (`class_id`),
  KEY `IDX_46C8B8063D9AB4A6` (`object_identity_id`),
  KEY `IDX_46C8B806DF9183C9` (`security_identity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;



CREATE TABLE IF NOT EXISTS `acl_object_identities` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_object_identity_id` INT(10) UNSIGNED DEFAULT NULL,
  `class_id` INT(10) UNSIGNED NOT NULL,
  `object_identifier` VARCHAR(100) COLLATE utf8_unicode_ci NOT NULL,
  `entries_inheriting` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_9407E5494B12AD6EA000B10` (`object_identifier`,`class_id`),
  KEY `IDX_9407E54977FA751A` (`parent_object_identity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `acl_object_identity_ancestors` (
  `object_identity_id` INT(10) UNSIGNED NOT NULL,
  `ancestor_id` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`object_identity_id`,`ancestor_id`),
  KEY `IDX_825DE2993D9AB4A6` (`object_identity_id`),
  KEY `IDX_825DE299C671CEA1` (`ancestor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



CREATE TABLE IF NOT EXISTS `acl_security_identities` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `identifier` VARCHAR(200) COLLATE utf8_unicode_ci NOT NULL,
  `username` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_8835EE78772E836AF85E0677` (`identifier`,`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;



CREATE TABLE IF NOT EXISTS `administradores` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(45) NOT NULL,
  `nome` VARCHAR(45) NOT NULL,
  `password` VARCHAR(120) NOT NULL,
  `salt` VARCHAR(32) NOT NULL,
  `roles` longtext NOT NULL COMMENT '(DC2Type:array)',
  `data_cadastro` datetime NOT NULL,
  `cro` VARCHAR(120) DEFAULT NULL,
  `curso` VARCHAR(120) DEFAULT NULL,
  `turma` VARCHAR(120) DEFAULT NULL,
  `coordenador` VARCHAR(120) DEFAULT NULL,
  `type` VARCHAR(120) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `atendimento` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `detalhes` text NOT NULL,
  `execucao_id` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_3FA50F2C892D0ABB` (`execucao_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;



CREATE TABLE IF NOT EXISTS `contrato` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `paciente_id` INT(11) DEFAULT NULL,
  `orcamento_id` INT(11) DEFAULT NULL,
  `data_cadastro` datetime NOT NULL,
  `cirurgiao_id` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_contrato_paciente1_idx` (`paciente_id`),
  KEY `fk_contrato_orcamento1_idx` (`orcamento_id`),
  KEY `fk_contrato_administradores1_idx` (`cirurgiao_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `execucao` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `procedimento_id` INT(11) DEFAULT NULL,
  `data_cadastro` datetime NOT NULL,
  `administradores_id` INT(11) DEFAULT NULL,
  `finalizado` tinyint(1) DEFAULT NULL,
  `atendimento_id` INT(11) DEFAULT NULL,
  `implante_id` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_139E7F7676323123` (`atendimento_id`),
  UNIQUE KEY `UNIQ_139E7F76957594D7` (`implante_id`),
  KEY `fk_execucao_procedimento1_idx` (`procedimento_id`),
  KEY `fk_execucao_administradores1_idx` (`administradores_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;



CREATE TABLE IF NOT EXISTS `implante` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `implante_tipo_id` INT(11) DEFAULT NULL,
  `implante_marca_id` INT(11) DEFAULT NULL,
  `detalhes` text NOT NULL,
  `medidas` VARCHAR(100) NOT NULL,
  `regiao` VARCHAR(45) NOT NULL,
  `recall` INT(11) NOT NULL,
  `execucao_id` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_A05F8138892D0ABB` (`execucao_id`),
  KEY `fk_implante_implante_tipo1_idx` (`implante_tipo_id`),
  KEY `fk_implante_implante_marca1_idx` (`implante_marca_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;



CREATE TABLE IF NOT EXISTS `implante_marca` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `implante_tipo` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;



CREATE TABLE IF NOT EXISTS `orcamento` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `paciente_id` INT(11) DEFAULT NULL,
  `data_cadastro` datetime NOT NULL,
  `valor` FLOAT NOT NULL,
  `parcelas` INT(11) NOT NULL,
  `valor_parcela` FLOAT NOT NULL,
  `forma_pagamento` VARCHAR(10) NOT NULL,
  `cheque` longtext COMMENT '(DC2Type:array)',
  `credito` longtext COMMENT '(DC2Type:array)',
  `debito` longtext COMMENT '(DC2Type:array)',
  PRIMARY KEY (`id`),
  KEY `fk_orcamento_pacientes_idx` (`paciente_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;



CREATE TABLE IF NOT EXISTS `paciente` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(100) NOT NULL,
  `rg` VARCHAR(15) NOT NULL,
  `cpf` VARCHAR(11) NOT NULL,
  `telefone` VARCHAR(11) NOT NULL,
  `celular` VARCHAR(11) DEFAULT NULL,
  `endereco` VARCHAR(300) NOT NULL,
  `cidade` VARCHAR(45) NOT NULL,
  `estado` VARCHAR(2) NOT NULL,
  `data_cadastro` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;



CREATE TABLE IF NOT EXISTS `procedimento` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `tipo_procedimento_id` INT(11) DEFAULT NULL,
  `regiao_procedimento_id` INT(11) DEFAULT NULL,
  `valor` FLOAT NOT NULL,
  `orcamento_id` INT(11) DEFAULT NULL,
  `quantidade` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_procedimento_tipo_procedimento1_idx` (`tipo_procedimento_id`),
  KEY `fk_procedimento_regiao_procedimento1_idx` (`regiao_procedimento_id`),
  KEY `IDX_CD974452A01CF1CA` (`orcamento_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;



CREATE TABLE IF NOT EXISTS `procedimento_regiao` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `procedimento_tipo` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(100) NOT NULL,
  `valor` DOUBLE DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;



ALTER TABLE `acl_entries`
  ADD CONSTRAINT `FK_46C8B8063D9AB4A6` FOREIGN KEY (`object_identity_id`) REFERENCES `acl_object_identities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `FK_46C8B806DF9183C9` FOREIGN KEY (`security_identity_id`) REFERENCES `acl_security_identities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `FK_46C8B806EA000B10` FOREIGN KEY (`class_id`) REFERENCES `acl_classes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `acl_object_identities`
  ADD CONSTRAINT `FK_9407E54977FA751A` FOREIGN KEY (`parent_object_identity_id`) REFERENCES `acl_object_identities` (`id`);

ALTER TABLE `acl_object_identity_ancestors`
  ADD CONSTRAINT `FK_825DE2993D9AB4A6` FOREIGN KEY (`object_identity_id`) REFERENCES `acl_object_identities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `FK_825DE299C671CEA1` FOREIGN KEY (`ancestor_id`) REFERENCES `acl_object_identities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `atendimento`
  ADD CONSTRAINT `FK_3FA50F2C892D0ABB` FOREIGN KEY (`execucao_id`) REFERENCES `execucao` (`id`);

ALTER TABLE `contrato`
  ADD CONSTRAINT `fk_contrato_administradores1` FOREIGN KEY (`cirurgiao_id`) REFERENCES `administradores` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_contrato_orcamento1` FOREIGN KEY (`orcamento_id`) REFERENCES `orcamento` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_contrato_paciente1` FOREIGN KEY (`paciente_id`) REFERENCES `paciente` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `execucao`
  ADD CONSTRAINT `FK_139E7F7676323123` FOREIGN KEY (`atendimento_id`) REFERENCES `atendimento` (`id`),
  ADD CONSTRAINT `FK_139E7F76957594D7` FOREIGN KEY (`implante_id`) REFERENCES `implante` (`id`),
  ADD CONSTRAINT `fk_execucao_administradores1` FOREIGN KEY (`administradores_id`) REFERENCES `administradores` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_execucao_procedimento1` FOREIGN KEY (`procedimento_id`) REFERENCES `procedimento` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `implante`
  ADD CONSTRAINT `FK_A05F8138892D0ABB` FOREIGN KEY (`execucao_id`) REFERENCES `execucao` (`id`),
  ADD CONSTRAINT `fk_implante_implante_marca1` FOREIGN KEY (`implante_marca_id`) REFERENCES `implante_marca` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_implante_implante_tipo1` FOREIGN KEY (`implante_tipo_id`) REFERENCES `implante_tipo` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `orcamento`
  ADD CONSTRAINT `fk_orcamento_pacientes` FOREIGN KEY (`paciente_id`) REFERENCES `paciente` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `procedimento`
  ADD CONSTRAINT `FK_CD974452A01CF1CA` FOREIGN KEY (`orcamento_id`) REFERENCES `orcamento` (`id`),
  ADD CONSTRAINT `fk_procedimento_regiao_procedimento1` FOREIGN KEY (`regiao_procedimento_id`) REFERENCES `procedimento_regiao` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_procedimento_tipo_procedimento1` FOREIGN KEY (`tipo_procedimento_id`) REFERENCES `procedimento_tipo` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;