

CREATE TABLE `holding` (
  `idHolding` int NOT NULL AUTO_INCREMENT,
  `nome` varchar(120) NOT NULL,
  `telefone` varchar(15) DEFAULT NULL,
  `cnpj` varchar(18) NOT NULL,
  `im` varchar(30) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `cep` varchar(9) DEFAULT NULL,
  `rua` varchar(120) DEFAULT NULL,
  `uf` char(2) DEFAULT NULL,
  `cidade` varchar(60) DEFAULT NULL,
  `bairro` varchar(60) DEFAULT NULL,
  `numero` varchar(10) DEFAULT NULL,
  `complemento` varchar(60) DEFAULT NULL,
  `status` ENUM('Ativo','Inativo') NOT NULL DEFAULT 'Ativo',
  PRIMARY KEY (`idHolding`),
  UNIQUE KEY `cnpj` (`cnpj`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO holding (nome, telefone, cnpj, im, email, cep, rua, uf, cidade, bairro, numero, complemento) VALUES
('Empresa 1', '31935984080', '46603679000104', '446453', 'deltatech@example.com', '37035607', 'Av. Contorno', 'MG', 'Belo Horizonte', 'Lourdes', 1335, 'Bloco B'),
('Empresa 2', '31949250864', '65218679000135', '746265', 'upsiloncorp@example.com', '36161276', 'Rua Santa Catarina', 'MG', 'Belo Horizonte', 'Cidade Jardim', 6765, 'Galpão A'),
('Empresa 3', '31904307898', '21368765000159', '199174', 'xiltda@example.com', '39877308', 'Av. Contorno', 'MG', 'Belo Horizonte', 'Savassi', 2312, 'Loja 5');

CREATE TABLE `associar_user` (
  `idAssociarUser` INT NOT NULL AUTO_INCREMENT,
  `idHolding` INT NOT NULL,
  `idUsuario` INT NOT NULL,
  PRIMARY KEY (`idAssociarUser`),
  CONSTRAINT `fk_associar_user_holding`
    FOREIGN KEY (`idHolding`) REFERENCES `holding` (`idHolding`)
    ON DELETE CASCADE,
  CONSTRAINT `fk_associar_user_usuario`
    FOREIGN KEY (`idUsuario`) REFERENCES `usuario` (`idUsuario`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `holding_arquivo` (
  `idHoldingArquivo` INT NOT NULL AUTO_INCREMENT,
  `idHolding` INT NOT NULL,
  `nomeArquivoEditado` VARCHAR(120) NOT NULL,
  `nomeArquivoOriginal` VARCHAR(120) NOT NULL,
  `identificador` VARCHAR(120) NOT NULL,
  PRIMARY KEY (`idHoldingArquivo`),
  CONSTRAINT `fk_holding_arquivo_holding` 
    FOREIGN KEY (`idHolding`)
    REFERENCES `holding`(`idHolding`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `imobiliaria` (
  `idImobiliaria` int NOT NULL AUTO_INCREMENT,
  `idHolding` INT NOT NULL,
  `nome` varchar(120) NOT NULL,
  `telefone` varchar(15) DEFAULT NULL,
  `cnpj` varchar(18) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `nomeContato` varchar(120) DEFAULT NULL,
  `status` ENUM('Ativo','Inativo') NOT NULL DEFAULT 'Ativo',
  `site` varchar(120) DEFAULT NULL,
  PRIMARY KEY (`idImobiliaria`),
  CONSTRAINT `fk_imobiliaria_holding` 
    FOREIGN KEY (`idHolding`)
    REFERENCES `holding`(`idHolding`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `imovel` (
  `idImovel` INT NOT NULL AUTO_INCREMENT,
  `idHolding` INT NOT NULL,
  `nome` VARCHAR(120) NOT NULL,
  `cep` VARCHAR(9) DEFAULT NULL,
  `rua` VARCHAR(120) DEFAULT NULL,
  `uf` CHAR(2) DEFAULT NULL,
  `cidade` VARCHAR(60) DEFAULT NULL,
  `bairro` VARCHAR(60) DEFAULT NULL,
  `numero` VARCHAR(10) DEFAULT NULL,
  `complemento` VARCHAR(60) DEFAULT NULL,
  `status` ENUM('Ativo','Vendido') NOT NULL DEFAULT 'Ativo',
  `tipo` ENUM('Casa','Loja','Galpão','Lote','APTO','Flat','Outro') NOT NULL DEFAULT 'Outro',
  `inscricaoCadastral` BIGINT DEFAULT NULL,
  `numeroMatricula` VARCHAR(100) DEFAULT NULL,
  `possuiRegistro` ENUM('Sim','Não') DEFAULT 'Não',
  `valorImovel` DECIMAL(12,2) DEFAULT NULL,
  `valorIptu` DECIMAL(10,2) DEFAULT NULL,
  `valorCondominio` DECIMAL(10,2) DEFAULT NULL,
  `identificadorEnergia` BIGINT DEFAULT NULL,
  `identificadorAgua` BIGINT DEFAULT NULL,
  `obsImovel` TEXT,
  PRIMARY KEY (`idImovel`),
  CONSTRAINT `fk_imovel_holding`
    FOREIGN KEY (`idHolding`) REFERENCES `holding`(`idHolding`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `imovel_arquivo` (
  `idImovelArquivo` INT NOT NULL AUTO_INCREMENT,
  `idImovel` INT NOT NULL,
  `nomeArquivoEditado` VARCHAR(120) NOT NULL,
  `nomeArquivoOriginal` VARCHAR(120) NOT NULL,
  `identificador` VARCHAR(120) NOT NULL,
  PRIMARY KEY (`idImovelArquivo`),
  CONSTRAINT `fk_arquivo_imovel` 
    FOREIGN KEY (`idImovel`)
    REFERENCES `imovel`(`idImovel`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `imovel_foto` (
  `idImovelFoto` INT NOT NULL AUTO_INCREMENT,
  `idImovel` INT NOT NULL,
  `nomeFotoOriginal` VARCHAR(120) NOT NULL,
  `identificadorFoto` VARCHAR(120) NOT NULL,
  `principal` ENUM('Sim', 'Não') NULL DEFAULT 'Não',
  PRIMARY KEY (`idImovelFoto`),
  CONSTRAINT `fk_foto_imovel` 
    FOREIGN KEY (`idImovel`)
    REFERENCES `imovel`(`idImovel`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `mobilia` (
  `idMobilia` int NOT NULL AUTO_INCREMENT,
  `nomeMobilia` varchar(120) DEFAULT NULL,
  PRIMARY KEY (`idMobilia`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `associar_mobilia` (
  `idAssociarMobilia` INT NOT NULL AUTO_INCREMENT,
  `idImovel` INT NOT NULL,
  `idMobilia` INT NOT NULL,
  PRIMARY KEY (`idAssociarMobilia`),
  CONSTRAINT `fk_associar_mobilia_imovel`
    FOREIGN KEY (`idImovel`) REFERENCES `imovel` (`idImovel`)
    ON DELETE CASCADE,
  CONSTRAINT `fk_associar_mobilia_mobilia`
    FOREIGN KEY (`idMobilia`) REFERENCES `mobilia` (`idMobilia`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `aluguel` (
  `idAluguel` INT NOT NULL AUTO_INCREMENT,
  `idImovel` INT NOT NULL,
  `idImobiliaria` INT DEFAULT NULL,
  `nomeInquilino` VARCHAR(120) NOT NULL,
  `valorAluguel` DECIMAL(12,2) DEFAULT NULL,
  `taxaAdm` DECIMAL(5,2) DEFAULT NULL,
  `dtInicioContrato` DATETIME DEFAULT NULL,
  `dtFimContrato` DATETIME DEFAULT NULL,
  `dtEncerramento` DATETIME DEFAULT NULL,
  `diaVencimentoAluguel` INT DEFAULT NULL,
  `diaRepasse` INT DEFAULT NULL,
  `situacao` ENUM('Ativo', 'Finalizado') NULL DEFAULT 'Ativo',
  `obsAluguel` TEXT,
  PRIMARY KEY (`idAluguel`),
  CONSTRAINT `fk_aluguel_imovel`
    FOREIGN KEY (`idImovel`) REFERENCES `imovel` (`idImovel`)
    ON DELETE CASCADE,
  CONSTRAINT `fk_aluguel_imobiliaria`
    FOREIGN KEY (`idImobiliaria`) REFERENCES `imobiliaria` (`idImobiliaria`)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `aluguel_arquivo` (
  `idAluguelArquivo` INT NOT NULL AUTO_INCREMENT,
  `idAluguel` INT NOT NULL,
  `nomeArquivoEditado` VARCHAR(120) NOT NULL,
  `nomeArquivoOriginal` VARCHAR(120) NOT NULL,
  `identificador` VARCHAR(120) NOT NULL,
  PRIMARY KEY (`idAluguelArquivo`),
  CONSTRAINT `fk_arquivo_aluguel` 
    FOREIGN KEY (`idAluguel`)
    REFERENCES `aluguel`(`idAluguel`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `pagamento` (
  `idPagamento` INT NOT NULL AUTO_INCREMENT,
  `idAluguel` INT NOT NULL,
  `mesReferencia` INT NOT NULL,
  `anoReferencia` INT NOT NULL,
  `valorPago` DECIMAL(12,2) DEFAULT NULL,
  `dtPagamento` DATETIME DEFAULT NULL,
  `obsPagamento` TEXT,
  PRIMARY KEY (`idPagamento`),
  CONSTRAINT `fk_pagamento_aluguel`
    FOREIGN KEY (`idAluguel`) REFERENCES `aluguel` (`idAluguel`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


INSERT INTO mobilia (nomeMobilia) VALUES
("Geladeira "),
("Cooktop"),
("Purificador de água "),
("Microondas "),
("Exaustor "),
("Cadeiras"),
("Mesa"),
("Banquetas"),
("Sofá "),
("Painel de TV "),
("TV"),
("Ar condicionado "),
("Cama box"),
("Cortina"),
("Armário de quarto"),
("Armário superior cozinha"),
("Armário de pia"),
("Armário pia banheiro"),
("Espelho"),
("Box"),
("Chuveiro"),
("Ducha higiênica "),
("Chave eletrônica"),
("Motor eletrônico de portão"),
("Interfone"),
("Alarme");

