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 `instalpon2013web` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
USE `instalpon2013web`;
-- ----------------------------------------------------
-- Table `instalpon2013web`.`sistema_usuarios` OK
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `instalpon2013web`.`sistema_usuarios` (
    `id` INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(100) NOT NULL ,
    `mail` VARCHAR(100) NOT NULL ,
    `clave` VARCHAR(100) NOT NULL ,
    `estado` ENUM('ACTIVO','DESHABILITADO') NOT NULL DEFAULT 'ACTIVO',
    PRIMARY KEY (`id`) ,
    UNIQUE INDEX `unique_usuario` (`mail` ASC)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `instalpon2013web`.`sistema_log_ingresos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `instalpon2013web`.`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,
    PRIMARY KEY (`id`) ,
    INDEX `fk_sistema_log_ingresos_usuarios` (`usuario_id` ASC) ,
    CONSTRAINT `fk_sistema_log_ingresos_usuarios`
        FOREIGN KEY (`usuario_id` )
        REFERENCES `instalpon2013web`.`sistema_usuarios` (`id` )
        ON DELETE RESTRICT   
        ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `instalpon2013web`.`unidades_medida`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `instalpon2013web`.`unidades_medidas` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    `nombre` VARCHAR(30) NOT NULL,
    `simbolo` VARCHAR(20) NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `instalpon2013web`.`categorias`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `instalpon2013web`.`categorias` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `codigo` VARCHAR(20) NOT NULL,
    `padre_id` INT UNSIGNED NULL,
    `nombre` VARCHAR(200) NOT NULL ,
    `slug` VARCHAR(50) NOT NULL,
    `descripcion` TEXT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_categoria_padre` (`padre_id` ASC) ,
    CONSTRAINT `fk_categoria_padre`
        FOREIGN KEY (`padre_id` )
        REFERENCES `instalpon2013web`.`categorias` (`id` )
        ON DELETE SET NULL
        ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `instalpon2013web`.`marcas`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `instalpon2013web`.`marcas` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(200) NOT NULL ,
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `instalpon2013web`.`familias`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `instalpon2013web`.`familias` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(200) NOT NULL ,
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `instalpon2013web`.`colores`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `instalpon2013web`.`colores` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tipo` VARCHAR(20) NOT NULL,
    `nombre` VARCHAR(200) NOT NULL ,
    `imagen` VARCHAR(100) NULL,
    `codigo` VARCHAR(100) NULL,
    PRIMARY KEY (`id`),
    INDEX `index_tipo` (`tipo` ASC)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `instalpon2013web`.`imagenes`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `instalpon2013web`.`imagenes` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    `nombre` VARCHAR(100) NOT NULL,
    `descripcion` TEXT NULL,
    `archivo` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `instalpon2013web`.`productos`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `instalpon2013web`.`productos` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `marca_id` INT UNSIGNED NULL,
    `familia_id` INT UNSIGNED NULL,
    `galeria_id` INT(3) UNSIGNED NOT NULL,
    `imagen_id` INT UNSIGNED NULL,
    `nombre` VARCHAR(200) NOT NULL ,
    `descripcion_general` TEXT NULL,
    `descripcion_colores` TEXT NULL,
    `descripcion_terminaciones` TEXT NULL,
    `descripcion_accionamientos` TEXT NULL,
    `ficha_descargable` TEXT NULL,
    `keywords` TEXT NOT NULL,
    `mostrar` INT(1) UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`) ,
    INDEX `fk_productos_marcas` (`marca_id` ASC) ,
    INDEX `fk_productos_familias` (`familia_id` ASC) ,
    INDEX `fk_productos_galerias` (`galeria_id` ASC) ,
    INDEX `fk_productos_imagenes` (`imagen_id` ASC) ,
    CONSTRAINT `fk_productos_marcas`
        FOREIGN KEY (`marca_id` )
        REFERENCES `instalpon2013web`.`marcas` (`id` )
        ON DELETE RESTRICT
        ON UPDATE CASCADE, 
    CONSTRAINT `fk_productos_familias`
        FOREIGN KEY (`familia_id` )
        REFERENCES `instalpon2013web`.`familias` (`id` )
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT `fk_productos_galerias`
        FOREIGN KEY (`galeria_id` )
        REFERENCES `instalpon2013web`.`galerias` (`id` )
        ON DELETE RESTRICT
        ON UPDATE CASCADE     
    CONSTRAINT `fk_productos_imagenes`
        FOREIGN KEY (`imagen_id` )
        REFERENCES `instalpon2013web`.`imagenes` (`id` )
        ON DELETE SET NULL
        ON UPDATE CASCADE         
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `instalpon2013web`.`productos_categorias`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `instalpon2013web`.`productos_categorias` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `producto_id` INT UNSIGNED NOT NULL,
    `categoria_id`  INT UNSIGNED NOT NULL, 
    PRIMARY KEY (`id`),
    INDEX `fk_productos_categorias_productos` (`producto_id` ASC) ,
    INDEX `fk_productos_categorias_categorias` (`categoria_id` ASC) ,
    UNIQUE `unique_asignacion_categoria` (`producto_id`, `categoria_id` ASC),
    CONSTRAINT `fk_productos_categorias_productos`
        FOREIGN KEY (`producto_id` )
        REFERENCES `instalpon2013web`.`productos` (`id` )
        ON DELETE CASCADE 
        ON UPDATE CASCADE,
    CONSTRAINT `fk_productos_categorias_categorias`
        FOREIGN KEY (`categoria_id` )
        REFERENCES `instalpon2013web`.`categorias` (`id` )
        ON DELETE CASCADE 
        ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `instalpon2013web`.`productos_colores`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `instalpon2013web`.`productos_colores` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `producto_id` INT UNSIGNED NOT NULL,
    `color_id`  INT UNSIGNED NOT NULL, 
    PRIMARY KEY (`id`),
    INDEX `fk_productos_colores_productos` (`producto_id` ASC) ,
    INDEX `fk_productos_colores_colores` (`color_id` ASC) ,
    UNIQUE `unique_asignacion_color` (`producto_id`, `color_id` ASC),
    CONSTRAINT `fk_productos_colores_productos`
        FOREIGN KEY (`producto_id` )
        REFERENCES `instalpon2013web`.`productos` (`id` )
        ON DELETE CASCADE 
        ON UPDATE CASCADE,
    CONSTRAINT `fk_productos_colores_colores`
        FOREIGN KEY (`color_id` )
        REFERENCES `instalpon2013web`.`colores` (`id` )
        ON DELETE CASCADE 
        ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `instalpon2013web`.`imagenes_banner`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `instalpon2013web`.`imagenes_banner` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(100) NOT NULL,
    `descripcion` TEXT NULL,
    `archivo` VARCHAR(100) NOT NULL,
    `url` TEXT NULL,
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `instalpon2013web`.`galerias`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `instalpon2013web`.`galerias` (
    `id` INT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(200) NOT NULL ,
    `descripcion` TEXT NULL,
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `instalpon2013web`.`galerias_imagenes`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `instalpon2013web`.`galerias_imagenes` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `galeria_id` INT(3) UNSIGNED NOT NULL ,
    `imagen_id` INT UNSIGNED NOT NULL ,
    PRIMARY KEY (`id`),
    INDEX `fk_imagenes_galerias_galerias` (`galeria_id` ASC) ,
    INDEX `fk_imagenes_galerias_imagenes` (`imagen_id` ASC) ,
    CONSTRAINT `fk_imagenes_galerias_galerias`
        FOREIGN KEY (`galeria_id` )
        REFERENCES `instalpon2013web`.`galerias` (`id` )
        ON DELETE CASCADE 
        ON UPDATE CASCADE,
    CONSTRAINT `fk_imagenes_galerias_imagenes`
        FOREIGN KEY (`imagen_id` )
        REFERENCES `instalpon2013web`.`imagenes` (`id` )
        ON DELETE CASCADE 
        ON UPDATE CASCADE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;
-- -----------------------------------------------------
-- Table `instalpon2013web`.`publicaciones`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `instalpon2013web`.`publicaciones` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `fecha_publicacion` DATE NOT NULL,
    `titulo` VARCHAR(200) NOT NULL ,
    `slug` VARCHAR(200) NOT NULL,
    `contenidos` TEXT NULL,
    `categoria` ENUM('NOTICIAS', 'INFOTEC', 'ARTICULOS') NOT NULL,
    `galeria_id` INT(3) UNSIGNED NOT NULL,
    `etiquetas` TEXT NOT NULL,
    `imagen_principal` VARCHAR(100) NULL,
    `estado` ENUM('NO PUBLICADO', 'PUBLICADO') NOT NULL DEFAULT 'NO PUBLICADO',
    PRIMARY KEY (`id`) ,
    INDEX `fk_publicaciones_galerias` (`galeria_id` ASC) ,
    CONSTRAINT `fk_publicaciones_galerias`
        FOREIGN KEY (`galeria_id` )
        REFERENCES `instalpon2013web`.`galerias` (`id` )
        ON DELETE RESTRICT
        ON UPDATE CASCADE      
)
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;
