-- phpMyAdmin SQL Dump
-- version 2.11.10
-- http://www.phpmyadmin.net
--
-- Host: 192.168.1.161
-- Generation Time: Dec 01, 2011 at 03:14 PM
-- Server version: 5.5.11
-- PHP Version: 5.2.10

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `reserv`
--

DELIMITER $$
--
-- Functions
--
DROP FUNCTION IF EXISTS `reserv_compute_reserve_deadline`$$
CREATE FUNCTION `reserv_compute_reserve_deadline`(minutes_bias INT, start_time DATETIME, crl_days_ago INT, crl_hours_ago INT, crl_minutes_ago INT, crl_weekday INT, crl_hour INT, crl_minute INT, crl_second INT) RETURNS datetime
    NO SQL
    DETERMINISTIC
BEGIN
DECLARE `time` DATETIME;
SET `time`=DATE_ADD(start_time, INTERVAL minutes_bias MINUTE);
SET `time`=IF(ISNULL(crl_days_ago),`time`,DATE_SUB(`time`, INTERVAL crl_days_ago DAY));

SET `time`=IF(ISNULL(crl_hours_ago),`time`,DATE_SUB(`time`, INTERVAL crl_hours_ago HOUR));
SET `time`=IF(ISNULL(crl_minutes_ago),`time`,DATE_SUB(`time`, INTERVAL crl_minutes_ago MINUTE));

SET `time`=IF(ISNULL(crl_weekday),`time`,DATE_SUB(`time`, INTERVAL DAYOFWEEK(`time`)-1-crl_weekday DAY));
SET `time`=IF(ISNULL(crl_hour),`time`,DATE_SUB(`time`, INTERVAL HOUR(`time`)-crl_hour HOUR));
SET `time`=IF(ISNULL(crl_minute),`time`,DATE_SUB(`time`, INTERVAL MINUTE(`time`)-crl_minute MINUTE));
SET `time`=IF(ISNULL(crl_second),`time`,DATE_SUB(`time`, INTERVAL SECOND(`time`)-crl_second SECOND));
SET `time`=DATE_SUB(`time`, INTERVAL minutes_bias MINUTE);

RETURN `time`;
END$$

DROP FUNCTION IF EXISTS `reserv_compute_reserve_starttime`$$
CREATE FUNCTION `reserv_compute_reserve_starttime`(minutes_bias INT, minutes_margin INT, crl_days_ago INT, crl_hours_ago INT, crl_minutes_ago INT, crl_weekday INT, crl_hour INT, crl_minute INT, crl_second INT) RETURNS datetime
    DETERMINISTIC
BEGIN
DECLARE `time` DATETIME;
DECLARE `timenow` DATETIME;
SET `timenow`=DATE_ADD(UTC_TIMESTAMP(), INTERVAL minutes_bias MINUTE);
SET `time`=DATE_ADD(`time`, INTERVAL minutes_margin MINUTE);
SET `time`=`timenow`;
SET `time`=IF(ISNULL(crl_weekday),`time`,DATE_SUB(`time`, INTERVAL DAYOFWEEK(`time`)-1-crl_weekday DAY));
SET `time`=IF(ISNULL(crl_hour),`time`,DATE_SUB(`time`, INTERVAL HOUR(`time`)-crl_hour HOUR));
SET `time`=IF(ISNULL(crl_minute),`time`,DATE_SUB(`time`, INTERVAL MINUTE(`time`)-crl_minute MINUTE));
SET `time`=IF(ISNULL(crl_second),`time`,DATE_SUB(`time`, INTERVAL SECOND(`time`)-crl_second SECOND));

SET `time`=IF(crl_weekday IS NOT NULL AND `time`<`timenow`,DATE_ADD(`time`, INTERVAL 1 WEEK),`time`);
SET `time`=IF(crl_hour IS NOT NULL AND `time`<`timenow`,DATE_ADD(`time`, INTERVAL 1 DAY),`time`);
SET `time`=IF(crl_minute IS NOT NULL AND `time`<`timenow`,DATE_ADD(`time`, INTERVAL 1 HOUR),`time`);
SET `time`=IF(crl_second IS NOT NULL AND `time`<`timenow`,DATE_ADD(`time`, INTERVAL 1 MINUTE),`time`);

SET `time`=IF(crl_days_ago,DATE_SUB(`time`, INTERVAL HOUR(`time`) HOUR),`time`);
SET `time`=IF(crl_days_ago,DATE_ADD(`time`, INTERVAL crl_days_ago DAY),`time`);
SET `time`=IF(crl_hours_ago OR crl_days_ago,DATE_SUB(`time`, INTERVAL MINUTE(`time`) MINUTE),`time`);
SET `time`=IF(crl_hours_ago,DATE_ADD(`time`, INTERVAL crl_hours_ago HOUR),`time`);
SET `time`=IF(crl_minutes_ago OR crl_hours_ago OR crl_days_ago,DATE_SUB(`time`, INTERVAL SECOND(`time`) SECOND),`time`);
SET `time`=IF(crl_minutes_ago,DATE_ADD(`time`, INTERVAL crl_minutes_ago MINUTE),`time`);
SET `time`=DATE_SUB(`time`, INTERVAL minutes_bias MINUTE);

RETURN `time`;
END$$

DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_admin`
--

DROP TABLE IF EXISTS `reserv_admin`;
CREATE TABLE IF NOT EXISTS `reserv_admin` (
  `entry` enum('login','pswdhash','email','cs-email') COLLATE utf8_unicode_ci NOT NULL COMMENT '''login'' -- admin login; ''pswdhash'' -- admin password hash; ''email'' -- admin email; ''cs-email'' -- customer service email.',
  `value` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`entry`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_answer`
--

