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';
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `truckcode` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
USE `truckcode`;
-- -----------------------------------------------------
-- Table `truckcode`.`sistema_propietario`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `truckcode`.`sistema_propietario` (
    `rut` VARCHAR(14) NOT NULL,
    `razon_social` VARCHAR(200) NOT NULL ,
    `nombre_fantasia` VARCHAR(200) NOT NULL ,
    `direccion` VARCHAR(200) NOT NULL ,
    `telefono` VARCHAR(30) NOT NULL ,
    `ciudad` VARCHAR(30) NOT NULL ,
    `comuna` VARCHAR(30) NOT NULL ,
    `giro` VARCHAR(200) NOT NULL ,
    `mail` VARCHAR(100) NOT NULL ,
    `web` VARCHAR(100) NULL ,
    `fax` VARCHAR(30) NULL ,
    PRIMARY KEY (`rut`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = DEFAULT
ROW_FORMAT = DEFAULT;
-- ----------------------------------------------------
-- Table `truckcode`.`sistema_usuarios`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `truckcode`.`sistema_usuarios` (
    `id` INT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
    `creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `creador` INT(3) UNSIGNED NULL,
    `nombre` VARCHAR(100) NOT NULL ,
    `mail` VARCHAR(100) NOT NULL ,
    `empresa` VARCHAR(30) NULL,
    `clave` VARCHAR(100) NOT NULL ,
    `tipo` INT(2) UNSIGNED NOT NULL ,
    `valido_hasta` DATE NULL DEFAULT NULL,
    `privilegios` TEXT NULL,
    `estado` INT(1) UNSIGNED NOT NULL DEFAULT '1',
    PRIMARY KEY (`id`) ,
    UNIQUE INDEX `unique_usuario` (`mail` ASC),
    INDEX `fk_sistema_usuarios_usuarios` (`creador` ASC) ,
    CONSTRAINT `fk_sistema_usuarios_usuarios`
        FOREIGN KEY (`creador` )
        REFERENCES `truckcode`.`sistema_usuarios` (`id` )
        ON DELETE SET NULL 
        ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `truckcode`.`sistema_preferencias`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `truckcode`.`sistema_preferencias` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    `nombre` VARCHAR(45) NOT NULL,
    `valor` FLOAT UNSIGNED NOT NULL ,
    `validador` VARCHAR(100) NOT NULL,
    `label` VARCHAR(50) NOT NULL,
    `descripcion` TEXT NULL,
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `truckcode`.`sistema_informes_preconfig`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `truckcode`.`sistema_informes_preconfig` (
    `id` INT(2) UNSIGNED NOT NULL AUTO_INCREMENT ,
    `nombre` VARCHAR(100) NOT NULL,
    `tipo` VARCHAR(50) NOT NULL,
    `base` VARCHAR(50) NOT NULL,
    `modelo` VARCHAR(50) NOT NULL,
    `parametros` TEXT NOT NULL,
    `estado` INT(1) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `truckcode`.`sistema_mensajes_auto`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `truckcode`.`sistema_mensajes_auto` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    `creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `creador` INT(3) UNSIGNED NULL,
    `usuario_id` INT(2) UNSIGNED NOT NULL,
    `informe_id` INT(2) UNSIGNED NOT NULL,
    `dia` INT(2) UNSIGNED NOT NULL,
    `con_copia` TEXT NULL,
    `con_copia_oculta` TEXT NULL,
    `mensaje` TEXT NULL ,
    PRIMARY KEY (`id`),
    INDEX `fk_sistema_mensajes_auto_usuarios` (`creador` ASC) ,
    INDEX `fk_sistema_mensajes_auto_destinatarios` (`usuario_id` ASC) ,
    INDEX `fk_sistema_mensajes_auto_informes` (`informe_id` ASC) ,
    CONSTRAINT `fk_sistema_mensajes_auto_usuarios`
        FOREIGN KEY (`creador` )
        REFERENCES `truckcode`.`sistema_usuarios` (`id` )
        ON DELETE SET NULL 
        ON UPDATE CASCADE,
    CONSTRAINT `fk_sistema_mensajes_auto_destinatarios`
        FOREIGN KEY (`usuario_id` )
        REFERENCES `truckcode`.`sistema_usuarios` (`id` )
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_sistema_mensajes_auto_informes`
        FOREIGN KEY (`informe_id` )
        REFERENCES `truckcode`.`sistema_informes_preconfig` (`id` )
        ON DELETE CASCADE
        ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `truckcode`.`sistema_mensajes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `truckcode`.`sistema_mensajes` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `creador` INT(3) UNSIGNED NULL,
    `informe_base` VARCHAR(50) NOT NULL,
    `informe_parametros` TEXT NOT NULL,
    `nombre` VARCHAR(200) NOT NULL,
    `mail` VARCHAR(200) NOT NULL,
    PRIMARY KEY (`id`) ,
    INDEX `fk_sistema_mensajes_usuarios` (`creador` ASC) ,
    CONSTRAINT `fk_sistema_mensajes_usuarios`
        FOREIGN KEY (`creador` )
        REFERENCES `truckcode`.`sistema_usuarios` (`id` )
        ON DELETE SET NULL 
        ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `truckcode`.`sistema_log_ingresos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `truckcode`.`sistema_log_ingresos` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `usuario_id` INT(2) UNSIGNED NOT NULL,
    `session_id` VARCHAR(50) NULL,
    `ip_address` VARCHAR(20) NULL,
    PRIMARY KEY (`id`) ,
    INDEX `fk_sistema_log_ingresos_usuarios` (`usuario_id` ASC) ,
    CONSTRAINT `fk_sistema_log_ingresos_usuarios`
        FOREIGN KEY (`usuario_id` )
        REFERENCES `truckcode`.`sistema_usuarios` (`id` )
        ON DELETE CASCADE  
        ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `truckcode`.`sistema_log_ingresos_fallidos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `truckcode`.`sistema_log_ingresos_fallidos` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `session_id` VARCHAR(50) NULL,
    `data` TEXT NOT NULL,
    `ip_address` VARCHAR(20) NULL,
    PRIMARY KEY (`id`)    
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `truckcode`.`sistema_log_actividades`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `truckcode`.`sistema_log_actividades` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `session_id` VARCHAR(50) NOT NULL,
    `params` TEXT NOT NULL,
    `ip` VARCHAR(20) NULL,
    PRIMARY KEY (`id`) 
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;