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';

DROP SCHEMA IF EXISTS `mydb` ;
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`video`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`video` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `title` VARCHAR(255) NOT NULL ,
  `description` TEXT NULL ,
  `featured` TINYINT(1) NOT NULL DEFAULT FALSE ,
  `private` TINYINT(1) NOT NULL DEFAULT FALSE ,
  `password` VARCHAR(25) NOT NULL DEFAULT '' ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`tag`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`tag` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(100) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`category`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`category` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(100) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`subcategory`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`subcategory` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `category_id` INT UNSIGNED NOT NULL ,
  `name` VARCHAR(100) NOT NULL ,
  PRIMARY KEY (`id`, `category_id`) ,
  INDEX `fk_category_id_idx` (`category_id` ASC) ,
  CONSTRAINT `fk_subcat_category_id`
    FOREIGN KEY (`category_id` )
    REFERENCES `mydb`.`category` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`organization`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`organization` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(255) NOT NULL ,
  `logo_filename` VARCHAR(60) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`contact`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`contact` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `first_name` VARCHAR(150) NOT NULL ,
  `last_name` VARCHAR(150) NOT NULL ,
  `email` VARCHAR(255) NOT NULL ,
  `phone` VARCHAR(45) NOT NULL ,
  `url` VARCHAR(255) NOT NULL ,
  `organization_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_organization_id_idx` (`organization_id` ASC) ,
  CONSTRAINT `fk_organization_id`
    FOREIGN KEY (`organization_id` )
    REFERENCES `mydb`.`organization` (`id` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`video_part`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`video_part` (
  `video_id` INT UNSIGNED NOT NULL ,
  `part` TINYINT UNSIGNED NOT NULL DEFAULT 1 ,
  `title` VARCHAR(100) NOT NULL DEFAULT '' ,
  `description` TEXT NULL ,
  `start_date` TIMESTAMP NULL ,
  `end_date` TIMESTAMP NULL ,
  `production_date` DATE NULL ,
  `post_date` TIMESTAMP NULL ,
  `duration` INT UNSIGNED NULL ,
  `approved` TINYINT(1) NOT NULL DEFAULT FALSE ,
  `can_download` TINYINT(1) NOT NULL DEFAULT TRUE ,
  `encoding_organization` INT UNSIGNED NOT NULL ,
  `production_organization` INT UNSIGNED NOT NULL ,
  `contact_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`video_id`, `part`) ,
  INDEX `fk_encoding_organization_idx` (`encoding_organization` ASC) ,
  INDEX `fk_production_organization_idx` (`production_organization` ASC) ,
  INDEX `fk_contact_idx` (`contact_id` ASC) ,
  CONSTRAINT `fk_video`
    FOREIGN KEY (`video_id` )
    REFERENCES `mydb`.`video` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_encoding_organization`
    FOREIGN KEY (`encoding_organization` )
    REFERENCES `mydb`.`organization` (`id` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `fk_production_organization`
    FOREIGN KEY (`production_organization` )
    REFERENCES `mydb`.`organization` (`id` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `fk_contact`
    FOREIGN KEY (`contact_id` )
    REFERENCES `mydb`.`contact` (`id` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`video_material`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`video_material` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `video_id` INT UNSIGNED NOT NULL ,
  `part` TINYINT UNSIGNED NOT NULL ,
  `title` VARCHAR(255) NOT NULL ,
  `filename` VARCHAR(60) NOT NULL ,
  PRIMARY KEY (`id`, `video_id`, `part`) ,
  INDEX `fk_video_id_part_idx` (`video_id` ASC, `part` ASC) ,
  CONSTRAINT `fk_material_video_id_part`
    FOREIGN KEY (`part` , `video_id` )
    REFERENCES `mydb`.`video_part` (`part` , `video_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`video_link`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`video_link` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `video_id` INT UNSIGNED NOT NULL ,
  `part` TINYINT UNSIGNED NOT NULL ,
  `title` VARCHAR(255) NOT NULL ,
  `url` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`, `video_id`, `part`) ,
  INDEX `fk_video_id_part_idx` (`part` ASC, `video_id` ASC) ,
  CONSTRAINT `fk_link_video_id_part`
    FOREIGN KEY (`part` , `video_id` )
    REFERENCES `mydb`.`video_part` (`part` , `video_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`video_tag`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`video_tag` (
  `video_id` INT UNSIGNED NOT NULL ,
  `tag_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`video_id`, `tag_id`) ,
  INDEX `fk_tag_id_idx` (`tag_id` ASC) ,
  INDEX `fk_video_id_idx` (`video_id` ASC) ,
  CONSTRAINT `fk_video_id`
    FOREIGN KEY (`video_id` )
    REFERENCES `mydb`.`video` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_tag_id`
    FOREIGN KEY (`tag_id` )
    REFERENCES `mydb`.`tag` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`video_category`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`video_category` (
  `video_id` INT UNSIGNED NOT NULL ,
  `category_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`video_id`, `category_id`) ,
  INDEX `fk_video_id_idx` (`video_id` ASC) ,
  INDEX `fk_category_id_idx` (`category_id` ASC) ,
  CONSTRAINT `fk_video_category_id`
    FOREIGN KEY (`category_id` )
    REFERENCES `mydb`.`category` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_video_video_id`
    FOREIGN KEY (`video_id` )
    REFERENCES `mydb`.`video` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`video_presenter`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`video_presenter` (
  `video_id` INT UNSIGNED NOT NULL ,
  `contact_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`video_id`, `contact_id`) ,
  INDEX `fk_contact_id_idx` (`contact_id` ASC) ,
  INDEX `fk_video_id_idx` (`video_id` ASC) ,
  CONSTRAINT `fk_pres_video_id`
    FOREIGN KEY (`video_id` )
    REFERENCES `mydb`.`video` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_pres_contact_id`
    FOREIGN KEY (`contact_id` )
    REFERENCES `mydb`.`contact` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;



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