DROP TABLE IF EXISTS `reserv_answer`;
CREATE TABLE IF NOT EXISTS `reserv_answer` (
  `a_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `as_id` bigint(20) NOT NULL,
  `qn_id` bigint(20) NOT NULL,
  `q_id` bigint(20) NOT NULL,
  `qc_id` bigint(20) DEFAULT NULL,
  `a_text` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`a_id`),
  UNIQUE KEY `as_id` (`as_id`,`qn_id`,`q_id`,`qc_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=29 ;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_answer_set`
--

DROP TABLE IF EXISTS `reserv_answer_set`;
CREATE TABLE IF NOT EXISTS `reserv_answer_set` (
  `as_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID of the answer set',
  `mb_id_answerer` bigint(20) DEFAULT NULL COMMENT 'Who answered.',
  `as_timestamp` datetime NOT NULL COMMENT 'When answered',
  PRIMARY KEY (`as_id`),
  KEY `mb_id_answerer` (`mb_id_answerer`,`as_timestamp`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=8 ;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_billboard`
--

DROP TABLE IF EXISTS `reserv_billboard`;
CREATE TABLE IF NOT EXISTS `reserv_billboard` (
  `bb_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `mb_id_author` bigint(20) NOT NULL,
  `bb_id_head` bigint(20) NOT NULL COMMENT 'All nodes of the whole message tree formed by bb_id_replyto share the same bb_id_head, which is the root bb_id.',
  `bb_id_replyto` bigint(20) DEFAULT NULL,
  `file_id` bigint(20) NOT NULL,
  `bb_timestamp` datetime NOT NULL COMMENT 'The time point that the post is first saved.',
  PRIMARY KEY (`bb_id`),
  UNIQUE KEY `bb_id_head` (`bb_id_head`,`bb_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=49 ;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_billboard_reader`
--

DROP TABLE IF EXISTS `reserv_billboard_reader`;
CREATE TABLE IF NOT EXISTS `reserv_billboard_reader` (
  `bbr_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `bb_id` bigint(20) NOT NULL,
  `cs_id` bigint(20) DEFAULT NULL COMMENT 'Too all people who are concerned with the course.  Not necessarily people who took part in the course.  ** Only for purpose of public comments, not displayed in lecture-specific threads.',
  `lctr_id` bigint(20) DEFAULT NULL COMMENT 'Too all people reserved the lecture, except those who cancelled the reservation.',
  `mb_id_reader` bigint(20) DEFAULT NULL COMMENT 'Only the specific reader can read the post.',
  PRIMARY KEY (`bbr_id`),
  UNIQUE KEY `cs_id` (`cs_id`,`lctr_id`,`mb_id_reader`,`bb_id`),
  KEY `bb_id_4` (`bb_id`,`lctr_id`),
  KEY `bb_id` (`bb_id`,`cs_id`),
  KEY `bb_id_2` (`bb_id`,`mb_id_reader`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Each row is an OR, each field in a row is an AND.' AUTO_INCREMENT=55 ;

-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_CGRB_weekly1_minutes`
--
DROP VIEW IF EXISTS `reserv_CGRB_weekly1_minutes`;
CREATE TABLE IF NOT EXISTS `reserv_CGRB_weekly1_minutes` (
`cgl_id` bigint(20)
,`cs_id` bigint(20)
,`cgv_yearweek` int(6)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_CGRB_weekly0_minutes`
--
DROP VIEW IF EXISTS `reserv_CGRB_weekly0_minutes`;
CREATE TABLE IF NOT EXISTS `reserv_CGRB_weekly0_minutes` (
`cgl_id` bigint(20)
,`cs_id` bigint(20)
,`cgv_yearweek` int(6)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_CGV_weekly1_minutes`
--
DROP VIEW IF EXISTS `reserv_CGV_weekly1_minutes`;
CREATE TABLE IF NOT EXISTS `reserv_CGV_weekly1_minutes` (
`cg_id` bigint(20)
,`cgv_yearweek` int(6)
,`cgv_minutes` decimal(42,0)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_CGV_weekly0_minutes`
--
DROP VIEW IF EXISTS `reserv_CGV_weekly0_minutes`;
CREATE TABLE IF NOT EXISTS `reserv_CGV_weekly0_minutes` (
`cg_id` bigint(20)
,`cgv_yearweek` int(6)
,`cgv_minutes` decimal(42,0)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_CGV_weekly_minutes_avail`
--
DROP VIEW IF EXISTS `reserv_CGV_weekly_minutes_avail`;
CREATE TABLE IF NOT EXISTS `reserv_CGV_weekly_minutes_avail` (
`cg_id` bigint(20)
,`cgv_yearweek0` int(6)
,`cgv_yearweek1` int(11)
,`cgv_minutes_avail` double
);
-- --------------------------------------------------------

--
-- Table structure for table `reserv_classroom`
--

DROP TABLE IF EXISTS `reserv_classroom`;
CREATE TABLE IF NOT EXISTS `reserv_classroom` (
  `clsr_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `clsr_name` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  `clsr_desc` text COLLATE utf8_unicode_ci NOT NULL,
  `mb_id_owner` bigint(20) NOT NULL,
  `clsr_status` enum('VALID','DELETED') COLLATE utf8_unicode_ci NOT NULL,
  `clsr_timestamp` datetime NOT NULL COMMENT 'Last time the record was modified.',
  `clsr_timestamp_initial` datetime NOT NULL COMMENT 'The time point when the record was created.',
  PRIMARY KEY (`clsr_id`),
  KEY `mb_id_owner` (`mb_id_owner`,`clsr_status`),
  KEY `clsr_timestamp` (`clsr_timestamp`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=26 ;

-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_classroom_occupation`
--
DROP VIEW IF EXISTS `reserv_classroom_occupation`;
CREATE TABLE IF NOT EXISTS `reserv_classroom_occupation` (
`clsr_id` bigint(20)
,`lctr_id` bigint(20)
,`lctr_since` varchar(19)
,`lctr_expire` varchar(19)
);
-- --------------------------------------------------------

--
-- Table structure for table `reserv_classroom_seat`
--

DROP TABLE IF EXISTS `reserv_classroom_seat`;
CREATE TABLE IF NOT EXISTS `reserv_classroom_seat` (
  `clsr_id` bigint(20) NOT NULL,
  `st_id` bigint(20) NOT NULL,
  PRIMARY KEY (`clsr_id`,`st_id`),
  UNIQUE KEY `st_id` (`st_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_classroom_size`
--
DROP VIEW IF EXISTS `reserv_classroom_size`;
CREATE TABLE IF NOT EXISTS `reserv_classroom_size` (
`clsr_id` bigint(20)
,`clsr_size` bigint(21)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_classroom_size_by_place`
--
DROP VIEW IF EXISTS `reserv_classroom_size_by_place`;
CREATE TABLE IF NOT EXISTS `reserv_classroom_size_by_place` (
`clsr_id` bigint(20)
,`plc_id` bigint(20)
,`clsr_size` bigint(21)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_classroom_vacancy`
--
DROP VIEW IF EXISTS `reserv_classroom_vacancy`;
CREATE TABLE IF NOT EXISTS `reserv_classroom_vacancy` (
`clsr_id` bigint(20)
,`clsrv_since` varchar(19)
,`clsrv_expire` varchar(19)
);
-- --------------------------------------------------------

--
-- Table structure for table `reserv_compere`
--

DROP TABLE IF EXISTS `reserv_compere`;
CREATE TABLE IF NOT EXISTS `reserv_compere` (
  `mb_id` bigint(20) NOT NULL,
  `cp_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `cp_timezone` int(11) NOT NULL COMMENT 'Minutes from UTC.',
  `cp_status` enum('VALID','INVALID','BANNED') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'VALID',
  `cp_timestamp` datetime NOT NULL COMMENT 'When the member become a compere.',
  PRIMARY KEY (`mb_id`),
  KEY `cp_status` (`cp_status`,`mb_id`),
  KEY `cp_timestamp` (`cp_timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_compere_info`
--

DROP TABLE IF EXISTS `reserv_compere_info`;
CREATE TABLE IF NOT EXISTS `reserv_compere_info` (
  `cp_id` bigint(20) NOT NULL,
  `cpi_key` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `cpi_value` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`cp_id`,`cpi_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_compere_limit`
--

DROP TABLE IF EXISTS `reserv_compere_limit`;
CREATE TABLE IF NOT EXISTS `reserv_compere_limit` (
  `cl_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cp_id` bigint(20) NOT NULL,
  `timeavail.count` int(11) NOT NULL DEFAULT '7',
  `seat.count` int(11) NOT NULL DEFAULT '1',
  `place.count` int(11) NOT NULL DEFAULT '1',
  `classroom.seat.count` int(11) NOT NULL DEFAULT '1',
  `classroom.count` int(11) NOT NULL DEFAULT '1',
  `lecture.count` int(11) NOT NULL DEFAULT '5',
  `course.count` int(11) NOT NULL DEFAULT '1',
  `coursegroup.count` int(11) NOT NULL DEFAULT '0',
  `reservation.count` int(11) NOT NULL DEFAULT '5',
  `option.count` int(11) NOT NULL DEFAULT '0',
  `team.size` int(11) NOT NULL DEFAULT '1',
  `question.count` int(11) NOT NULL DEFAULT '10',
  `questionnaire.count` int(11) NOT NULL DEFAULT '1',
  `cl_priority` int(11) NOT NULL DEFAULT '0' COMMENT 'The smaller the value, the higher the priority.  If multiple limits exist at the same time, the one with the highest priority become the selected one.',
  `cl_expire` datetime NOT NULL,
  `cl_timestamp_enter` datetime NOT NULL COMMENT 'The time that current record is inserted into the table and marked VALID .',
  `cl_timestamp_leave` datetime DEFAULT NULL COMMENT 'The time that current record is marked DELETED .',
  `cl_status` enum('VALID','DELETED') COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`cl_id`),
  KEY `cp_id` (`cp_id`,`cl_status`,`cl_expire`,`cl_priority`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=39 ;

-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_compere_occupation`
--
DROP VIEW IF EXISTS `reserv_compere_occupation`;
CREATE TABLE IF NOT EXISTS `reserv_compere_occupation` (
`mb_id` bigint(20)
,`lctr_id` bigint(20)
,`lctr_since` varchar(19)
,`lctr_expire` varchar(19)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_compere_qualified`
--
DROP VIEW IF EXISTS `reserv_compere_qualified`;
CREATE TABLE IF NOT EXISTS `reserv_compere_qualified` (
`mb_id` bigint(20)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_compere_vacancy`
--
DROP VIEW IF EXISTS `reserv_compere_vacancy`;
CREATE TABLE IF NOT EXISTS `reserv_compere_vacancy` (
`mb_id` bigint(20)
,`cpv_since` varchar(19)
,`cpv_expire` varchar(19)
);
-- --------------------------------------------------------

--
-- Table structure for table `reserv_course`
--

DROP TABLE IF EXISTS `reserv_course`;
CREATE TABLE IF NOT EXISTS `reserv_course` (
  `cs_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cs_member_min` int(11) NOT NULL,
  `cs_member_max` int(11) NOT NULL,
  `cs_minutes_min` int(11) NOT NULL,
  `cs_minutes_max` int(11) NOT NULL,
  `cs_minutes_step` int(11) NOT NULL,
  `cs_minutes_nonwork` int(11) NOT NULL DEFAULT '0' COMMENT 'Number of minutes, in each lecture of the course, that is consumed for nonworking activities, such as traffic, etc.',
  `cs_currency` char(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'JPY',
  `cs_price_base` double(10,2) NOT NULL,
  `cs_price_step` double(10,2) NOT NULL,
  `cs_price_taxrate` double NOT NULL DEFAULT '0',
  `cs_lecture_cost` double(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Cost that happens for each lecture.',
  `mb_id_owner` bigint(20) NOT NULL COMMENT 'The instructor_id who has ownship of the course.',
  `cs_since` datetime NOT NULL,
  `cs_expire` datetime NOT NULL,
  `cs_visibleto` enum('PUBLIC','CIRCLE') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'PUBLIC',
  `cs_initby` enum('OWNER','PARTICIPANT') COLLATE utf8_unicode_ci NOT NULL COMMENT '''PRIMARY'': Only the course owner can initiate a lecture.  ''PARTICIPANT'': Every participant can initiate a lecture.',
  `cs_autoconfirm` enum('YES','NO','ALLBUTONE') COLLATE utf8_unicode_ci NOT NULL COMMENT '''YES'': All reservations must be confirmed by compere. ''NO'': All reservations are automatically confirmed.  ''ALLBUTONE'': Reservation for a lecture will be confirmed automatically iff some reservations for the lecture have been confirmed.',
  `cs_status` enum('VALID','DELETED') COLLATE utf8_unicode_ci NOT NULL,
  `cs_timestamp` datetime NOT NULL COMMENT 'When the record was last modified.',
  `cs_timestamp_initial` datetime NOT NULL COMMENT 'When the record was created.',
  PRIMARY KEY (`cs_id`),
  KEY `cs_timestamp` (`cs_timestamp`),
  KEY `cs_since` (`cs_status`,`cs_since`,`cs_expire`),
  KEY `mb_id_owner` (`mb_id_owner`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=132 ;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_course_circle`
--

DROP TABLE IF EXISTS `reserv_course_circle`;
CREATE TABLE IF NOT EXISTS `reserv_course_circle` (
  `cs_id` bigint(20) NOT NULL,
  `mb_id` bigint(20) NOT NULL,
  `cc_timestamp` datetime NOT NULL,
  PRIMARY KEY (`cs_id`,`mb_id`),
  UNIQUE KEY `mb_id` (`mb_id`,`cs_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_course_classroom`
--

DROP TABLE IF EXISTS `reserv_course_classroom`;
CREATE TABLE IF NOT EXISTS `reserv_course_classroom` (
  `cs_id` bigint(20) NOT NULL,
  `clsr_id` bigint(20) NOT NULL,
  PRIMARY KEY (`cs_id`,`clsr_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_course_desc`
--

DROP TABLE IF EXISTS `reserv_course_desc`;
CREATE TABLE IF NOT EXISTS `reserv_course_desc` (
  `cs_id` bigint(20) NOT NULL,
  `cs_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `cs_desc` text COLLATE utf8_unicode_ci NOT NULL,
  `cs_desc_type` enum('PLAINTEXT','HTML') COLLATE utf8_unicode_ci NOT NULL,
  `cs_thumbnail_url` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`cs_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_course_group`
--

DROP TABLE IF EXISTS `reserv_course_group`;
CREATE TABLE IF NOT EXISTS `reserv_course_group` (
  `cg_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID of the course group',
  `mb_id_owner` bigint(20) NOT NULL COMMENT 'ID of owner of the group',
  `cg_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Name of the group',
  `cg_brief` text COLLATE utf8_unicode_ci NOT NULL COMMENT 'Brief description of the group',
  `cg_create_time` datetime NOT NULL,
  `cg_modify_time` datetime NOT NULL,
  `cg_status` enum('VALID','DELETED') COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`cg_id`),
  KEY `mb_id_owner` (`mb_id_owner`,`cg_status`,`cg_name`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6 ;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_course_group_course`
--

DROP TABLE IF EXISTS `reserv_course_group_course`;
CREATE TABLE IF NOT EXISTS `reserv_course_group_course` (
  `cg_id` bigint(20) NOT NULL COMMENT 'ID of the course group',
  `cs_id` bigint(20) NOT NULL COMMENT 'ID of a course in the group',
  PRIMARY KEY (`cg_id`,`cs_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_course_group_limit`
--

DROP TABLE IF EXISTS `reserv_course_group_limit`;
CREATE TABLE IF NOT EXISTS `reserv_course_group_limit` (
  `cgl_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID of the rule.',
  `cg_id` bigint(20) NOT NULL COMMENT 'ID of the course group, which is the object that the rule is applied on.',
  `cgl_view` enum('weekly0_minutes','weekly1_minutes') COLLATE utf8_unicode_ci NOT NULL COMMENT 'Name of the view on which the rule must hold',
  `cgl_limit` double NOT NULL COMMENT 'The limiting value such that the field specified by cgr_field and cgr_view must not exceed this value.',
  `cgl_status` enum('VALID','DELETED') COLLATE utf8_unicode_ci NOT NULL,
  `cgl_timestamp` datetime NOT NULL COMMENT 'Time stamp when current limit was created.',
  PRIMARY KEY (`cgl_id`),
  KEY `cg_id` (`cgl_status`,`cg_id`,`cgl_view`,`cgl_timestamp`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Define limiting rules that must hold.' AUTO_INCREMENT=19 ;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_course_info`
--

DROP TABLE IF EXISTS `reserv_course_info`;
CREATE TABLE IF NOT EXISTS `reserv_course_info` (
  `cs_id` bigint(20) NOT NULL,
  `ci_property` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `ci_value` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`cs_id`,`ci_property`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_course_info_request`
--

DROP TABLE IF EXISTS `reserv_course_info_request`;
CREATE TABLE IF NOT EXISTS `reserv_course_info_request` (
  `cs_id` bigint(20) NOT NULL,
  `id_field` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`cs_id`,`id_field`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_course_keyword`
--

DROP TABLE IF EXISTS `reserv_course_keyword`;
CREATE TABLE IF NOT EXISTS `reserv_course_keyword` (
  `cs_id` bigint(20) NOT NULL,
  `ck_word` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `ck_count` int(11) NOT NULL,
  PRIMARY KEY (`cs_id`,`ck_word`),
  UNIQUE KEY `cs_id` (`ck_word`,`cs_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_course_last_reservation`
--
DROP VIEW IF EXISTS `reserv_course_last_reservation`;
CREATE TABLE IF NOT EXISTS `reserv_course_last_reservation` (
`cs_id` bigint(20)
,`rsv_id_last` bigint(20)
,`rsv_timestamp_last` datetime
);
-- --------------------------------------------------------

--
-- Table structure for table `reserv_course_option`
--

DROP TABLE IF EXISTS `reserv_course_option`;
CREATE TABLE IF NOT EXISTS `reserv_course_option` (
  `cs_id` bigint(20) NOT NULL,
  `o_id` bigint(20) NOT NULL,
  PRIMARY KEY (`cs_id`,`o_id`),
  UNIQUE KEY `cs_id` (`o_id`,`cs_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_course_questionnaire`
--

DROP TABLE IF EXISTS `reserv_course_questionnaire`;
CREATE TABLE IF NOT EXISTS `reserv_course_questionnaire` (
  `cs_id` bigint(20) NOT NULL,
  `qn_id` bigint(20) NOT NULL,
  PRIMARY KEY (`cs_id`),
  KEY `qn_id` (`qn_id`,`cs_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_course_reservation_count`
--
DROP VIEW IF EXISTS `reserv_course_reservation_count`;
CREATE TABLE IF NOT EXISTS `reserv_course_reservation_count` (
`cs_id` bigint(20)
,`crc_count` bigint(21)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_course_reservation_count_thisyear`
--
DROP VIEW IF EXISTS `reserv_course_reservation_count_thisyear`;
CREATE TABLE IF NOT EXISTS `reserv_course_reservation_count_thisyear` (
`cs_id` bigint(20)
,`crct_count` bigint(21)
);
-- --------------------------------------------------------

--
-- Table structure for table `reserv_course_reserve_deadline`
--

DROP TABLE IF EXISTS `reserv_course_reserve_deadline`;
CREATE TABLE IF NOT EXISTS `reserv_course_reserve_deadline` (
  `cs_id` bigint(20) NOT NULL,
  `crd_days_ago` int(11) DEFAULT NULL,
  `crd_hours_ago` int(11) DEFAULT NULL,
  `crd_minutes_ago` int(11) DEFAULT NULL,
  `crd_weekday` int(11) DEFAULT NULL,
  `crd_hour` int(11) DEFAULT NULL,
  `crd_minute` int(11) DEFAULT NULL,
  PRIMARY KEY (`cs_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_course_staff`
--

DROP TABLE IF EXISTS `reserv_course_staff`;
CREATE TABLE IF NOT EXISTS `reserv_course_staff` (
  `cs_id` bigint(20) NOT NULL,
  `mb_id` bigint(20) NOT NULL,
  PRIMARY KEY (`mb_id`,`cs_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_course_timeavail`
--

DROP TABLE IF EXISTS `reserv_course_timeavail`;
CREATE TABLE IF NOT EXISTS `reserv_course_timeavail` (
  `ta_id` bigint(20) NOT NULL,
  `cs_id` bigint(20) NOT NULL,
  PRIMARY KEY (`ta_id`,`cs_id`),
  UNIQUE KEY `cs_id` (`cs_id`,`ta_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_course_weekly_minutes_avail`
--
DROP VIEW IF EXISTS `reserv_course_weekly_minutes_avail`;
CREATE TABLE IF NOT EXISTS `reserv_course_weekly_minutes_avail` (
`cs_id` bigint(20)
,`cgv_yearweek0` int(6)
,`cgv_yearweek1` int(11)
,`cgv_minutes_avail` double
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_course_weekly_minutes_max`
--
DROP VIEW IF EXISTS `reserv_course_weekly_minutes_max`;
CREATE TABLE IF NOT EXISTS `reserv_course_weekly_minutes_max` (
`cs_id` bigint(20)
,`cgl_limit_max` double
);
-- --------------------------------------------------------

--
-- Table structure for table `reserv_file`
--

DROP TABLE IF EXISTS `reserv_file`;
CREATE TABLE IF NOT EXISTS `reserv_file` (
  `file_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `fh_id` bigint(20) NOT NULL,
  PRIMARY KEY (`file_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=397 ;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_file_history`
--

DROP TABLE IF EXISTS `reserv_file_history`;
CREATE TABLE IF NOT EXISTS `reserv_file_history` (
  `fh_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `fh_id_updates` bigint(20) DEFAULT NULL COMMENT 'ID of the history file that current file updates',
  `fh_brief` text COLLATE utf8_unicode_ci COMMENT 'Used as abstract, summary, or title.',
  `fh_text` text COLLATE utf8_unicode_ci,
  `fh_timestamp` datetime NOT NULL,
  PRIMARY KEY (`fh_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=405 ;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_info_disclose`
--

DROP TABLE IF EXISTS `reserv_info_disclose`;
CREATE TABLE IF NOT EXISTS `reserv_info_disclose` (
  `mb_id_from` bigint(20) NOT NULL,
  `mb_id_team_leader` bigint(20) NOT NULL COMMENT 'mb_id of the leader of the team to whom info is disclosed.',
  `id_field` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`mb_id_from`,`mb_id_team_leader`,`id_field`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_lecture`
--

DROP TABLE IF EXISTS `reserv_lecture`;
CREATE TABLE IF NOT EXISTS `reserv_lecture` (
  `lctr_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cs_id` bigint(20) NOT NULL,
  `clsr_id` bigint(20) NOT NULL,
  `lctr_since` datetime NOT NULL,
  `lctr_expire` datetime NOT NULL,
  `lctr_deadline` datetime NOT NULL COMMENT 'Deadline of reservation.',
  `lctr_status` enum('OPEN','HIDDEN','CLOSED','DELETED') COLLATE utf8_unicode_ci NOT NULL COMMENT '''OPEN'': accepts seat reservations from participants.  ''HIDDEN'': same as OPEN except not publicly listed.  ''CLOSED'': only reservable by owner.  ''DELETED'': the lecture has been cancelled.',
  PRIMARY KEY (`lctr_id`),
  KEY `lctr_expire` (`lctr_status`,`lctr_expire`,`lctr_since`),
  KEY `cs_since` (`lctr_status`,`cs_id`,`lctr_since`,`lctr_expire`,`lctr_deadline`),
  KEY `cs_deadline` (`lctr_status`,`cs_id`,`lctr_deadline`,`lctr_since`,`lctr_expire`),
  KEY `lctr_since` (`lctr_status`,`lctr_since`,`lctr_expire`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=159 ;

-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_lecture_addable`
--
DROP VIEW IF EXISTS `reserv_lecture_addable`;
CREATE TABLE IF NOT EXISTS `reserv_lecture_addable` (
`cs_id` bigint(20)
,`mb_id` bigint(20)
,`la_since` varchar(19)
,`la_expire` varchar(19)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_lecture_addable_with_deadline`
--
DROP VIEW IF EXISTS `reserv_lecture_addable_with_deadline`;
CREATE TABLE IF NOT EXISTS `reserv_lecture_addable_with_deadline` (
`cs_id` bigint(20)
,`mb_id` bigint(20)
,`la_since` varchar(19)
,`la_expire` varchar(19)
,`lctr_id` binary(0)
,`la_deadline` datetime
,`cs_minutes_min` int(11)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_lecture_income`
--
DROP VIEW IF EXISTS `reserv_lecture_income`;
CREATE TABLE IF NOT EXISTS `reserv_lecture_income` (
`lctr_id` bigint(20)
,`li_revenue` double(19,2)
,`li_tax` double(19,2)
,`li_expense` double(10,2)
,`li_count_participant` bigint(21)
,`li_count_participant_charged` bigint(21)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_lecture_size`
--
DROP VIEW IF EXISTS `reserv_lecture_size`;
CREATE TABLE IF NOT EXISTS `reserv_lecture_size` (
`lctr_id` bigint(20)
,`lctr_size` bigint(21)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_lecture_size_by_place_classroom`
--
DROP VIEW IF EXISTS `reserv_lecture_size_by_place_classroom`;
CREATE TABLE IF NOT EXISTS `reserv_lecture_size_by_place_classroom` (
`lctr_id` bigint(20)
,`plc_id` bigint(20)
,`clsr_id` bigint(20)
,`lctr_size` bigint(21)
,`clsr_size` bigint(21)
);
-- --------------------------------------------------------

--
-- Table structure for table `reserv_lecture_staff`
--

DROP TABLE IF EXISTS `reserv_lecture_staff`;
CREATE TABLE IF NOT EXISTS `reserv_lecture_staff` (
  `lctr_id` bigint(20) NOT NULL,
  `mb_id` bigint(20) NOT NULL COMMENT 'ID of the compere.',
  PRIMARY KEY (`lctr_id`,`mb_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_lecture_unit_income`
--

DROP TABLE IF EXISTS `reserv_lecture_unit_income`;
CREATE TABLE IF NOT EXISTS `reserv_lecture_unit_income` (
  `lctr_id` bigint(20) NOT NULL,
  `lui_revenue` double(10,2) NOT NULL COMMENT 'Per RESERVATION revenue',
  `lui_expense` double(10,2) NOT NULL COMMENT 'Per LECTURE expense',
  `lui_tax` double(10,2) NOT NULL COMMENT 'Per RESERVATION tax, not in revenue',
  PRIMARY KEY (`lctr_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_message`
--

DROP TABLE IF EXISTS `reserv_message`;
CREATE TABLE IF NOT EXISTS `reserv_message` (
  `msg_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `mb_id_from` bigint(20) DEFAULT NULL COMMENT 'NULL if user is not logged in.',
  `msg_from_as` enum('MEMBER','COMPERE','GUEST') COLLATE utf8_unicode_ci NOT NULL,
  `mb_id_to` bigint(20) DEFAULT NULL COMMENT 'NULL if it is sent to the administrator.',
  `msg_to_as` enum('MEMBER','COMPERE','GUEST') COLLATE utf8_unicode_ci NOT NULL,
  `msg_type` enum('INVITE','INQUIRY','RESERVE','CANCEL','REPLY','DECLINE','MESSAGE') COLLATE utf8_unicode_ci NOT NULL,
  `msg_decision` enum('ACCEPT','DENY') COLLATE utf8_unicode_ci DEFAULT NULL,
  `msg_cs_id` bigint(20) DEFAULT NULL,
  `msg_lctr_id` bigint(20) DEFAULT NULL,
  `msg_st_id` bigint(20) DEFAULT NULL,
  `msg_id_replyto` bigint(20) DEFAULT NULL,
  `file_id` bigint(20) NOT NULL,
  PRIMARY KEY (`msg_id`),
  KEY `mb_id_to` (`mb_id_to`,`file_id`),
  KEY `msg_id` (`msg_id`,`file_id`),
  KEY `msg_id_replyto` (`msg_id_replyto`,`msg_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=291 ;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_message_attrib`
--

DROP TABLE IF EXISTS `reserv_message_attrib`;
CREATE TABLE IF NOT EXISTS `reserv_message_attrib` (
  `msg_id` bigint(20) NOT NULL,
  `msga_prop` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `msga_value` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`msg_id`,`msga_prop`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_message_read`
--

DROP TABLE IF EXISTS `reserv_message_read`;
CREATE TABLE IF NOT EXISTS `reserv_message_read` (
  `msg_id` bigint(20) NOT NULL,
  `mb_id` bigint(20) NOT NULL,
  `msg_read` tinyint(1) NOT NULL DEFAULT '1',
  `msgr_timestamp` datetime NOT NULL,
  PRIMARY KEY (`msg_id`,`mb_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_option`
--

DROP TABLE IF EXISTS `reserv_option`;
CREATE TABLE IF NOT EXISTS `reserv_option` (
  `o_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `mb_id_owner` bigint(20) NOT NULL,
  `o_ordinal` int(11) DEFAULT NULL,
  `o_price_base` double(10,2) NOT NULL,
  `o_price_step` double(10,2) NOT NULL,
  `o_num_min` int(11) NOT NULL DEFAULT '0',
  `o_num_max` int(11) NOT NULL DEFAULT '99',
  `o_thumbnail_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `o_status` enum('VALID','DELETED') COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`o_id`),
  KEY `cs_id` (`mb_id_owner`,`o_status`,`o_ordinal`,`o_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=9 ;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_option_desc`
--

DROP TABLE IF EXISTS `reserv_option_desc`;
CREATE TABLE IF NOT EXISTS `reserv_option_desc` (
  `o_id` bigint(20) NOT NULL,
  `o_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `o_desc` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`o_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_place`
--

DROP TABLE IF EXISTS `reserv_place`;
CREATE TABLE IF NOT EXISTS `reserv_place` (
  `plc_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `mb_id_owner` bigint(20) NOT NULL,
  `plc_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `plc_country` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `plc_zipcode` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `plc_state` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `plc_city` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `plc_address` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `plc_room` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `plc_phone` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `plc_timestamp` datetime NOT NULL COMMENT 'When the record was last modified.',
  `plc_timestamp_initial` datetime NOT NULL COMMENT 'When the record was last modified.',
  `plc_status` enum('VALID','DELETED') COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`plc_id`),
  KEY `mb_id_owner` (`mb_id_owner`,`plc_timestamp`),
  KEY `plc_state` (`plc_state`,`plc_city`,`plc_address`),
  KEY `plc_zipcode` (`plc_zipcode`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=21 ;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_question`
--

DROP TABLE IF EXISTS `reserv_question`;
CREATE TABLE IF NOT EXISTS `reserv_question` (
  `q_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `mb_id_owner` bigint(20) NOT NULL,
  `q_type` enum('SINGLE','MULTI','STATE') COLLATE utf8_unicode_ci NOT NULL COMMENT 'SINGLE: single choice; MULTI: multiple choice; STATE: statement',
  `q_text` text COLLATE utf8_unicode_ci COMMENT 'Question text',
  `q_status` enum('VALID','DELETED') COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`q_id`),
  KEY `byowner` (`mb_id_owner`,`q_status`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=15 ;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_questionnaire`
--

DROP TABLE IF EXISTS `reserv_questionnaire`;
CREATE TABLE IF NOT EXISTS `reserv_questionnaire` (
  `qn_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `mb_id_owner` bigint(20) NOT NULL,
  `qn_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `qn_desc` text COLLATE utf8_unicode_ci NOT NULL,
  `qn_status` enum('VALID','DELETED') COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`qn_id`),
  KEY `qn_id` (`mb_id_owner`,`qn_status`,`qn_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_questionnaire_question`
--

DROP TABLE IF EXISTS `reserv_questionnaire_question`;
CREATE TABLE IF NOT EXISTS `reserv_questionnaire_question` (
  `qn_id` bigint(20) NOT NULL,
  `q_id` bigint(20) NOT NULL,
  `qnq_required` tinyint(1) NOT NULL DEFAULT '1',
  `qnq_ordinal` int(11) NOT NULL,
  PRIMARY KEY (`qn_id`,`q_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_question_choice`
--

DROP TABLE IF EXISTS `reserv_question_choice`;
CREATE TABLE IF NOT EXISTS `reserv_question_choice` (
  `qc_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `q_id` bigint(20) NOT NULL,
  `qc_ordinal` int(11) NOT NULL,
  `qc_text` text COLLATE utf8_unicode_ci NOT NULL,
  `qc_status` enum('VALID','DELETED') COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`qc_id`),
  KEY `cq_id` (`q_id`,`qc_status`,`qc_ordinal`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16 ;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_reservation`
--

DROP TABLE IF EXISTS `reserv_reservation`;
CREATE TABLE IF NOT EXISTS `reserv_reservation` (
  `rsv_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `lctr_id` bigint(20) NOT NULL,
  `st_id` bigint(20) NOT NULL,
  `mb_id` bigint(20) NOT NULL COMMENT 'The participant (customer) who ordered the reservation.',
  `rsv_timestamp_provisional` datetime NOT NULL,
  `rsv_timestamp_confirmed` datetime DEFAULT NULL COMMENT 'NULL for unconfirmed.',
  `mb_id_reserver` bigint(20) NOT NULL COMMENT 'Who made the reservation.',
  `rsv_absent` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Marked 1 if absent without cancelling.',
  PRIMARY KEY (`rsv_id`),
  UNIQUE KEY `lctr_id_2` (`lctr_id`,`st_id`,`mb_id`),
  KEY `mb_id` (`mb_id`,`rsv_timestamp_provisional`),
  KEY `lctr_id` (`lctr_id`,`mb_id`),
  KEY `rsv_timestamp_provisional` (`rsv_timestamp_provisional`,`rsv_timestamp_confirmed`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=201 ;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_reservation_answer_set`
--

DROP TABLE IF EXISTS `reserv_reservation_answer_set`;
CREATE TABLE IF NOT EXISTS `reserv_reservation_answer_set` (
  `rsv_id` bigint(20) NOT NULL,
  `as_id` bigint(20) NOT NULL,
  PRIMARY KEY (`rsv_id`,`as_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_reservation_cancel`
--

DROP TABLE IF EXISTS `reserv_reservation_cancel`;
CREATE TABLE IF NOT EXISTS `reserv_reservation_cancel` (
  `rsv_id` bigint(20) NOT NULL,
  `mb_id_cancelby` bigint(20) NOT NULL,
  `rsvc_timestamp` datetime NOT NULL,
  `lctr_id` bigint(20) NOT NULL,
  `st_id` bigint(20) NOT NULL,
  `mb_id` bigint(20) NOT NULL COMMENT 'The participant (customer) who ordered the reservation.',
  `rsv_timestamp_provisional` datetime NOT NULL,
  `rsv_timestamp_confirmed` datetime DEFAULT NULL COMMENT 'NULL for unconfirmed.',
  `mb_id_reserver` bigint(20) NOT NULL,
  KEY `mb_id` (`mb_id`,`rsv_timestamp_provisional`),
  KEY `lctr_id` (`lctr_id`,`mb_id`),
  KEY `rsv_id` (`rsv_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_reservation_option`
--

DROP TABLE IF EXISTS `reserv_reservation_option`;
CREATE TABLE IF NOT EXISTS `reserv_reservation_option` (
  `rsv_id` bigint(20) NOT NULL,
  `o_id` bigint(20) NOT NULL,
  `ro_count` int(11) NOT NULL,
  `ro_unit_price` double(10,2) NOT NULL COMMENT 'Unit price of the option based on the lecture, without tax.',
  `ro_unit_tax` double(10,2) NOT NULL COMMENT 'Tax over ro_unit_price.',
  PRIMARY KEY (`rsv_id`,`o_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_seat`
--

DROP TABLE IF EXISTS `reserv_seat`;
CREATE TABLE IF NOT EXISTS `reserv_seat` (
  `st_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Seat ID',
  `plc_id` bigint(20) NOT NULL COMMENT 'Place ID',
  `st_no` int(11) NOT NULL COMMENT 'Seat No. within the place.',
  `st_status` enum('VALID','DELETED') COLLATE utf8_unicode_ci NOT NULL,
  `st_timestamp_initial` datetime NOT NULL COMMENT 'When the record was created.',
  PRIMARY KEY (`st_id`),
  UNIQUE KEY `place_to_seat_number` (`plc_id`,`st_status`,`st_no`,`st_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=411 ;

-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_seat_occupation`
--
DROP VIEW IF EXISTS `reserv_seat_occupation`;
CREATE TABLE IF NOT EXISTS `reserv_seat_occupation` (
`st_id` bigint(20)
,`lctr_id` bigint(20)
,`lctr_since` varchar(19)
,`lctr_expire` varchar(19)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_seat_reservation`
--
DROP VIEW IF EXISTS `reserv_seat_reservation`;
CREATE TABLE IF NOT EXISTS `reserv_seat_reservation` (
`lctr_id` bigint(20)
,`cs_id` bigint(20)
,`clsr_id` bigint(20)
,`lctr_since` datetime
,`lctr_expire` datetime
,`lctr_deadline` datetime
,`lctr_status` enum('OPEN','HIDDEN','CLOSED','DELETED')
,`rsv_id` bigint(20)
,`st_id` bigint(20)
,`mb_id` bigint(20)
,`rsv_timestamp_provisional` datetime
,`rsv_timestamp_confirmed` datetime
,`mb_id_reserver` bigint(20)
,`rsv_absent` tinyint(1)
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_seat_vacancy`
--
DROP VIEW IF EXISTS `reserv_seat_vacancy`;
CREATE TABLE IF NOT EXISTS `reserv_seat_vacancy` (
`st_id` bigint(20)
,`stv_since` varchar(19)
,`stv_expire` varchar(19)
);
-- --------------------------------------------------------

--
-- Table structure for table `reserv_supported_currency`
--

DROP TABLE IF EXISTS `reserv_supported_currency`;
CREATE TABLE IF NOT EXISTS `reserv_supported_currency` (
  `crny_name` char(3) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`crny_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_team`
--

DROP TABLE IF EXISTS `reserv_team`;
CREATE TABLE IF NOT EXISTS `reserv_team` (
  `mb_id_leader` bigint(20) NOT NULL,
  `mb_id_member` bigint(20) NOT NULL,
  `tm_status` enum('INVITED','VALID','EXPIRED') COLLATE utf8_unicode_ci NOT NULL,
  `file_id` bigint(20) DEFAULT NULL,
  `tm_is_alias` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'True if this member is added as an alias.',
  PRIMARY KEY (`mb_id_leader`,`mb_id_member`),
  KEY `tm_status` (`tm_status`,`mb_id_leader`,`mb_id_member`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reserv_timeavail`
--

DROP TABLE IF EXISTS `reserv_timeavail`;
CREATE TABLE IF NOT EXISTS `reserv_timeavail` (
  `ta_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `mb_id` bigint(20) NOT NULL,
  `ta_begin` datetime NOT NULL,
  `ta_end` datetime NOT NULL,
  `ta_status` enum('VALID','INVALID') COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`ta_id`),
  KEY `mb_id` (`mb_id`,`ta_status`,`ta_begin`,`ta_end`),
  KEY `ta_end` (`ta_status`,`ta_end`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1906 ;

-- --------------------------------------------------------

--
-- Stand-in structure for view `reserv_timeavail_effective`
--
DROP VIEW IF EXISTS `reserv_timeavail_effective`;
CREATE TABLE IF NOT EXISTS `reserv_timeavail_effective` (
`ta_id` bigint(20)
,`mb_id` bigint(20)
,`ta_begin` datetime
,`ta_end` datetime
,`ta_status` enum('VALID','INVALID')
);
-- --------------------------------------------------------

--
-- Table structure for table `reserv_validation_code`
--

DROP TABLE IF EXISTS `reserv_validation_code`;
CREATE TABLE IF NOT EXISTS `reserv_validation_code` (
  `vc_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `vc_code` bigint(20) NOT NULL COMMENT 'Program computed verification code.',
  `vc_timestamp` datetime NOT NULL,
  PRIMARY KEY (`vc_id`),
  KEY `vc_timestamp` (`vc_timestamp`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=20 ;

-- --------------------------------------------------------

--
-- Structure for view `reserv_CGRB_weekly1_minutes`
--
DROP TABLE IF EXISTS `reserv_CGRB_weekly1_minutes`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_CGRB_weekly1_minutes` AS select `reserv`.`reserv_course_group_limit`.`cgl_id` AS `cgl_id`,`reserv`.`reserv_course_group_course`.`cs_id` AS `cs_id`,`reserv_CGV_weekly1_minutes`.`cgv_yearweek` AS `cgv_yearweek` from ((((`reserv`.`reserv_CGV_weekly1_minutes` join `reserv`.`reserv_course_group_course` on((`reserv_CGV_weekly1_minutes`.`cg_id` = `reserv`.`reserv_course_group_course`.`cg_id`))) join `reserv`.`reserv_course` on((`reserv`.`reserv_course_group_course`.`cs_id` = `reserv`.`reserv_course`.`cs_id`))) join `reserv`.`reserv_course_group_limit` on((`reserv_CGV_weekly1_minutes`.`cg_id` = `reserv`.`reserv_course_group_limit`.`cg_id`))) join `reserv`.`reserv_course_group` on(((`reserv_CGV_weekly1_minutes`.`cg_id` = `reserv`.`reserv_course_group`.`cg_id`) and (`reserv`.`reserv_course`.`mb_id_owner` = `reserv`.`reserv_course_group`.`mb_id_owner`)))) where ((`reserv`.`reserv_course_group_limit`.`cgl_status` = _utf8'VALID') and (`reserv`.`reserv_course`.`cs_status` = _utf8'VALID') and (`reserv`.`reserv_course_group`.`cg_status` = _utf8'VALID') and (`reserv`.`reserv_course_group_limit`.`cgl_view` = _utf8'weekly1_minutes') and ((`reserv_CGV_weekly1_minutes`.`cgv_minutes` + `reserv`.`reserv_course`.`cs_minutes_min`) > `reserv`.`reserv_course_group_limit`.`cgl_limit`));

-- --------------------------------------------------------

--
-- Structure for view `reserv_CGRB_weekly0_minutes`
--
DROP TABLE IF EXISTS `reserv_CGRB_weekly0_minutes`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_CGRB_weekly0_minutes` AS select `reserv`.`reserv_course_group_limit`.`cgl_id` AS `cgl_id`,`reserv`.`reserv_course_group_course`.`cs_id` AS `cs_id`,`reserv_CGV_weekly0_minutes`.`cgv_yearweek` AS `cgv_yearweek` from ((((`reserv`.`reserv_CGV_weekly0_minutes` join `reserv`.`reserv_course_group_course` on((`reserv_CGV_weekly0_minutes`.`cg_id` = `reserv`.`reserv_course_group_course`.`cg_id`))) join `reserv`.`reserv_course` on((`reserv`.`reserv_course_group_course`.`cs_id` = `reserv`.`reserv_course`.`cs_id`))) join `reserv`.`reserv_course_group_limit` on((`reserv_CGV_weekly0_minutes`.`cg_id` = `reserv`.`reserv_course_group_limit`.`cg_id`))) join `reserv`.`reserv_course_group` on(((`reserv_CGV_weekly0_minutes`.`cg_id` = `reserv`.`reserv_course_group`.`cg_id`) and (`reserv`.`reserv_course`.`mb_id_owner` = `reserv`.`reserv_course_group`.`mb_id_owner`)))) where ((`reserv`.`reserv_course_group_limit`.`cgl_status` = _utf8'VALID') and (`reserv`.`reserv_course`.`cs_status` = _utf8'VALID') and (`reserv`.`reserv_course_group`.`cg_status` = _utf8'VALID') and (`reserv`.`reserv_course_group_limit`.`cgl_view` = _utf8'weekly0_minutes') and ((`reserv_CGV_weekly0_minutes`.`cgv_minutes` + `reserv`.`reserv_course`.`cs_minutes_min`) > `reserv`.`reserv_course_group_limit`.`cgl_limit`));

-- --------------------------------------------------------

--
-- Structure for view `reserv_CGV_weekly1_minutes`
--
DROP TABLE IF EXISTS `reserv_CGV_weekly1_minutes`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_CGV_weekly1_minutes` AS select `reserv`.`reserv_course_group_course`.`cg_id` AS `cg_id`,yearweek((`reserv`.`reserv_lecture`.`lctr_since` + `reserv`.`uniwits_member`.`mb_timezone_minutes`),1) AS `cgv_yearweek`,sum(timestampdiff(MINUTE,`reserv`.`reserv_lecture`.`lctr_since`,`reserv`.`reserv_lecture`.`lctr_expire`)) AS `cgv_minutes` from (((`reserv`.`reserv_lecture` join `reserv`.`reserv_course_group_course` on((`reserv`.`reserv_lecture`.`cs_id` = `reserv`.`reserv_course_group_course`.`cs_id`))) join `reserv`.`reserv_course` on((`reserv`.`reserv_lecture`.`cs_id` = `reserv`.`reserv_course`.`cs_id`))) join `reserv`.`uniwits_member` on((`reserv`.`uniwits_member`.`mb_id` = `reserv`.`reserv_course`.`mb_id_owner`))) where ((`reserv`.`reserv_lecture`.`lctr_status` <> 'DELETED') and (`reserv`.`reserv_course`.`cs_status` = 'VALID')) group by `reserv`.`reserv_course_group_course`.`cg_id`,yearweek((`reserv`.`reserv_lecture`.`lctr_since` + `reserv`.`uniwits_member`.`mb_timezone_minutes`),1);

-- --------------------------------------------------------

--
-- Structure for view `reserv_CGV_weekly0_minutes`
--
DROP TABLE IF EXISTS `reserv_CGV_weekly0_minutes`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_CGV_weekly0_minutes` AS select `reserv`.`reserv_course_group_course`.`cg_id` AS `cg_id`,yearweek((`reserv`.`reserv_lecture`.`lctr_since` + `reserv`.`uniwits_member`.`mb_timezone_minutes`),0) AS `cgv_yearweek`,sum(timestampdiff(MINUTE,`reserv`.`reserv_lecture`.`lctr_since`,`reserv`.`reserv_lecture`.`lctr_expire`)) AS `cgv_minutes` from (((`reserv`.`reserv_lecture` join `reserv`.`reserv_course_group_course` on((`reserv`.`reserv_lecture`.`cs_id` = `reserv`.`reserv_course_group_course`.`cs_id`))) join `reserv`.`reserv_course` on((`reserv`.`reserv_lecture`.`cs_id` = `reserv`.`reserv_course`.`cs_id`))) join `reserv`.`uniwits_member` on((`reserv`.`uniwits_member`.`mb_id` = `reserv`.`reserv_course`.`mb_id_owner`))) where ((`reserv`.`reserv_lecture`.`lctr_status` <> 'DELETED') and (`reserv`.`reserv_course`.`cs_status` = 'VALID')) group by `reserv`.`reserv_course_group_course`.`cg_id`,yearweek((`reserv`.`reserv_lecture`.`lctr_since` + `reserv`.`uniwits_member`.`mb_timezone_minutes`),0);

-- --------------------------------------------------------

--
-- Structure for view `reserv_CGV_weekly_minutes_avail`
--
DROP TABLE IF EXISTS `reserv_CGV_weekly_minutes_avail`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_CGV_weekly_minutes_avail` AS (select `reserv`.`reserv_course_group_limit`.`cg_id` AS `cg_id`,`reserv_CGV_weekly0_minutes`.`cgv_yearweek` AS `cgv_yearweek0`,NULL AS `cgv_yearweek1`,(`reserv`.`reserv_course_group_limit`.`cgl_limit` - `reserv_CGV_weekly0_minutes`.`cgv_minutes`) AS `cgv_minutes_avail` from (((`reserv`.`reserv_course_group_limit` left join `reserv`.`reserv_course_group_course` on((`reserv`.`reserv_course_group_limit`.`cg_id` = `reserv`.`reserv_course_group_course`.`cg_id`))) left join `reserv`.`reserv_course_group` on((`reserv`.`reserv_course_group_limit`.`cg_id` = `reserv`.`reserv_course_group`.`cg_id`))) left join `reserv`.`reserv_CGV_weekly0_minutes` on((`reserv`.`reserv_course_group_limit`.`cg_id` = `reserv_CGV_weekly0_minutes`.`cg_id`))) where ((`reserv`.`reserv_course_group_limit`.`cgl_status` = _utf8'VALID') and (`reserv`.`reserv_course_group`.`cg_status` = _utf8'VALID') and (`reserv`.`reserv_course_group_limit`.`cgl_view` = _utf8'weekly0_minutes'))) union (select `reserv`.`reserv_course_group_limit`.`cg_id` AS `cg_id`,NULL AS `cgv_yearweek0`,`reserv_CGV_weekly1_minutes`.`cgv_yearweek` AS `cgv_yearweek1`,(`reserv`.`reserv_course_group_limit`.`cgl_limit` - `reserv_CGV_weekly1_minutes`.`cgv_minutes`) AS `cgv_minutes_avail` from (((`reserv`.`reserv_course_group_limit` left join `reserv`.`reserv_course_group_course` on((`reserv`.`reserv_course_group_limit`.`cg_id` = `reserv`.`reserv_course_group_course`.`cg_id`))) left join `reserv`.`reserv_course_group` on((`reserv`.`reserv_course_group_limit`.`cg_id` = `reserv`.`reserv_course_group`.`cg_id`))) left join `reserv`.`reserv_CGV_weekly1_minutes` on((`reserv`.`reserv_course_group_limit`.`cg_id` = `reserv_CGV_weekly1_minutes`.`cg_id`))) where ((`reserv`.`reserv_course_group_limit`.`cgl_status` = _utf8'VALID') and (`reserv`.`reserv_course_group`.`cg_status` = _utf8'VALID') and (`reserv`.`reserv_course_group_limit`.`cgl_view` = _utf8'weekly1_minutes')));

-- --------------------------------------------------------

--
-- Structure for view `reserv_classroom_occupation`
--
DROP TABLE IF EXISTS `reserv_classroom_occupation`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_classroom_occupation` AS (select `reserv`.`reserv_classroom`.`clsr_id` AS `clsr_id`,0 AS `lctr_id`,'1000-01-01 00:00:00' AS `lctr_since`,'1000-01-01 00:00:01' AS `lctr_expire` from `reserv`.`reserv_classroom`) union (select `reserv`.`reserv_classroom`.`clsr_id` AS `clsr_id`,0 AS `lctr_id`,'9999-12-31 23:59:58' AS `lctr_since`,'9999-12-31 23:59:59' AS `lctr_expire` from `reserv`.`reserv_classroom`) union (select `reserv`.`reserv_lecture`.`clsr_id` AS `clsr_id`,`reserv`.`reserv_lecture`.`lctr_id` AS `lctr_id`,`reserv`.`reserv_lecture`.`lctr_since` AS `lctr_since`,`reserv`.`reserv_lecture`.`lctr_expire` AS `lctr_expire` from `reserv`.`reserv_lecture` where ((`reserv`.`reserv_lecture`.`lctr_status` <> 'DELETED') and (`reserv`.`reserv_lecture`.`lctr_since` >= utc_timestamp()))) order by `clsr_id`,`lctr_since`;

-- --------------------------------------------------------

--
-- Structure for view `reserv_classroom_size`
--
DROP TABLE IF EXISTS `reserv_classroom_size`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_classroom_size` AS select `reserv`.`reserv_classroom`.`clsr_id` AS `clsr_id`,count(`reserv`.`reserv_classroom_seat`.`st_id`) AS `clsr_size` from (((`reserv`.`reserv_classroom` left join `reserv`.`reserv_classroom_seat` on((`reserv`.`reserv_classroom`.`clsr_id` = `reserv`.`reserv_classroom_seat`.`clsr_id`))) left join `reserv`.`reserv_seat` on((`reserv`.`reserv_classroom_seat`.`st_id` = `reserv`.`reserv_seat`.`st_id`))) left join `reserv`.`reserv_place` on(((`reserv`.`reserv_classroom`.`mb_id_owner` = `reserv`.`reserv_place`.`mb_id_owner`) and (`reserv`.`reserv_seat`.`plc_id` = `reserv`.`reserv_place`.`plc_id`)))) where ((`reserv`.`reserv_classroom`.`clsr_status` = 'VALID') and (`reserv`.`reserv_seat`.`st_status` = 'VALID') and (`reserv`.`reserv_place`.`plc_status` = 'VALID')) group by `reserv`.`reserv_classroom`.`clsr_id`;

-- --------------------------------------------------------

--
-- Structure for view `reserv_classroom_size_by_place`
--
DROP TABLE IF EXISTS `reserv_classroom_size_by_place`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_classroom_size_by_place` AS select `c`.`clsr_id` AS `clsr_id`,`s`.`plc_id` AS `plc_id`,count(`cs`.`st_id`) AS `clsr_size` from (((`reserv`.`reserv_classroom` `c` left join `reserv`.`reserv_classroom_seat` `cs` on((`c`.`clsr_id` = `cs`.`clsr_id`))) left join `reserv`.`reserv_seat` `s` on((`cs`.`st_id` = `s`.`st_id`))) left join `reserv`.`reserv_place` on((`s`.`plc_id` = `reserv`.`reserv_place`.`plc_id`))) where ((`c`.`clsr_status` = 'VALID') and (`s`.`st_status` = 'VALID') and (`reserv`.`reserv_place`.`plc_status` = 'VALID')) group by `c`.`clsr_id`,`s`.`plc_id`;

-- --------------------------------------------------------

--
-- Structure for view `reserv_classroom_vacancy`
--
DROP TABLE IF EXISTS `reserv_classroom_vacancy`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_classroom_vacancy` AS select `b`.`clsr_id` AS `clsr_id`,(select max(`a`.`lctr_expire`) AS `MAX(a.``lctr_expire``)` from `reserv`.`reserv_classroom_occupation` `a` where ((`a`.`clsr_id` = `b`.`clsr_id`) and (`a`.`lctr_expire` <= `b`.`lctr_since`))) AS `clsrv_since`,`b`.`lctr_since` AS `clsrv_expire` from `reserv`.`reserv_classroom_occupation` `b` where (`b`.`lctr_since` > utc_timestamp()) group by `b`.`clsr_id`,`b`.`lctr_since` order by `b`.`clsr_id`,(select max(`a`.`lctr_expire`) AS `MAX(a.``lctr_expire``)` from `reserv`.`reserv_classroom_occupation` `a` where ((`a`.`clsr_id` = `b`.`clsr_id`) and (`a`.`lctr_expire` <= `b`.`lctr_since`)));

-- --------------------------------------------------------

--
-- Structure for view `reserv_compere_occupation`
--
DROP TABLE IF EXISTS `reserv_compere_occupation`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_compere_occupation` AS (select `reserv`.`reserv_compere`.`mb_id` AS `mb_id`,0 AS `lctr_id`,'1000-01-01 00:00:00' AS `lctr_since`,'1000-01-01 00:00:01' AS `lctr_expire` from `reserv`.`reserv_compere`) union (select `reserv`.`reserv_compere`.`mb_id` AS `mb_id`,0 AS `lctr_id`,'9999-12-31 23:59:58' AS `lctr_since`,'9999-12-31 23:59:59' AS `lctr_expire` from `reserv`.`reserv_compere`) union (select `reserv`.`reserv_lecture_staff`.`mb_id` AS `mb_id`,`reserv`.`reserv_lecture`.`lctr_id` AS `lctr_id`,`reserv`.`reserv_lecture`.`lctr_since` AS `lctr_since`,`reserv`.`reserv_lecture`.`lctr_expire` AS `lctr_expire` from (`reserv`.`reserv_lecture` join `reserv`.`reserv_lecture_staff` on((`reserv`.`reserv_lecture`.`lctr_id` = `reserv`.`reserv_lecture_staff`.`lctr_id`))) where ((`reserv`.`reserv_lecture`.`lctr_status` <> 'DELETED') and (`reserv`.`reserv_lecture`.`lctr_since` >= utc_timestamp()))) order by `mb_id`,`lctr_since`;

-- --------------------------------------------------------

--
-- Structure for view `reserv_compere_qualified`
--
DROP TABLE IF EXISTS `reserv_compere_qualified`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `reserv`.`reserv_compere_qualified` AS select `reserv`.`reserv_compere`.`mb_id` AS `mb_id` from ((`reserv`.`reserv_compere` join `reserv`.`uniwits_member_notifiable` on((`reserv`.`reserv_compere`.`mb_id` = `uniwits_member_notifiable`.`mb_id`))) join `reserv`.`uniwits_member_info` on((`reserv`.`reserv_compere`.`mb_id` = `reserv`.`uniwits_member_info`.`mb_id`))) where ((not((`reserv`.`uniwits_member_info`.`mb_name` like '%|'))) and (not((`reserv`.`uniwits_member_info`.`mb_name` like '|%'))));

-- --------------------------------------------------------

--
-- Structure for view `reserv_compere_vacancy`
--
DROP TABLE IF EXISTS `reserv_compere_vacancy`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_compere_vacancy` AS select `b`.`mb_id` AS `mb_id`,(select max(`a`.`lctr_expire`) AS `MAX(a.``lctr_expire``)` from `reserv`.`reserv_compere_occupation` `a` where ((`a`.`mb_id` = `b`.`mb_id`) and (`a`.`lctr_expire` <= `b`.`lctr_since`))) AS `cpv_since`,`b`.`lctr_since` AS `cpv_expire` from `reserv`.`reserv_compere_occupation` `b` where (`b`.`lctr_since` > utc_timestamp()) group by `b`.`mb_id`,`b`.`lctr_since` order by `b`.`mb_id`,(select max(`a`.`lctr_expire`) AS `MAX(a.``lctr_expire``)` from `reserv`.`reserv_compere_occupation` `a` where ((`a`.`mb_id` = `b`.`mb_id`) and (`a`.`lctr_expire` <= `b`.`lctr_since`)));

-- --------------------------------------------------------

--
-- Structure for view `reserv_course_last_reservation`
--
DROP TABLE IF EXISTS `reserv_course_last_reservation`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_course_last_reservation` AS select `reserv`.`reserv_lecture`.`cs_id` AS `cs_id`,`reserv`.`reserv_reservation`.`rsv_id` AS `rsv_id_last`,max(`reserv`.`reserv_reservation`.`rsv_timestamp_provisional`) AS `rsv_timestamp_last` from (`reserv`.`reserv_lecture` join `reserv`.`reserv_reservation` on((`reserv`.`reserv_lecture`.`lctr_id` = `reserv`.`reserv_reservation`.`lctr_id`))) group by `reserv`.`reserv_lecture`.`cs_id`;

-- --------------------------------------------------------

--
-- Structure for view `reserv_course_reservation_count`
--
DROP TABLE IF EXISTS `reserv_course_reservation_count`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_course_reservation_count` AS select `reserv`.`reserv_course`.`cs_id` AS `cs_id`,count(0) AS `crc_count` from (`reserv`.`reserv_course` left join `reserv`.`reserv_seat_reservation` on((`reserv`.`reserv_course`.`cs_id` = `reserv_seat_reservation`.`cs_id`))) where (`reserv_seat_reservation`.`lctr_status` <> 'DELETED') group by `reserv`.`reserv_course`.`cs_id`;

-- --------------------------------------------------------

--
-- Structure for view `reserv_course_reservation_count_thisyear`
--
DROP TABLE IF EXISTS `reserv_course_reservation_count_thisyear`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_course_reservation_count_thisyear` AS select `reserv`.`reserv_course`.`cs_id` AS `cs_id`,count(0) AS `crct_count` from (`reserv`.`reserv_course` left join `reserv`.`reserv_seat_reservation` on((`reserv`.`reserv_course`.`cs_id` = `reserv_seat_reservation`.`cs_id`))) where (convert(`reserv_seat_reservation`.`lctr_since` using utf8) >= convert((utc_timestamp() - interval 1 year) using utf8)) group by `reserv`.`reserv_course`.`cs_id`;

-- --------------------------------------------------------

--
-- Structure for view `reserv_course_weekly_minutes_avail`
--
DROP TABLE IF EXISTS `reserv_course_weekly_minutes_avail`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_course_weekly_minutes_avail` AS select `reserv`.`reserv_course`.`cs_id` AS `cs_id`,`reserv_CGV_weekly_minutes_avail`.`cgv_yearweek0` AS `cgv_yearweek0`,`reserv_CGV_weekly_minutes_avail`.`cgv_yearweek1` AS `cgv_yearweek1`,min(`reserv_CGV_weekly_minutes_avail`.`cgv_minutes_avail`) AS `cgv_minutes_avail` from ((`reserv`.`reserv_course` left join `reserv`.`reserv_course_group_course` on((`reserv`.`reserv_course`.`cs_id` = `reserv`.`reserv_course_group_course`.`cs_id`))) left join `reserv`.`reserv_CGV_weekly_minutes_avail` on((`reserv`.`reserv_course_group_course`.`cg_id` = `reserv_CGV_weekly_minutes_avail`.`cg_id`))) group by `reserv`.`reserv_course`.`cs_id`,`reserv_CGV_weekly_minutes_avail`.`cgv_yearweek0`,`reserv_CGV_weekly_minutes_avail`.`cgv_yearweek1`;

-- --------------------------------------------------------

--
-- Structure for view `reserv_course_weekly_minutes_max`
--
DROP TABLE IF EXISTS `reserv_course_weekly_minutes_max`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_course_weekly_minutes_max` AS select `cgc`.`cs_id` AS `cs_id`,min(`cgl`.`cgl_limit`) AS `cgl_limit_max` from (`reserv`.`reserv_course_group_limit` `cgl` join `reserv`.`reserv_course_group_course` `cgc` on((`cgl`.`cg_id` = `cgc`.`cg_id`))) where ((`cgl`.`cgl_status` = _utf8'VALID') and (`cgl`.`cgl_view` in (_utf8'weekly0_minutes',_utf8'weekly1_minutes'))) group by `cgc`.`cs_id`;

-- --------------------------------------------------------

--
-- Structure for view `reserv_lecture_addable`
--
DROP TABLE IF EXISTS `reserv_lecture_addable`;

CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW `reserv`.`reserv_lecture_addable` AS select `cs`.`cs_id` AS `cs_id`,`cs`.`mb_id` AS `mb_id`,greatest(`cv`.`cpv_since`,`clv`.`clsrv_since`,`t`.`ta_begin`) AS `la_since`,least(`cv`.`cpv_expire`,`clv`.`clsrv_expire`,`t`.`ta_end`) AS `la_expire` from (((((((`reserv`.`reserv_course_staff` `cs` join `reserv`.`reserv_course` `c` on((`cs`.`cs_id` = `c`.`cs_id`))) join `reserv`.`reserv_course_classroom` `cc` on((`cs`.`cs_id` = `cc`.`cs_id`))) join `reserv`.`reserv_classroom` `clsr` on((`cc`.`clsr_id` = `clsr`.`clsr_id`))) join `reserv`.`reserv_compere_vacancy` `cv` on((`cs`.`mb_id` = `cv`.`mb_id`))) join `reserv`.`reserv_classroom_vacancy` `clv` on((`cc`.`clsr_id` = `clv`.`clsr_id`))) join `reserv`.`reserv_course_timeavail` `ct` on((`cs`.`cs_id` = `ct`.`cs_id`))) join `reserv`.`reserv_timeavail` `t` on(((`cs`.`mb_id` = `t`.`mb_id`) and (`ct`.`ta_id` = `t`.`ta_id`)))) where ((`c`.`cs_status` = 'VALID') and (`clsr`.`clsr_status` = 'VALID') and (`t`.`ta_status` = 'VALID') and (`cv`.`cpv_since` < `cv`.`cpv_expire`) and (`cv`.`cpv_since` < `clv`.`clsrv_expire`) and (`cv`.`cpv_since` < `t`.`ta_end`) and (`clv`.`clsrv_since` < `cv`.`cpv_expire`) and (`clv`.`clsrv_since` < `clv`.`clsrv_expire`) and (`clv`.`clsrv_since` < `t`.`ta_end`) and (`t`.`ta_begin` < `cv`.`cpv_expire`) and (`t`.`ta_begin` < `clv`.`clsrv_expire`) and (`t`.`ta_begin` < `t`.`ta_end`) and ((greatest(`cv`.`cpv_since`,`clv`.`clsrv_since`,`t`.`ta_begin`) + interval `c`.`cs_minutes_min` minute) <= least(`cv`.`cpv_expire`,`clv`.`clsrv_expire`,`t`.`ta_end`))) order by `cs`.`cs_id`,greatest(`cv`.`cpv_since`,`clv`.`clsrv_since`,`t`.`ta_begin`),`cs`.`mb_id`,least(`cv`.`cpv_expire`,`clv`.`clsrv_expire`,`t`.`ta_end`);

-- --------------------------------------------------------

--
-- Structure for view `reserv_lecture_addable_with_deadline`
--
DROP TABLE IF EXISTS `reserv_lecture_addable_with_deadline`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_lecture_addable_with_deadline` AS select `la`.`cs_id` AS `cs_id`,`la`.`mb_id` AS `mb_id`,`la`.`la_since` AS `la_since`,`la`.`la_expire` AS `la_expire`,NULL AS `lctr_id`,`reserv_compute_reserve_deadline`(`reserv`.`reserv_compere`.`cp_timezone`,(`la`.`la_expire` - interval `cs`.`cs_minutes_min` minute),`reserv`.`reserv_course_reserve_deadline`.`crd_days_ago`,`reserv`.`reserv_course_reserve_deadline`.`crd_hours_ago`,`reserv`.`reserv_course_reserve_deadline`.`crd_minutes_ago`,`reserv`.`reserv_course_reserve_deadline`.`crd_weekday`,`reserv`.`reserv_course_reserve_deadline`.`crd_hour`,`reserv`.`reserv_course_reserve_deadline`.`crd_minute`,0) AS `la_deadline`,`cs`.`cs_minutes_min` AS `cs_minutes_min` from (((`reserv`.`reserv_lecture_addable` `la` join `reserv`.`reserv_course` `cs` on((`la`.`cs_id` = `cs`.`cs_id`))) join `reserv`.`reserv_compere` on((`la`.`mb_id` = `reserv`.`reserv_compere`.`mb_id`))) left join `reserv`.`reserv_course_reserve_deadline` on((`la`.`cs_id` = `reserv`.`reserv_course_reserve_deadline`.`cs_id`))) where ((`cs`.`cs_status` = 'VALID') and (`reserv`.`reserv_compere`.`cp_status` = 'VALID')) having ((`la_deadline` + interval `cs`.`cs_minutes_min` minute) <= `la`.`la_expire`);

-- --------------------------------------------------------

--
-- Structure for view `reserv_lecture_income`
--
DROP TABLE IF EXISTS `reserv_lecture_income`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_lecture_income` AS select `reserv`.`reserv_lecture_unit_income`.`lctr_id` AS `lctr_id`,((`reserv`.`reserv_lecture_unit_income`.`lui_revenue` * count(`reserv`.`reserv_reservation`.`mb_id`)) + coalesce(sum((`reserv`.`reserv_reservation_option`.`ro_count` * `reserv`.`reserv_reservation_option`.`ro_unit_price`)),0)) AS `li_revenue`,((`reserv`.`reserv_lecture_unit_income`.`lui_tax` * count(`reserv`.`reserv_reservation`.`mb_id`)) + coalesce(sum((`reserv`.`reserv_reservation_option`.`ro_count` * `reserv`.`reserv_reservation_option`.`ro_unit_tax`)),0)) AS `li_tax`,`reserv`.`reserv_lecture_unit_income`.`lui_expense` AS `li_expense`,count(0) AS `li_count_participant`,count((`reserv`.`reserv_lecture_unit_income`.`lui_revenue` or `reserv`.`reserv_reservation_option`.`ro_unit_price`)) AS `li_count_participant_charged` from ((`reserv`.`reserv_lecture_unit_income` join `reserv`.`reserv_reservation` on((`reserv`.`reserv_lecture_unit_income`.`lctr_id` = `reserv`.`reserv_reservation`.`lctr_id`))) left join `reserv`.`reserv_reservation_option` on((`reserv`.`reserv_reservation`.`rsv_id` = `reserv`.`reserv_reservation_option`.`rsv_id`))) group by `reserv`.`reserv_lecture_unit_income`.`lctr_id`;

-- --------------------------------------------------------

--
-- Structure for view `reserv_lecture_size`
--
DROP TABLE IF EXISTS `reserv_lecture_size`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_lecture_size` AS select `reserv`.`reserv_lecture`.`lctr_id` AS `lctr_id`,count(`reserv`.`reserv_reservation`.`st_id`) AS `lctr_size` from (`reserv`.`reserv_lecture` left join `reserv`.`reserv_reservation` on((`reserv`.`reserv_lecture`.`lctr_id` = `reserv`.`reserv_reservation`.`lctr_id`))) group by `reserv`.`reserv_lecture`.`lctr_id`;

-- --------------------------------------------------------

--
-- Structure for view `reserv_lecture_size_by_place_classroom`
--
DROP TABLE IF EXISTS `reserv_lecture_size_by_place_classroom`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_lecture_size_by_place_classroom` AS select `l`.`lctr_id` AS `lctr_id`,`s`.`plc_id` AS `plc_id`,`l`.`clsr_id` AS `clsr_id`,count(`sr`.`st_id`) AS `lctr_size`,count(`s`.`st_id`) AS `clsr_size` from ((((`reserv`.`reserv_lecture` `l` join `reserv`.`reserv_classroom_seat` on((`l`.`clsr_id` = `reserv`.`reserv_classroom_seat`.`clsr_id`))) join `reserv`.`reserv_seat` `s` on((`reserv`.`reserv_classroom_seat`.`st_id` = `s`.`st_id`))) left join `reserv`.`reserv_reservation` `sr` on(((`reserv`.`reserv_classroom_seat`.`st_id` = `sr`.`st_id`) and (`l`.`lctr_id` = `sr`.`lctr_id`)))) join `reserv`.`reserv_place` on((`s`.`plc_id` = `reserv`.`reserv_place`.`plc_id`))) where ((`s`.`st_status` = 'VALID') and (`reserv`.`reserv_place`.`plc_status` = 'VALID')) group by `l`.`lctr_id`,`s`.`plc_id`,`l`.`clsr_id`;

-- --------------------------------------------------------

--
-- Structure for view `reserv_seat_occupation`
--
DROP TABLE IF EXISTS `reserv_seat_occupation`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_seat_occupation` AS (select `reserv`.`reserv_seat`.`st_id` AS `st_id`,0 AS `lctr_id`,_utf8'1000-01-01 00:00:00' AS `lctr_since`,_utf8'1000-01-01 00:00:01' AS `lctr_expire` from `reserv`.`reserv_seat` group by `reserv`.`reserv_seat`.`st_id`) union (select `reserv`.`reserv_seat`.`st_id` AS `st_id`,0 AS `lctr_id`,_utf8'9999-12-31 23:59:58' AS `lctr_since`,_utf8'9999-12-31 23:59:59' AS `lctr_expire` from `reserv`.`reserv_seat` group by `reserv`.`reserv_seat`.`st_id`) union (select `reserv_seat_reservation`.`st_id` AS `st_id`,`reserv_seat_reservation`.`lctr_id` AS `lctr_id`,`reserv_seat_reservation`.`lctr_since` AS `lctr_since`,`reserv_seat_reservation`.`lctr_expire` AS `lctr_expire` from `reserv`.`reserv_seat_reservation`) order by `st_id`,`lctr_since`;

-- --------------------------------------------------------

--
-- Structure for view `reserv_seat_reservation`
--
DROP TABLE IF EXISTS `reserv_seat_reservation`;

CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW `reserv`.`reserv_seat_reservation` AS select `reserv`.`reserv_lecture`.`lctr_id` AS `lctr_id`,`reserv`.`reserv_lecture`.`cs_id` AS `cs_id`,`reserv`.`reserv_lecture`.`clsr_id` AS `clsr_id`,`reserv`.`reserv_lecture`.`lctr_since` AS `lctr_since`,`reserv`.`reserv_lecture`.`lctr_expire` AS `lctr_expire`,`reserv`.`reserv_lecture`.`lctr_deadline` AS `lctr_deadline`,`reserv`.`reserv_lecture`.`lctr_status` AS `lctr_status`,`reserv`.`reserv_reservation`.`rsv_id` AS `rsv_id`,`reserv`.`reserv_reservation`.`st_id` AS `st_id`,`reserv`.`reserv_reservation`.`mb_id` AS `mb_id`,`reserv`.`reserv_reservation`.`rsv_timestamp_provisional` AS `rsv_timestamp_provisional`,`reserv`.`reserv_reservation`.`rsv_timestamp_confirmed` AS `rsv_timestamp_confirmed`,`reserv`.`reserv_reservation`.`mb_id_reserver` AS `mb_id_reserver`,`reserv`.`reserv_reservation`.`rsv_absent` AS `rsv_absent` from (`reserv`.`reserv_lecture` join `reserv`.`reserv_reservation` on((`reserv`.`reserv_lecture`.`lctr_id` = `reserv`.`reserv_reservation`.`lctr_id`))) order by `reserv`.`reserv_reservation`.`st_id`,`reserv`.`reserv_lecture`.`lctr_since`,`reserv`.`reserv_lecture`.`lctr_expire`;

-- --------------------------------------------------------

--
-- Structure for view `reserv_seat_vacancy`
--
DROP TABLE IF EXISTS `reserv_seat_vacancy`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `reserv`.`reserv_seat_vacancy` AS select `b`.`st_id` AS `st_id`,(select max(`a`.`lctr_expire`) AS `MAX(a.``lctr_expire``)` from `reserv`.`reserv_seat_occupation` `a` where ((`a`.`st_id` = `b`.`st_id`) and (`a`.`lctr_expire` <= `b`.`lctr_since`))) AS `stv_since`,`b`.`lctr_since` AS `stv_expire` from `reserv`.`reserv_seat_occupation` `b` where (`b`.`lctr_since` > utc_timestamp()) group by `b`.`st_id`,`b`.`lctr_since` order by `b`.`st_id`,(select max(`a`.`lctr_expire`) AS `MAX(a.``lctr_expire``)` from `reserv`.`reserv_seat_occupation` `a` where ((`a`.`st_id` = `b`.`st_id`) and (`a`.`lctr_expire` <= `b`.`lctr_since`)));

-- --------------------------------------------------------

--
-- Structure for view `reserv_timeavail_effective`
--
DROP TABLE IF EXISTS `reserv_timeavail_effective`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `reserv`.`reserv_timeavail_effective` AS select `reserv`.`reserv_timeavail`.`ta_id` AS `ta_id`,`reserv`.`reserv_timeavail`.`mb_id` AS `mb_id`,`reserv`.`reserv_timeavail`.`ta_begin` AS `ta_begin`,`reserv`.`reserv_timeavail`.`ta_end` AS `ta_end`,`reserv`.`reserv_timeavail`.`ta_status` AS `ta_status` from `reserv`.`reserv_timeavail` where ((`reserv`.`reserv_timeavail`.`ta_status` = 'VALID') and (convert(utc_timestamp() using utf8) <= convert(`reserv`.`reserv_timeavail`.`ta_end` using utf8)));
