-- phpMyAdmin SQL Dump
-- version 3.3.8.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 13, 2011 at 12:57 PM
-- Server version: 5.1.51
-- PHP Version: 5.3.5-pl0-gentoo

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `statutory_event`
--

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

--
-- Table structure for table `ab_bodies`
--

CREATE TABLE IF NOT EXISTS `ab_bodies` (
  `BodyCode` varchar(3) NOT NULL DEFAULT '',
  `BodyName` varchar(50) NOT NULL DEFAULT '',
  `Email` varchar(70) DEFAULT NULL,
  `BodyStatus` varchar(2) DEFAULT NULL,
  `BodyCategory` varchar(25) NOT NULL DEFAULT '',
  `BodyCategoryOrder` int(11) NOT NULL DEFAULT '0',
  `MemberNo` int(11) NOT NULL DEFAULT '0',
  `MemberFeeCurrencyId` int(11) DEFAULT NULL,
  `MemberSource` varchar(9) NOT NULL DEFAULT '',
  `MemberClaimed` int(11) NOT NULL DEFAULT '0',
  `MemberClaimedBy` varchar(64) NOT NULL DEFAULT '',
  `MemberLastUpdate` datetime DEFAULT NULL,
  `BodyNameAscii` varchar(50) DEFAULT NULL,
  `NetcomCode` char(1) DEFAULT NULL,
  `CountryCode` varchar(3) DEFAULT NULL,
  `Password` varchar(32) NOT NULL,
  `NoDelegates` int(11) NOT NULL DEFAULT '-1',
  `NoEnvoys` int(11) NOT NULL DEFAULT '-1',
  `NoVisitors` int(11) NOT NULL DEFAULT '-1',
  `NoObservers` int(11) NOT NULL DEFAULT '-1',
  PRIMARY KEY (`BodyCode`),
  KEY `BodyName` (`BodyName`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1;

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

--
-- Table structure for table `appl_extraanswers`
--

CREATE TABLE IF NOT EXISTS `appl_extraanswers` (
  `appl_id` int(11) NOT NULL,
  `question_id` int(11) NOT NULL,
  `answer` text NOT NULL,
  UNIQUE KEY `index` (`appl_id`,`question_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `appl_extraquestions`
--

CREATE TABLE IF NOT EXISTS `appl_extraquestions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `question` varchar(255) NOT NULL,
  `maxlength` int(11) NOT NULL,
  `order` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `candidatepost`
--

CREATE TABLE IF NOT EXISTS `candidatepost` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `position` varchar(48) NOT NULL,
  `places` int(11) NOT NULL,
  `deadline` enum('auto','open','closed') NOT NULL DEFAULT 'auto',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `candidates`
--

CREATE TABLE IF NOT EXISTS `candidates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL,
  `FirstName` varchar(50) NOT NULL,
  `LastName` varchar(50) NOT NULL,
  `DateBirth` date NOT NULL,
  `Sex` enum('male','female') NOT NULL DEFAULT 'male',
  `Email` varchar(70) NOT NULL,
  `Nationality` int(11) NOT NULL,
  `Photo` MEDIUMBLOB NOT NULL,
  `Street` varchar(50) NOT NULL,
  `Zip` varchar(8) NOT NULL,
  `City` varchar(50) NOT NULL,
  `CountryCode` int(11) NOT NULL,
  `Phone` varchar(50) NOT NULL,
  `Mobile` varchar(50) NOT NULL,
  `Fax` varchar(50) NOT NULL,
  `BodyCode` varchar(3) NOT NULL,
  `Studies` varchar(50) NOT NULL,
  `Languages` varchar(50) NOT NULL,
  `Position` int(11) NOT NULL,
  `MemberSince` date NOT NULL,
  `EUExperience` text NOT NULL,
  `Agoras` text NOT NULL,
  `EBMs` text NOT NULL,
  `AEGEEConferences` text NOT NULL,
  `LocalBoardPosition` varchar(50) NOT NULL,
  `LocalActivity` text NOT NULL,
  `NonAEGEE` text NOT NULL,
  `CDTasks` text NOT NULL,
  `Motivation` text NOT NULL,
  `TaskExperience` text NOT NULL,
  `Program` text NOT NULL,
  `Approved` enum('pending','yes','no') NOT NULL DEFAULT 'pending',
  `Created` datetime NOT NULL,
  `Modified` datetime NOT NULL,
  `Log` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `cand_extraanswers`
--

CREATE TABLE IF NOT EXISTS `cand_extraanswers` (
  `cand_id` int(11) NOT NULL,
  `question_id` int(11) NOT NULL,
  `answer` text NOT NULL,
  UNIQUE KEY `index` (`cand_id`,`question_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `cand_extraquestions`
--

CREATE TABLE IF NOT EXISTS `cand_extraquestions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `candidatepost_id` int(11) NOT NULL,
  `question` varchar(255) NOT NULL,
  `maxlength` int(11) NOT NULL,
  `order` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `CountryCodes`
--

CREATE TABLE IF NOT EXISTS `CountryCodes` (
  `Code` int(11) NOT NULL DEFAULT '0',
  `Name` varchar(50) NOT NULL DEFAULT '',
  `ISO_2` varchar(2) NOT NULL DEFAULT '',
  `ISO_3` varchar(3) NOT NULL DEFAULT '',
  `Postal` varchar(3) NOT NULL DEFAULT '',
  `Continent` enum('EU','AF','AS','AU','NA','SA','') NOT NULL DEFAULT '',
  `Fee` enum('low','high','unknown') NOT NULL DEFAULT 'unknown',
  PRIMARY KEY (`Code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1;

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

--
-- Table structure for table `currencies`
--

CREATE TABLE IF NOT EXISTS `currencies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ISO_2` varchar(2) NOT NULL DEFAULT '',
  `name` varchar(24) NOT NULL,
  `short` varchar(12) NOT NULL,
  `rate` decimal(12,10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `embassies`
--

CREATE TABLE IF NOT EXISTS `embassies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `countryCode` int(11) NOT NULL,
  `Name` varchar(128) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `files`
--

CREATE TABLE IF NOT EXISTS `files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `info_id` int(11) NOT NULL,
  `subject` varchar(64) NOT NULL,
  `filename` varchar(64) NOT NULL,
  `filetype` varchar(64) NOT NULL,
  `data` mediumblob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `functions`
--

CREATE TABLE IF NOT EXISTS `functions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `function_roles`
--

CREATE TABLE IF NOT EXISTS `function_roles` (
  `role_id` int(11) NOT NULL,
  `function_id` int(11) NOT NULL,
  PRIMARY KEY (`role_id`,`function_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `info`
--

CREATE TABLE IF NOT EXISTS `info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subject` varchar(128) NOT NULL,
  `message` text NOT NULL,
  `access` enum('public','aegee','draft') NOT NULL DEFAULT 'public',
  `dateadd` datetime NOT NULL,
  `datemodify` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `log`
--

CREATE TABLE IF NOT EXISTS `log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `section` varchar(16) NOT NULL DEFAULT '',
  `message` varchar(192) NOT NULL DEFAULT '',
  `type` enum('debug','info','warning','error') NOT NULL DEFAULT 'info',
  `sess_name` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `mailallhistory`
--

CREATE TABLE IF NOT EXISTS `mailallhistory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `user` varchar(50) NOT NULL,
  `fromname` varchar(64) NOT NULL,
  `fromemail` varchar(64) NOT NULL,
  `accepted` varchar(16) NOT NULL,
  `nationality` int(11) NOT NULL,
  `firstAgora` enum('yes','undefined') NOT NULL DEFAULT 'undefined',
  `subject` varchar(128) NOT NULL,
  `message` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `mailbatch`
--

CREATE TABLE IF NOT EXISTS `mailbatch` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `receipt` varchar(100) NOT NULL,
  `mail` blob NOT NULL,
  `sender_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `mailout`
--

CREATE TABLE IF NOT EXISTS `mailout` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `to` varchar(128) NOT NULL DEFAULT '',
  `content` varchar(32) NOT NULL DEFAULT '',
  `Mail` blob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `memberlist`
--

CREATE TABLE IF NOT EXISTS `memberlist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `BodyCode` varchar(3) NOT NULL,
  `GivenName` varchar(50) NOT NULL,
  `Surname` varchar(50) NOT NULL,
  `CSN` varchar(13) NOT NULL,
  `DateBirth` date NOT NULL,
  `MemberSince` date NOT NULL,
  `Fee` decimal(8,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `persons`
--

CREATE TABLE IF NOT EXISTS `persons` (
  `PersonID` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL DEFAULT '',
  `FirstName` varchar(50) NOT NULL DEFAULT '',
  `LastName` varchar(50) NOT NULL DEFAULT '',
  `BodyCode` varchar(3) NOT NULL DEFAULT '',
  `DateBirth` date NOT NULL DEFAULT '0000-00-00',
  `Sex` enum('male','female') NOT NULL DEFAULT 'male',
  `CSN` varchar(13) NOT NULL DEFAULT '',
  `Email` varchar(70) NOT NULL DEFAULT '',
  `Visa` enum('yes','no') NOT NULL DEFAULT 'no',
  `PassportNo` varchar(50) NOT NULL DEFAULT '',
  `IDType` enum('Passport','National ID Card (EU+Norway+Switzerland only)') NOT NULL,
  `PlaceOfBirth` varchar(64) NOT NULL,
  `PassportExpirationDate` date DEFAULT NULL,
  `Nationality` int(11) NOT NULL DEFAULT '0',
  `Embassy` int(11) NOT NULL,
  `JoinStatutory` enum('yes','no') NOT NULL DEFAULT 'yes',
  `JoinEventPre1` enum('yes','no') NOT NULL DEFAULT 'no',
  `JoinEventPre2` enum('yes','no') NOT NULL DEFAULT 'no',
  `JoinEventPre3` enum('yes','no') NOT NULL DEFAULT 'no',
  `JoinEventPost1` enum('yes','no') NOT NULL DEFAULT 'no',
  `JoinEventPost2` enum('yes','no') NOT NULL DEFAULT 'no',
  `JoinEventPost3` enum('yes','no') NOT NULL DEFAULT 'no',
  `WorkshopID1` int(11) NOT NULL DEFAULT '0',
  `WorkshopID2` int(11) NOT NULL DEFAULT '0',
  `WorkshopID3` int(11) NOT NULL DEFAULT '0',
  `WorkshopID4` int(11) NOT NULL DEFAULT '0',
  `chairWS1` enum('yes','no') NOT NULL DEFAULT 'no',
  `chairWS3` enum('yes','no') NOT NULL DEFAULT 'no',
  `WorkshopStrategy` int(11) NOT NULL,
  `ExtraFee` enum('yes','no') NOT NULL DEFAULT 'no',
  `DateArrival` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `DateDeparture` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `CareOf` varchar(50) NOT NULL DEFAULT '',
  `Street` varchar(50) NOT NULL DEFAULT '',
  `Zip` varchar(8) NOT NULL DEFAULT '',
  `City` varchar(50) NOT NULL DEFAULT '',
  `CountryCode` int(11) NOT NULL DEFAULT '0',
  `Phone` varchar(50) NOT NULL DEFAULT '',
  `Mobile` varchar(50) NOT NULL DEFAULT '',
  `HasMobile` enum('yes','no') NOT NULL DEFAULT 'no',
  `Fax` varchar(50) NOT NULL DEFAULT '',
  `HasLaptop` enum('yes','no') NOT NULL DEFAULT 'no',
  `MAC` varchar(17) NOT NULL DEFAULT '',
  `TShirtSize` enum('S','M','L','XL') DEFAULT NULL,
  `Photo` mediumblob NOT NULL,
  `Meals` enum('normal','vegetarian','vegan','diet') NOT NULL DEFAULT 'normal',
  `LodgingNeeded` enum('yes','no') NOT NULL DEFAULT 'yes',
  `MemberType` enum('Board member of a local','Board member of a WG','Member of a project team','Board member of a contact','None of these') NOT NULL DEFAULT 'None of these',
  `AgoraVisited` int(11) NOT NULL DEFAULT '0',
  `Motivation` text NOT NULL,
  `Remarks` text NOT NULL,
  `CommentBoard` text NOT NULL,
  `CommentNetcom` text NOT NULL,
  `Created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Modified` datetime DEFAULT NULL,
  `BoardConfirmed` enum('-','yes','no') NOT NULL DEFAULT '-',
  `MemberlistConfirmed` enum('-','yes','pending','no') NOT NULL DEFAULT '-',
  `MemberlistOnPrevious` enum('-','yes','pending','no') NOT NULL DEFAULT '-',
  `MemberlistOnCurrent` enum('-','yes','pending','no') NOT NULL DEFAULT '-',
  `MemberlistIsBoard` enum('-','yes','pending','no') NOT NULL DEFAULT '-',
  `Accepted` enum('yes','pending','confirmed','-','no','cancel','arrived') NOT NULL DEFAULT '-',
  `WorkshopIDSlot1` int(11) NOT NULL DEFAULT '0',
  `WorkshopIDSlot2` int(11) NOT NULL DEFAULT '0',
  `ParticipantType` enum('-','delegate','envoy','observer','visitor') NOT NULL DEFAULT '-',
  `ParticipantOrder` int(11) NOT NULL DEFAULT '0',
  `Fee` int(11) NOT NULL DEFAULT '-1',
  `FeePaid` decimal(4,2) NOT NULL DEFAULT '-1.00',
  `Gym` int(11) NOT NULL DEFAULT '0',
  `GymPreference` varchar(512) NOT NULL,
  `ConfirmationNumber` int(11) NOT NULL,
  `RegistrationDesk` int(11) NOT NULL DEFAULT '0',
  `RegistrationCustField1` varchar(200) NOT NULL DEFAULT '',
  `RegistrationCustField2` varchar(200) NOT NULL DEFAULT '',
  `RegistrationCustField3` varchar(200) NOT NULL DEFAULT '',
  `Log` text NOT NULL,
  `PassportIssuingAuthority` varchar(64) NOT NULL,
  `PassportIssuingDate` date DEFAULT NULL,
  PRIMARY KEY (`PersonID`),
  KEY `workshopID1` (`WorkshopID1`),
  KEY `workshopID2` (`WorkshopID2`),
  KEY `workshopID3` (`WorkshopID3`,`WorkshopID4`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 PACK_KEYS=0 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `querylog`
--

CREATE TABLE IF NOT EXISTS `querylog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `sid` int(11) NOT NULL,
  `user` varchar(32) NOT NULL,
  `query` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `roles`
--

CREATE TABLE IF NOT EXISTS `roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `setup`
--

CREATE TABLE IF NOT EXISTS `setup` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category` varchar(16) NOT NULL DEFAULT '',
  `Name` varchar(30) NOT NULL DEFAULT '',
  `Value` text NOT NULL,
  `RegEx` varchar(100) NOT NULL DEFAULT '',
  `Explanation` text NOT NULL,
  `ac_view` enum('no','view','edit') NOT NULL DEFAULT 'no',
  `ac_edit` enum('no','view','edit') NOT NULL DEFAULT 'no',
  `ac_audit` enum('no','view','edit') NOT NULL DEFAULT 'no',
  `ac_jc` enum('no','view','edit') NOT NULL DEFAULT 'no',
  `ac_registration` enum('no','view','edit') NOT NULL DEFAULT 'no',
  `ac_localorg` enum('no','view','edit') NOT NULL DEFAULT 'no',
  `ac_chair` enum('no','view','edit') NOT NULL DEFAULT 'no',
  `default` text NOT NULL,
  `empty` enum('yes','no') NOT NULL DEFAULT 'no',
  PRIMARY KEY (`id`),
  UNIQUE KEY `Name` (`Name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `statistics`
--

CREATE TABLE IF NOT EXISTS `statistics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `selection` enum('all','accepted','arrived') NOT NULL DEFAULT 'all',
  `title` varchar(32) NOT NULL,
  `date` datetime NOT NULL,
  `html` text NOT NULL,
  `image` mediumblob NOT NULL,
  `image_height` int(11) NOT NULL,
  `image_width` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `user` varchar(50) NOT NULL DEFAULT '',
  `password` varchar(40) NOT NULL DEFAULT '',
  `email` varchar(75) NOT NULL DEFAULT '',
  `role_id` int(11) NOT NULL,
  `privstatementagree` enum('yes','no') NOT NULL DEFAULT 'no',
  PRIMARY KEY (`uid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 PACK_KEYS=0 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `workshops`
--

CREATE TABLE IF NOT EXISTS `workshops` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `description` text NOT NULL,
  `leader` varchar(150) NOT NULL,
  `slot` enum('first','second','both','strategy') NOT NULL DEFAULT 'both',
  `url` varchar(120) NOT NULL,
  `places` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
