SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema db_boilerplate
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema db_boilerplate
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `db_boilerplate` DEFAULT CHARACTER SET latin1 ;
USE `db_boilerplate` ;


-- -----------------------------------------------------
-- Table `db_boilerplate`.`tab_civil_status`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `db_boilerplate`.`tab_civil_status` (
  `civil_status_id` INT NOT NULL AUTO_INCREMENT,
  `civil_status_name` VARCHAR(30) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`civil_status_id`),
  UNIQUE INDEX `name_UNIQUE` (`civil_status_name` ASC))
ENGINE = InnoDB;



-- -----------------------------------------------------
-- Table `db_boilerplate`.`tab_state`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `db_boilerplate`.`tab_state` (
  `state_id` INT NOT NULL AUTO_INCREMENT,
  `state_name` VARCHAR(20) NOT NULL,
  `acronym` VARCHAR(2) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`state_id`),
  UNIQUE INDEX `acronym_UNIQUE` (`acronym` ASC))
ENGINE = InnoDB;



-- -----------------------------------------------------
-- Table `db_boilerplate`.`tab_city`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `db_boilerplate`.`tab_city` (
  `city_id` INT NOT NULL AUTO_INCREMENT,
  `city_name` VARCHAR(250) NOT NULL,
  `state_id` INT NOT NULL,
  `ibge_code` VARCHAR(8) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`city_id`),
  INDEX `fk_tab_city_tab_state1_idx` (`state_id` ASC),
  UNIQUE INDEX `name_state_UNIQUE` (`city_name` ASC, `state_id` ASC),
  CONSTRAINT `fk_tab_city_tab_state1`
    FOREIGN KEY (`state_id`)
    REFERENCES `db_boilerplate`.`tab_state` (`state_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;