-- -----------------------------------------------------
-- Table `@(VDADB)`.`t_template`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `@(VDADB)`.`t_template` (
  `id` BIGINT NOT NULL ,
  `name` CHAR(255) NULL ,
  `guest_name` CHAR(255) NULL ,
  `ip_or_dns` CHAR(255) NULL ,
  `port` INT NULL ,
  `type` CHAR(30) NOT NULL ,
  `external_id` CHAR(255) NOT NULL ,
  `pool_id` BIGINT NULL ,
  `state` CHAR(30) NULL ,
  `desktop_provider_id` BIGINT NULL ,
  `description` CHAR(255) NULL ,
  `operating_system` CHAR(255) NULL ,
  `optionals` TEXT NULL ,
  `volume_size` BIGINT NULL ,
  `creation_date` DATETIME NULL ,
  `host_id` BIGINT NULL ,
  `storage_id` BIGINT NULL ,
  `rev_template_id` BIGINT NULL ,
  `rev_master_id` BIGINT NULL ,
  `rev_parent_id` BIGINT NULL ,
  `version` BIGINT NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_template_pool` (`pool_id` ASC) ,
  INDEX `fk_template_desktopprovider` (`desktop_provider_id` ASC) ,
  INDEX `fk_template_host` (`host_id` ASC) ,
  INDEX `fk_template_storage` (`storage_id` ASC) ,
  UNIQUE INDEX external_provider_id (`external_id` ASC, `desktop_provider_id` ASC) ,
  INDEX `fk_template_template_id` (`rev_template_id` ASC) ,
  INDEX `fk_template_master_id` (`rev_master_id` ASC) ,
  INDEX `fk_template_parent_id` (`rev_parent_id` ASC) ,
  CONSTRAINT `fk_template_pool`
    FOREIGN KEY (`pool_id` )
    REFERENCES `@(VDADB)`.`t_pool` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_template_desktopprovider`
    FOREIGN KEY (`desktop_provider_id` )
    REFERENCES `@(VDADB)`.`t_desktopprovider` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_template_host`
    FOREIGN KEY (`host_id` )
    REFERENCES `@(VDADB)`.`t_host` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_template_storage`
    FOREIGN KEY (`storage_id` )
    REFERENCES `@(VDADB)`.`t_storage` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_template_template_id`
    FOREIGN KEY (`rev_template_id` )
    REFERENCES `@(VDADB)`.`t_template` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_template_master_id`
    FOREIGN KEY (`rev_master_id` )
    REFERENCES `@(VDADB)`.`t_template` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_template_parent_id`
    FOREIGN KEY (`rev_parent_id` )
    REFERENCES `@(VDADB)`.`t_template` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = @(VDADB.ENGINE);


-- -----------------------------------------------------
-- Table `@(VDADB)`.`t_vlan`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `@(VDADB)`.`t_vlan` (
  `id` BIGINT NOT NULL ,
  `subnet` CHAR(255) NOT NULL ,
  `name` CHAR(255) NULL,
  `version` BIGINT NOT NULL ,
  PRIMARY KEY (`id`),
  UNIQUE INDEX subnet_idx (`subnet` ASC))
ENGINE = @(VDADB.ENGINE);

-- -----------------------------------------------------
-- Table `@(VDADB)`.`t_desktop`
-- -----------------------------------------------------
ALTER TABLE `@(VDADB)`.`t_desktop`
  ADD `creation_date` DATETIME NULL,
  ADD `template_id_v31` BIGINT NULL;

-- jpr
ALTER TABLE `@(VDADB)`.`t_desktop`
  ADD CONSTRAINT `fk_desktop_template_v31`
      FOREIGN KEY (`template_id_v31` )
      REFERENCES `@(VDADB)`.`t_template` (`id` )
      ON DELETE NO ACTION
      ON UPDATE NO ACTION;
-- /jpr


-- -----------------------------------------------------
-- Table `@(VDADB)`.`t_pool`
-- -----------------------------------------------------
ALTER TABLE `@(VDADB)`.`t_pool`
  ADD `template_id_v31` BIGINT NULL,
  ADD INDEX fk_pool_template_v31 (`template_id_v31` ASC),
  ADD CONSTRAINT `fk_pool_template`
      FOREIGN KEY (`template_id_v31` )
      REFERENCES `@(VDADB)`.`t_template` (`id` )
      ON DELETE NO ACTION
      ON UPDATE NO ACTION;
-- jpr: named the constraint fk_pool_template, so it can live together w/fk_pool_desktop (v3)

-- -----------------------------------------------------
-- Data for table `@(VDADB)`.`t_sequence`
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO `@(VDADB)`.`t_sequence` (`seq_name`, `seq_count`) SELECT DISTINCT 'gen_template', 0 FROM (SELECT COUNT(*) FROM `@(VDADB)`.`t_sequence`) AS `seq` WHERE 'gen_template' NOT IN (SELECT `seq_name` FROM `@(VDADB)`.`t_sequence`);
INSERT INTO `@(VDADB)`.`t_sequence` (`seq_name`, `seq_count`) SELECT DISTINCT 'gen_vlan', 0 FROM (SELECT COUNT(*) FROM `@(VDADB)`.`t_sequence`) AS `seq` WHERE 'gen_vlan' NOT IN (SELECT `seq_name` FROM `@(VDADB)`.`t_sequence`);
COMMIT;


-- -----------------------------------------------------
-- Migration 3.0 -> 3.1
-- -----------------------------------------------------

-- add auto_increment flag for inserting templates
ALTER TABLE `@(VDADB)`.`t_template` MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT;

-- copy templates from t_desktop to t_template
INSERT INTO `@(VDADB)`.`t_template` (`name`, `guest_name`, `ip_or_dns`, `port`, `type`, `external_id`, `pool_id`, `state`, `desktop_provider_id`, `description`, `operating_system`, `optionals`, `host_id`, `storage_id`, `creation_date`, `version`) SELECT `name`, `guest_name`, `ip_or_dns`, `port`, 'template', `external_id`, `pool_id`, `state`, `desktop_provider_id`, `description`, `operating_system`, `optionals`, `host_id`, `storage_id`, `creation_date`, 1 FROM `@(VDADB)`.`t_desktop` WHERE is_template = 1;

-- remove auto_increment flag
ALTER TABLE `@(VDADB)`.`t_template` MODIFY COLUMN id BIGINT NOT NULL;

-- update template references in t_desktop
UPDATE `@(VDADB)`.`t_desktop` t0, `@(VDADB)`.`t_desktop` t1, `@(VDADB)`.`t_template` t2 SET t0.template_id_v31 = t2.id WHERE t0.is_template = 0 AND t0.template_id = t1.id AND t1.external_id = t2.external_id AND t1.desktop_provider_id = t2.desktop_provider_id;

-- update template references in t_pool
UPDATE `@(VDADB)`.`t_pool` t0, `@(VDADB)`.`t_desktop` t1, `@(VDADB)`.`t_template` t2 SET t0.template_id_v31 = t2.id WHERE t0.template_id = t1.id AND t1.external_id = t2.external_id AND t1.desktop_provider_id = t2.desktop_provider_id;

-- update sequence table
UPDATE `@(VDADB)`.`t_sequence` t0 SET t0.seq_count = (SELECT max(id) FROM `@(VDADB)`.`t_template`) + 10 WHERE t0.seq_name = 'gen_template';