
START TRANSACTION;



--
-- Banco de dados: `vistoriadynamost_VISTORIA`
--

-- --------------------------------------------------------

CREATE TABLE `enderecos` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `logradouro` VARCHAR(255) NOT NULL,
    `numero` VARCHAR(50),
    `complemento` VARCHAR(100),
    `bairro` VARCHAR(100),
    `cidade` VARCHAR(100) NOT NULL,
    `uf` VARCHAR(2) NOT NULL,
    `cep` VARCHAR(20) NOT NULL,
    `pais` VARCHAR(50) NOT NULL DEFAULT 'BRASIL',
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` datetime DEFAULT NULL,
    `deleted_at` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `planos` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `nome` VARCHAR(100) NOT NULL,
    `valor` INT(11) NOT NULL,
    `limite_vistorias` INT(11) NOT NULL,
    `descricao` TEXT,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT NULL,
    `deleted_at` DATETIME DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `imobiliarias` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nome` varchar(100) NOT NULL,
  `telefone` varchar(20),
  `email` varchar(100),
  `cnpj` varchar(20),
  `endereco_id` int(11) NOT NULL,
  `plano_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`endereco_id`) REFERENCES `enderecos`(`id`),
  FOREIGN KEY (`plano_id`) REFERENCES `planos`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tipos_imoveis` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `nome` VARCHAR(255) NOT NULL,
    `descricao` TEXT,
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` datetime DEFAULT NULL,
    `deleted_at` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `subtipos_imoveis` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `nome` VARCHAR(255) NOT NULL,
    `descricao` TEXT,
    `tipo_imovel_id` INT(11) NOT NULL,
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` datetime DEFAULT NULL,
    `deleted_at` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`tipo_imovel_id`) REFERENCES `tipos_imoveis`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `imoveis` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `user_id` int(11) NOT NULL,
    `subtipo_imovel_id` int(11) NOT NULL,
    `endereco_id` int(11) NOT NULL,
    `foto_capa` VARCHAR(255),
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` datetime DEFAULT NULL,
    `deleted_at` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`),
    FOREIGN KEY (`subtipo_imovel_id`) REFERENCES `subtipos_imoveis`(`id`),
    FOREIGN KEY (`endereco_id`) REFERENCES `enderecos`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `ambientes` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `nome` VARCHAR(255) NOT NULL,
    `descricao` TEXT,
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` datetime DEFAULT NULL,
    `deleted_at` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `vistorias` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `imovel_id` INT(11) NOT NULL,
    `user_id` INT(11) NOT NULL,
    `locador` VARCHAR(200),
    `locatario` VARCHAR(200),
    `data_vistoria` DATE NOT NULL,
    `status` ENUM('pendente', 'concluida', 'cancelada') DEFAULT 'pendente',
    `observacoes` TEXT,
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` datetime DEFAULT NULL,
    `deleted_at` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`imovel_id`) REFERENCES `imoveis`(`id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `ambientes_vistorias` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `vistoria_id` INT(11) NOT NULL,
    `ambiente_id` INT(11) NOT NULL,
    `estado` VARCHAR(255) NOT NULL,
    `qtd` INT(11) NOT NULL DEFAULT 1,
    `observacoes` TEXT,
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` datetime DEFAULT NULL,
    `deleted_at` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`vistoria_id`) REFERENCES `vistorias`(`id`),
    FOREIGN KEY (`ambiente_id`) REFERENCES `ambientes`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `ambientes_vistorias_fotos` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `ambientes_vistoria_id` INT(11) NOT NULL,
    `caminho_foto` VARCHAR(255) NOT NULL,
    `descricao` TEXT,
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` datetime DEFAULT NULL,
    `deleted_at` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`ambientes_vistoria_id`) REFERENCES `ambientes_vistorias`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `itens` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(255) NOT NULL,
  `ambiente_id` INT(11) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT NULL,
  `deleted_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
    FOREIGN KEY (`ambiente_id`) REFERENCES `ambientes`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `ambientes_itens_vistorias` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `vistoria_id` INT(11) NOT NULL,
    `ambiente_id` INT(11) NOT NULL,
    `item_id` INT(11) NOT NULL,
    `estado` VARCHAR(255) NOT NULL,
    `observacoes` TEXT,
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` datetime DEFAULT NULL,
    `deleted_at` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`vistoria_id`) REFERENCES `vistorias`(`id`),
    FOREIGN KEY (`ambiente_id`) REFERENCES `ambientes`(`id`),
    FOREIGN KEY (`item_id`) REFERENCES `itens`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

COMMIT;

