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 `control_frigo` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
USE `control_frigo`;
-- -----------------------------------------------------
-- Table `control_frigo`.`sistema_propietario`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`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) NOT NULL ,
    `fax` VARCHAR(30) NOT NULL ,
    PRIMARY KEY (`rut`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = DEFAULT
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `control_frigo`.`sistema_interfaces`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`sistema_interfaces` (
    `id` INT(2) UNSIGNED NOT NULL AUTO_INCREMENT ,
    `nombre` VARCHAR(45) NOT NULL,
    `path` VARCHAR(30) NOT NULL,
    `header` VARCHAR(30) NOT NULL,
    `descripcion` TEXT NULL,
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- ----------------------------------------------------
-- Table `control_frigo`.`sistema_perfiles_usuarios`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`sistema_perfiles_usuarios` (
    `id` INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
    `creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `interfaz_id` INT(2) UNSIGNED NOT NULL ,
    `nombre` VARCHAR(100) NOT NULL ,
    `clientes` ENUM('TODOS', 'ASIGNADOS') NOT NULL DEFAULT 'TODOS',
    `tipo` ENUM('INTERNO', 'EXTERNO') NOT NULL DEFAULT 'INTERNO',
    `ocultar` INT(1) UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`) ,
    INDEX `fk_sistema_perfiles_usuarios_interfaces` (`interfaz_id` ASC) ,
    CONSTRAINT `fk_sistema_perfiles_usuarios_interfaces`
        FOREIGN KEY (`interfaz_id` )
        REFERENCES `control_frigo`.`sistema_interfaces` (`id` )
        ON DELETE RESTRICT  
        ON UPDATE CASCADE    
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- ----------------------------------------------------
-- Table `control_frigo`.`sistema_usuarios`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`sistema_usuarios` (
    `id` INT(2) UNSIGNED NOT NULL,
    `creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `creador` INT(2) UNSIGNED NULL,
    `cliente_id` INT(2) UNSIGNED NULL,
    `perfil_id` INT(2) UNSIGNED NOT NULL ,
    `nombre` VARCHAR(100) NOT NULL ,
    `mail` VARCHAR(100) NOT NULL ,
    `cargo` VARCHAR(30) NULL,
    `telefono` VARCHAR(30) NULL,
    `comuna` VARCHAR(30) NULL,
    `empresa` VARCHAR(30) NULL,
    `clave` VARCHAR(100) NOT NULL ,
    `valido_hasta` DATE NULL DEFAULT NULL,
    `notificaciones` INT(1) UNSIGNED NOT NULL DEFAULT 0,
    `estado` INT(1) UNSIGNED NOT NULL DEFAULT '1',
    PRIMARY KEY (`id`) ,
    UNIQUE INDEX `unique_usuario` (`mail` ASC),
    UNIQUE INDEX `unique_contacto` (`contacto_id` ASC) ,
    INDEX `fk_sistema_usuarios_usuarios` (`creador` ASC) ,
    INDEX `fk_sistema_usuarios_perfiles` (`perfil_id` ASC) ,
    CONSTRAINT `fk_sistema_usuarios_usuarios`
        FOREIGN KEY (`creador` )
        REFERENCES `control_frigo`.`sistema_usuarios` (`id` )
        ON DELETE SET NULL 
        ON UPDATE CASCADE,
    CONSTRAINT `fk_sistema_usuarios_perfiles`
        FOREIGN KEY (`perfil_id` )
        REFERENCES `control_frigo`.`sistema_perfiles_usuarios` (`id` )
        ON DELETE RESTRICT  
        ON UPDATE CASCADE,
    CONSTRAINT `fk_sistema_usuarios_contactos`
        FOREIGN KEY (`contacto_id` )
        REFERENCES `control_frigo`.`contactos` (`id` )
        ON DELETE CASCADE   
        ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `control_frigo`.`sistema_preferencias`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`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 `control_frigo`.`sistema_informes_preconfig`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`sistema_informes_preconfig` (
    `id` INT(2) UNSIGNED NOT NULL AUTO_INCREMENT ,
    `nombre` VARCHAR(100) NOT NULL,
    `url` VARCHAR(100) 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 `control_frigo`.`sistema_mensajes_auto`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `control_frigo`.`sistema_mensajes_auto` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    `creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `creador` INT(2) 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 `control_frigo`.`sistema_usuarios` (`id` )
        ON DELETE SET NULL 
        ON UPDATE CASCADE,
    CONSTRAINT `fk_sistema_mensajes_auto_destinatarios`
        FOREIGN KEY (`usuario_id` )
        REFERENCES `control_frigo`.`sistema_usuarios` (`id` )
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_sistema_mensajes_auto_informes`
        FOREIGN KEY (`informe_id` )
        REFERENCES `control_frigo`.`sistema_informes_preconfig` (`id` )
        ON DELETE CASCADE
        ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `control_frigo`.`sistema_mensajes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `control_frigo`.`sistema_mensajes` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `creador` INT(2) 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 `control_frigo`.`sistema_usuarios` (`id` )
        ON DELETE SET NULL 
        ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `control_frigo`.`sistema_log_ingresos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `control_frigo`.`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 `control_frigo`.`sistema_usuarios` (`id` )
        ON DELETE CASCADE  
        ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `control_frigo`.`sistema_log_ingresos_fallidos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `control_frigo`.`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 `control_frigo`.`sistema_log_actividades`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `control_frigo`.`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;
-- -----------------------------------------------------
-- VISTA USUARIOS
-- -----------------------------------------------------
-- Table `control_frigo`.`vista_usuarios`
-- -----------------------------------------------------
CREATE VIEW `control_frigo`.`vista_usuarios` AS 
SELECT 
    su.id, su.perfil_id, su.cliente_id, su.nombre, su.mail, su.empresa, su.clave,  
    su.valido_hasta, su.notificaciones, su.estado, 
    pu.nombre AS perfil, pu.clientes, si.nombre AS interfaz, 
    si.path , si.header, c.nombre_fantasia AS cliente   
FROM sistema_usuarios su 
JOIN sistema_perfiles_usuarios pu ON pu.id=su.perfil_id 
JOIN sistema_interfaces si ON si.id=pu.interfaz_id 
LEFT JOIN clientes c ON c.id=su.cliente_id ;


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