-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.0.15-nt


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


--
-- Create schema subtrack
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ subtrack;
USE subtrack;

--
-- Table structure for table `subtrack`.`stgenre`
--

DROP TABLE IF EXISTS `stgenre`;
CREATE TABLE `stgenre` (
  `stGenreId` int(10) unsigned NOT NULL default '0',
  `stName` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`stGenreId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--
-- Table structure for table `subtrack`.`stintention`
--

DROP TABLE IF EXISTS `stintention`;
CREATE TABLE `stintention` (
  `stIntentionId` int(10) unsigned NOT NULL default '0',
  `stWorkId` int(10) unsigned NOT NULL default '0',
  `stMarketId` int(10) unsigned NOT NULL default '0',
  `stScore` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`stIntentionId`),
  KEY `idxWorkId` (`stWorkId`),
  KEY `idxMarket` (`stMarketId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Table structure for table `subtrack`.`stmarket`
--

DROP TABLE IF EXISTS `stmarket`;
CREATE TABLE `stmarket` (
  `stMarketId` int(10) unsigned NOT NULL default '0',
  `stName` varchar(45) NOT NULL default '',
  `stDescription` text,
  `stURL` text,
  `stActive` tinyint(1) NOT NULL default '0',
  `stMarketTypeId` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`stMarketId`,`stName`),
  KEY `idxActive` (`stActive`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `subtrack`.`stmarketgenre`
--

DROP TABLE IF EXISTS `stmarketgenre`;
CREATE TABLE `stmarketgenre` (
  `stMarketId` int(10) unsigned NOT NULL default '0',
  `stGenreId` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`stMarketId`,`stGenreId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `subtrack`.`stmarketsize`
--

DROP TABLE IF EXISTS `stmarketsize`;
CREATE TABLE `stmarketsize` (
  `stMarketId` int(10) unsigned NOT NULL default '0',
  `stSizeId` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`stMarketId`,`stSizeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `subtrack`.`stmarketsubdate`
--

DROP TABLE IF EXISTS `stmarketsubdate`;
CREATE TABLE `stmarketsubdate` (
  `stMarketSubDateId` int(10) unsigned NOT NULL default '0',
  `stMarketId` int(10) unsigned NOT NULL default '0',
  `stDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `stDateType` int(10) unsigned NOT NULL default '0' COMMENT '0-Open,1-Close',
  PRIMARY KEY  (`stMarketSubDateId`,`stMarketId`),
  KEY `idxDate` (`stDate`),
  KEY `idxMarket` (`stMarketId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `subtrack`.`stmarkettype`
--

DROP TABLE IF EXISTS `stmarkettype`;
CREATE TABLE `stmarkettype` (
  `stMarketTypeId` int(10) unsigned NOT NULL default '0',
  `stName` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`stMarketTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `subtrack`.`stsize`
--

DROP TABLE IF EXISTS `stsize`;
CREATE TABLE `stsize` (
  `stSizeId` int(10) unsigned NOT NULL auto_increment,
  `stName` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`stSizeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--
-- Table structure for table `subtrack`.`ststatus`
--

DROP TABLE IF EXISTS `ststatus`;
CREATE TABLE `ststatus` (
  `stStatusId` int(10) unsigned NOT NULL default '0',
  `stName` varchar(45) NOT NULL default '',
  `stDescription` varchar(45) NOT NULL default '',
  `stStatusType` int(10) unsigned NOT NULL default '0' COMMENT '0 - Open, 1 - Closed',
  PRIMARY KEY  (`stStatusId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `subtrack`.`ststatus`
--

/*!40000 ALTER TABLE `ststatus` DISABLE KEYS */;
INSERT INTO `ststatus` (`stStatusId`,`stName`,`stDescription`,`stStatusType`) VALUES
 (1,'submitted','',0),
 (2,'queried','',0),
 (3,'re-submitted','',0),
 (4,'lost','',2),
 (5,'revision requested','',2),
 (6,'accepted','',1),
 (7,'rejected','',1),
 (8,'withdrawn','',1);
/*!40000 ALTER TABLE `ststatus` ENABLE KEYS */;


--
-- Table structure for table `subtrack`.`stsubmissionstate`
--

DROP TABLE IF EXISTS `stsubmissionstate`;
CREATE TABLE `stsubmissionstate` (
  `stSubmissionStateId` int(10) unsigned NOT NULL default '0',
  `stIntentionId` int(10) unsigned NOT NULL default '0',
  `stStatusId` int(10) unsigned NOT NULL default '0',
  `stStatusDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `stComment` varchar(256) default '',
  `stQueryDate` datetime default '0000-00-00 00:00:00',
  `stChangedDate` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`stSubmissionStateId`),
  KEY `idxIntention` (`stIntentionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `subtrack`.`stwork`
--

DROP TABLE IF EXISTS `stwork`;
CREATE TABLE `stwork` (
  `stWorkId` int(10) unsigned NOT NULL default '0',
  `stName` varchar(45) NOT NULL default '',
  `stDescription` text,
  `stActive` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`stWorkId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--
-- Table structure for table `subtrack`.`stworkgenre`
--

DROP TABLE IF EXISTS `stworkgenre`;
CREATE TABLE `stworkgenre` (
  `stWorkId` int(10) unsigned NOT NULL default '0',
  `stGenreId` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`stWorkId`,`stGenreId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--
-- Table structure for table `subtrack`.`stworksize`
--

DROP TABLE IF EXISTS `stworksize`;
CREATE TABLE `stworksize` (
  `stWorkId` int(10) unsigned NOT NULL default '0',
  `stSizeId` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`stWorkId`,`stSizeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- View structure for view `subtrack`.`v_availablemarkets`
--

DROP VIEW IF EXISTS `v_availablemarkets`;
CREATE VIEW `subtrack`.`v_availablemarkets` AS select sql_no_cache `s`.`stMarketId` AS `stMarketId`,`s`.`stName` AS `stName`,`s`.`stDescription` AS `stDescription`,`s`.`stURL` AS `stURL`,`s`.`stActive` AS `stActive`,`s`.`stMarketTypeId` AS `stMarketTypeId` from (`subtrack`.`stmarket` `s` left join `subtrack`.`stmarketsubdate` `msdate` on((`msdate`.`stMarketId` = `s`.`stMarketId`))) where ((isnull(`msdate`.`stDate`) or ((`msdate`.`stDate` = (select sql_no_cache max(`subtrack`.`stmarketsubdate`.`stDate`) AS `max(stmarketsubdate.stDate)` from `subtrack`.`stmarketsubdate` where ((`subtrack`.`stmarketsubdate`.`stMarketId` = `msdate`.`stMarketId`) and (`subtrack`.`stmarketsubdate`.`stDate` <= curdate())))) and ((`msdate`.`stDateType` = 0) or isnull(`msdate`.`stDateType`))) or ((`msdate`.`stDate` = (select sql_no_cache min(`subtrack`.`stmarketsubdate`.`stDate`) AS `min(stmarketsubdate.stDate)` from `subtrack`.`stmarketsubdate` where ((`subtrack`.`stmarketsubdate`.`stMarketId` = `msdate`.`stMarketId`) and (`subtrack`.`stmarketsubdate`.`stDate` > curdate()) and not(exists(select sql_no_cache 1 AS `Not_used` from `subtrack`.`stmarketsubdate` where ((`subtrack`.`stmarketsubdate`.`stMarketId` = `msdate`.`stMarketId`) and (`subtrack`.`stmarketsubdate`.`stDate` <= curdate()))))))) and (`msdate`.`stDateType` = 1))) and (`s`.`stActive` = 1) and not(`s`.`stmarketid` in (select sql_no_cache `subtrack`.`stmarket`.`stMarketId` AS `stmarketid` from (`subtrack`.`stmarket` join (`subtrack`.`stintention` join (`subtrack`.`stsubmissionstate` join `subtrack`.`ststatus` on((`subtrack`.`ststatus`.`stStatusId` = `subtrack`.`stsubmissionstate`.`stStatusId`))) on((`subtrack`.`stsubmissionstate`.`stIntentionId` = `subtrack`.`stintention`.`stIntentionId`))) on((`subtrack`.`stintention`.`stMarketId` = `subtrack`.`stmarket`.`stMarketId`))) where ((`subtrack`.`ststatus`.`stStatusType` = 0) and (`subtrack`.`stsubmissionstate`.`stStatusDate` = (select sql_no_cache max(`s_state`.`stStatusDate`) AS `max(s_state.stStatusDate)` from `subtrack`.`stsubmissionstate` `s_state` where (`s_state`.`stIntentionId` = `subtrack`.`stsubmissionstate`.`stIntentionId`))) and (`subtrack`.`stsubmissionstate`.`stChangedDate` = (select sql_no_cache max(`ch_state`.`stChangedDate`) AS `max(ch_state.stChangedDate)` from `subtrack`.`stsubmissionstate` `ch_state` where ((`ch_state`.`stIntentionId` = `subtrack`.`stsubmissionstate`.`stIntentionId`) and (`subtrack`.`stsubmissionstate`.`stStatusDate` = `ch_state`.`stStatusDate`))))))));


--
-- View structure for view `subtrack`.`v_intentionstatus`
--

DROP VIEW IF EXISTS `v_intentionstatus`;
CREATE VIEW `subtrack`.`v_intentionstatus` AS select `subtrack`.`stintention`.`stWorkId` AS `stworkid`,`subtrack`.`stintention`.`stMarketId` AS `stmarketid`,`subtrack`.`stintention`.`stScore` AS `stscore`,`subtrack`.`stsubmissionstate`.`stSubmissionStateId` AS `stSubmissionStateId`,`subtrack`.`stsubmissionstate`.`stIntentionId` AS `stIntentionId`,`subtrack`.`stsubmissionstate`.`stStatusId` AS `stStatusId`,`subtrack`.`stsubmissionstate`.`stStatusDate` AS `stStatusDate`,`subtrack`.`stsubmissionstate`.`stComment` AS `stComment`,`subtrack`.`stsubmissionstate`.`stQueryDate` AS `stQueryDate`,`subtrack`.`stsubmissionstate`.`stChangedDate` AS `stChangedDate`,`subtrack`.`ststatus`.`stStatusType` AS `ststatustype` from (`subtrack`.`stintention` join (`subtrack`.`stsubmissionstate` join `subtrack`.`ststatus` on((`subtrack`.`ststatus`.`stStatusId` = `subtrack`.`stsubmissionstate`.`stStatusId`))) on((`subtrack`.`stsubmissionstate`.`stIntentionId` = `subtrack`.`stintention`.`stIntentionId`))) where ((`subtrack`.`stsubmissionstate`.`stStatusDate` = (select max(`s_state`.`stStatusDate`) AS `max(ststatusdate)` from `subtrack`.`stsubmissionstate` `s_state` where (`s_state`.`stIntentionId` = `subtrack`.`stsubmissionstate`.`stIntentionId`))) and (`subtrack`.`stsubmissionstate`.`stChangedDate` = (select max(`ch_state`.`stChangedDate`) AS `max(stchangeddate)` from `subtrack`.`stsubmissionstate` `ch_state` where ((`ch_state`.`stIntentionId` = `subtrack`.`stsubmissionstate`.`stIntentionId`) and (`subtrack`.`stsubmissionstate`.`stStatusDate` = `ch_state`.`stStatusDate`)))));

--
-- View structure for view `subtrack`.`v_currentsubmissions`
--

DROP VIEW IF EXISTS `v_currentsubmissions`;
CREATE VIEW `subtrack`.`v_currentsubmissions` AS select `sw`.`stWorkId` AS `stWorkId`,`sw`.`stName` AS `stName`,`sw`.`stDescription` AS `stDescription`,`sw`.`stActive` AS `stActive`,`subtrack`.`stsubmissionstate`.`stSubmissionStateId` AS `stSubmissionStateId`,`subtrack`.`stsubmissionstate`.`stIntentionId` AS `stIntentionId`,`subtrack`.`stsubmissionstate`.`stStatusId` AS `stStatusId`,`subtrack`.`stsubmissionstate`.`stStatusDate` AS `stStatusDate`,`subtrack`.`stsubmissionstate`.`stComment` AS `stComment`,`subtrack`.`stsubmissionstate`.`stQueryDate` AS `stQueryDate`,`subtrack`.`stsubmissionstate`.`stChangedDate` AS `stChangedDate`,`subtrack`.`ststatus`.`stName` AS `statusName`,`subtrack`.`ststatus`.`stStatusType` AS `stStatusType` from (`subtrack`.`stwork` `sw` join (`subtrack`.`stintention` join (`subtrack`.`stsubmissionstate` join `subtrack`.`ststatus` on((`subtrack`.`ststatus`.`stStatusId` = `subtrack`.`stsubmissionstate`.`stStatusId`))) on((`subtrack`.`stsubmissionstate`.`stIntentionId` = `subtrack`.`stintention`.`stIntentionId`))) on((`subtrack`.`stintention`.`stWorkId` = `sw`.`stWorkId`))) where ((`subtrack`.`ststatus`.`stStatusType` = 0) and (`subtrack`.`stsubmissionstate`.`stStatusDate` = (select max(`s_state`.`stStatusDate`) AS `max(ststatusdate)` from `subtrack`.`stsubmissionstate` `s_state` where (`s_state`.`stIntentionId` = `subtrack`.`stsubmissionstate`.`stIntentionId`))) and (`subtrack`.`stsubmissionstate`.`stChangedDate` = (select max(`ch_state`.`stChangedDate`) AS `max(stchangeddate)` from `subtrack`.`stsubmissionstate` `ch_state` where ((`ch_state`.`stIntentionId` = `subtrack`.`stsubmissionstate`.`stIntentionId`) and (`subtrack`.`stsubmissionstate`.`stStatusDate` = `ch_state`.`stStatusDate`)))));


/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
