


CREATE TABLE IF NOT EXISTS userPrivacy(
  privacyId int not null auto_increment,
  basic enum ('meonly','friends','all'),
  picture enum ('meonly','friends','all'),
  location enum ('meonly','friends','all'),
  phoneNumber enum ('meonly','friends','all'),
  email enum ('meonly','friends','all'),
  primary key (privacyId),
  index (basic,picture,location,phoneNumber,email)
);


CREATE TABLE IF NOT EXISTS userSettings(
  settingsId int not null auto_increment,
  planNotif enum ('yes','no'),
  friendsRequestNotif enum ('yes','no'),
  sendCurrentPosition enum ('yes','no'),
  postToFacebook enum ('yes','no'),
  postToTwitter enum ('yes','no'),
  postToLinkedIn enum ('yes','no'),
  profileForBusinesses enum ('yes','no'),
  primary key (settingsId),
  index (planNotif,friendsRequestNotif,sendCurrentPosition,postToFacebook,postToTwitter,postToLinkedIn,profileForBusinesses)
);


CREATE TABLE IF NOT EXISTS link(
  linkId int not null auto_increment,
  userId int not null,
  contactId int not null,
  since date null,
  primary key (linkId),
  index (userId),
  index (contactId),
  index (since),
  unique (userId,contactId)
);


CREATE TABLE IF NOT EXISTS list(
  listId int not null auto_increment,
  userId int not null,
  listName varchar(32) not null,
  primary key (listId),
  index (userId),
  unique (userId,listName)
);


CREATE TABLE IF NOT EXISTS member(
  memberId int not null auto_increment,
  listId int not null,
  linkId int not null,
  primary key (memberId),
  index (listId)
);

CREATE TABLE IF NOT EXISTS usersGroup(
  usersGroupId int not null auto_increment,
  name varchar(32),
  description varchar(128),
  picture varchar(32),
  status enum ('open', 'closed') default 'closed',
  userReferenceId int,
  primary key (usersGroupId),
  unique (userReferenceId)
);


CREATE TABLE IF NOT EXISTS usersGroupMember(
  usersGroupMemberId int not null auto_increment,
  groupId int,
  userId int,
  isAdmin boolean default 0,
  primary key (usersGroupMemberId),
  index (groupId),
  index (userId),
  unique (groupId, userId)
);


CREATE TABLE IF NOT EXISTS venue(
  venueId int not null auto_increment,
  ownerId int,
  name varchar(50),
  internalName varchar(32),
  email varchar(64),
  phoneNumber char(16),
  country varchar(35) not null,
  postalCode char (8),
  region varchar(32),
  address varchar(128),
  longitude float ,
  latitude float ,
  extOrigin enum('Foursquare','GooglePlaces'),
  extId int,
  description text,
  picture varchar(64),
  capacity int,
  status enum('available', 'unavailable'),
  private TINYINT NOT NULL DEFAULT  '0',
  primary key (venueId),
  index (private)
);


CREATE TABLE IF NOT EXISTS venueFollower(
  venueFollowerId int not null auto_increment,
  venueId int,
  userId int,
  primary key (venueFollowerId),
  index (venueId),
  index (userId),
  unique (venueId, userId)
);


CREATE TABLE IF NOT EXISTS plan(
  planId int not null auto_increment,
  name varchar(50) default null,
  organizerId int not null,
  venueId int not null,
  venueLabel varchar(100) default null,
  startTime timestamp not NULL default '0000-00-00 00:00:00',
  description text,
  status enum('future', 'ongoing', 'past', 'cancelled') not null,
  scope enum('normal', 'openToFriends', 'openToAll', 'group') not null,
  /*secretKey char(32),*/
  updateTime timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  lastCommentId INT NOT NULL DEFAULT '0',
  primary key (planId),
  index (organizerId),
  index (venueId)
);



CREATE TABLE IF NOT EXISTS planHistory (
  planHistoryId int(11) NOT NULL auto_increment,
  planId int(11) NOT NULL,
  updateTime timestamp NULL default CURRENT_TIMESTAMP,
  name varchar(50) default NULL,
  organizerId int(11) default NULL,
  venueId int(11) default NULL,
  venueLabel varchar(100) default NULL,
  startTime timestamp NULL default '0000-00-00 00:00:00',
  description text,
  lastCommentId INT NOT NULL DEFAULT '0',
  scope enum('normal', 'openToAll', 'group') not null,
  PRIMARY KEY  (planHistoryId),
  KEY planId (planId)
);

CREATE TABLE IF NOT EXISTS planConsultation (
planConsultationId INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
planId INT NOT NULL ,
userId INT NOT NULL ,
lastConsultation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX (  planId ,  userId )
) ;

CREATE TABLE IF NOT EXISTS invitation(
  invitationId int not null auto_increment,
  planId int not null,
  userId int not null,
  status enum('pending','accepted','rejected','tentative','cancelled') not null,
  primary key (invitationId),
  index (planId),
  index (userId),
  unique (planId,userId)
);


CREATE TABLE IF NOT EXISTS comment(
  commentId int not null auto_increment,
  planId int not null,
  userId int not null,
  time timestamp not null,
  comment text,
  `type` enum('text','html') NOT NULL default 'text',
  `priority` tinyint(4) NOT NULL default '9',
  primary key (commentId),
  index (planId)
);


CREATE TABLE IF NOT EXISTS feedback(
  feedbackId int not null auto_increment,
  planId int not null,
  userId int not null,
  time timestamp not null,
  picture varchar(32),
  comment text,
  primary key (feedbackId),
  key (planId)
);


CREATE TABLE IF NOT EXISTS connection(
	connectionId int not null auto_increment,
	connectionLabel varchar(32)not null,
	primary key (connectionId),
	unique(connectionLabel) 
);


CREATE TABLE IF NOT EXISTS businessOwnerShip(
    businessOwnerShipId int not null auto_increment,
    businessOwnerId int,
    venueId int,
    rights enum ('basic', 'premium') default 'basic',
    primary key (businessOwnerShipId),
    unique (businessOwnerId, venueId)
);


CREATE TABLE IF NOT EXISTS venueEvent(
    venueEventId int not null auto_increment,
    issuerId int,
    name varchar(32),
    description varchar(128),
    startTime timestamp,
    endTime timestamp,
    primary key (venueEventId)
);


CREATE TABLE IF NOT EXISTS badge (
    badgeId int not null auto_increment,
    name varchar(32) not null,
    period int,
    refCount int,
    refPlansCount int,
    primary key (badgeId),
    unique (name)
);

CREATE TABLE IF NOT EXISTS userBadge (
    userBadgeId int not null auto_increment,
    userId int not null,
    badgeId int not null,
    primary key (userBadgeId),
    unique (userId, badgeId),
    index (userId)
);

INSERT into connection (connectionLabel) VALUE ("Facebook");
INSERT into connection (connectionLabel) VALUE ("Gmail");
INSERT into connection (connectionLabel) VALUE ("Twitter");
INSERT into connection (connectionLabel) VALUE ("Foursquare");

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

--
-- Structure de la table countries
--

CREATE TABLE IF NOT EXISTS countries (
  countryId char(3) NOT NULL,
  country varchar(35) NOT NULL,
  PRIMARY KEY  (countryId),
  UNIQUE KEY country (country)
);

--
-- Contenu de la table countries
--

INSERT INTO countries (countryId, country) VALUES
('AF', 'Afghanistan'),
('AL', 'Albania'),
('DZ', 'Algeria'),
('AS', 'American Samoa'),
('AD', 'Andorra'),
('AO', 'Angola'),
('AI', 'Anguilla'),
('AG', 'Antigua and Barbuda'),
('AR', 'Argentina'),
('AW', 'Aruba'),
('AC', 'Ascension Islands'),
('AU', 'Australia'),
('AT', 'Austria'),
('AZ', 'Azores'),
('BS', 'Bahamas'),
('BH', 'Bahrain'),
('BD', 'Bangladesh'),
('BB', 'Barbados'),
('BE', 'Belgium'),
('BZ', 'Belize'),
('BEL', 'Belorussia'),
('BJ', 'Benin'),
('BM', 'Bermuda'),
('BT', 'Bhutan'),
('BO', 'Bolivia'),
('BW', 'Botswana'),
('BV', 'Bouvet Island'),
('BR', 'Brazil'),
('IO', 'British Indian Ocean Territory'),
('VG', 'British Virgin Islands'),
('BN', 'Brunei Darussalam'),
('BG', 'Bulgaria'),
('BF', 'Burkina Faso'),
('BI', 'Burundi'),
('KH', 'Cambodia'),
('CA', 'Canada'),
('CV', 'Cape Verde'),
('KY', 'Cayman Islands'),
('CF', 'Central African Republic'),
('TD', 'Chad'),
('CL', 'Chile'),
('CN', 'China'),
('CX', 'Christmas Island'),
('CC', 'Cocos (Keeling) Islands'),
('CO', 'Colombia'),
('KM', 'Comoro Island'),
('CG', 'Congo'),
('CK', 'Cook Islands');

INSERT INTO countries (countryId, country) VALUES 
('CZ', 'Corsica'),
('CR', 'Costa Rica'),
('CU', 'Cuba'),
('CUR', 'Curacao'),
('CY', 'Cyprus'),
('CZE', 'Czech Republic'),
('DK', 'Denmark'),
('DJ', 'Djibouti'),
('DM', 'Dominica'),
('DO', 'Dominican Republic'),
('EC', 'Ecuador'),
('EG', 'Egypt'),
('SV', 'El Salvador'),
('GQ', 'Equatorial Guinea'),
('ET', 'Ethiopia'),
('FK', 'Falkland Islands'),
('FO', 'Faroe Islands'),
('YU', 'Federal Republic of Yugoslavia'),
('FJ', 'Fiji'),
('FI', 'Finland'),
('CS', 'Former Czechoslovakia'),
('SU', 'Former Soviet Union'),
('FR', 'France'),
('GF', 'French Guiana'),
('PF', 'French Polynesia'),
('TF', 'French Southern Territories'),
('GA', 'Gabon'),
('GM', 'Gambia'),
('GK', 'Gaza and Khan Yunis'),
('DE', 'Germany'),
('GH', 'Ghana'),
('GI', 'Gibraltar'),
('GR', 'Greece'),
('GL', 'Greenland'),
('GD', 'Grenada'),
('GP', 'Guadeloupe'),
('GU', 'Guam'),
('GT', 'Guatemala'),
('GN', 'Guinea'),
('GW', 'Guinea-Bissau'),
('GY', 'Guyana'),
('HT', 'Haiti'),
('HM', 'Heard & McDonald Islands'),
('HN', 'Honduras'),
('HK', 'Hong Kong'),
('HU', 'Hungary'),
('IS', 'Iceland'),
('IN', 'India');

INSERT INTO countries (countryId, country) VALUES 
('ID', 'Indonesia'),
('IR', 'Iran'),
('IQ', 'Iraq'),
('IE', 'Ireland'),
('IL', 'Israel'),
('IT', 'Italy'),
('CI', 'Ivory Coast'),
('JM', 'Jamaica'),
('JP', 'Japan'),
('JO', 'Jordan'),
('KE', 'Kenya'),
('KI', 'Kirghizia'),
('KII', 'Kiribati'),
('KW', 'Kuwait'),
('LA', 'Laos'),
('LB', 'Lebanon'),
('LS', 'Lesotho'),
('LR', 'Liberia'),
('LY', 'Libyan Arab Jamahiriya'),
('LI', 'Liechtenstein'),
('LU', 'Luxembourg'),
('MO', 'Macau'),
('MAC', 'Macedonia'),
('MG', 'Madagascar'),
('MAD', 'Madeira'),
('MW', 'Malawi'),
('MY', 'Malaysia'),
('MV', 'Maldives'),
('ML', 'Mali'),
('MT', 'Malta'),
('MP', 'Mariana Island'),
('MH', 'Marshall Island'),
('MQ', 'Martinique'),
('MR', 'Mauritania'),
('MU', 'Mauritius'),
('MX', 'Mexico'),
('FM', 'Micronesia'),
('MC', 'Monaco'),
('MN', 'Mongolia'),
('MON', 'Montenegro'),
('MS', 'Montserrat'),
('MA', 'Morocco'),
('MZ', 'Mozambique'),
('NA', 'Namibia'),
('NR', 'Nauru'),
('NP', 'Nepal'),
('AN', 'Netherland Antillies'),
('NL', 'Netherlands'),
('NC', 'New Caledonia'),
('NZ', 'New Zealand'),
('NI', 'Nicaragua'),
('NE', 'Niger'),
('NG', 'Nigeria'),
('NU', 'Niue'),
('NF', 'Norfolk Island'),
('KP', 'North Korea'),
('NO', 'Norway'),
('OM', 'Oman'),
('PK', 'Pakistan'),
('PW', 'Palau'),
('PA', 'Panama'),
('PG', 'Papua New Guinea'),
('PY', 'Paraguay'),
('PE', 'Peru'),
('PH', 'Philippines'),
('PN', 'Pitcairn'),
('PL', 'Poland'),
('PT', 'Portugal'),
('PR', 'Puerto Rico'),
('QA', 'Qata'),
('ARM', 'Republic of Armenia'),
('AZE', 'Republic of Azerbaijan'),
('BL', 'Republic of Belarus'),
('BOS', 'Republic of Bosnia Hercegovina'),
('CM', 'Republic of Cameroon'),
('CRO', 'Republic of Croatia'),
('EST', 'Republic of Estonia'),
('GEO', 'Republic of Georgia'),
('KAZ', 'Republic of Kazakhstan'),
('KG', 'Republic of Kyrgyzstan'),
('LAT', 'Republic of Latvia'),
('LIT', 'Republic of Lithuania'),
('MOL', 'Republic of Moldovia'),
('SLO', 'Republic of Slovenia'),
('TAD', 'Republic of Tajikistan'),
('UZB', 'Republic of Uzbekistan'),
('RE', 'Reunion'),
('RO', 'Romania'),
('RUS', 'Russian Federation'),
('RW', 'Rwanda'),
('SM', 'San Marino'),
('ST', 'Sao Tome and Principe'),
('SI', 'Sardinia'),
('SA', 'Saudi Arabia'),
('SN', 'Senegal'),
('SER', 'Serbia'),
('SC', 'Seychelles'),
('SL', 'Sierra Leone');

INSERT INTO countries (countryId, country) VALUES 
('SG', 'Singapore'),
('SLV', 'Slovak Republic'),
('SB', 'Solomon Islands'),
('SO', 'Somalia'),
('ZA', 'South Africa'),
('KR', 'South Korea'),
('ES', 'Spain'),
('SP', 'Spanish Territories of N Africa'),
('SJ', 'Spitzbergen (Svalbard & Jan Mayen)'),
('LK', 'Sri Lanka>'),
('LC', 'St Lucia'),
('KN', 'St. Christopher (St. Kitts) - Nevis'),
('SH', 'St. Helena'),
('PM', 'St. Pierre and Miquelon'),
('VC', 'St. Vincent and The Grenadines'),
('SD', 'Sudan'),
('SR', 'Surinam'),
('SZ', 'Swaziland'),
('SE', 'Sweden'),
('CH', 'Switzerland'),
('SY', 'Syrian Arab Republic'),
('TW', 'Taiwan'),
('TZ', 'Tanzania'),
('TH', 'Thailand'),
('TG', 'Togo'),
('TK', 'Tokelau'),
('TO', 'Tonga Islands'),
('TT', 'Trinidad & Tobago'),
('TDC', 'Tristan da Cunha'),
('TN', 'Tunisia'),
('TR', 'Turkey'),
('TUR', 'Turkmenistan'),
('TC', 'Turks and Caicos Islands'),
('TV', 'Tuvalu'),
('UG', 'Uganda'),
('UKR', 'Ukraine'),
('MM', 'Union of Myanmar'),
('AE', 'United Arab Emirates'),
('GB', 'United Kingdom'),
('UM', 'United States Oceania'),
('US', 'United States of America'),
('UY', 'Uruguay'),
('VU', 'Vanuatu'),
('VA', 'Vatican City State'),
('VE', 'Venezuela'),
('VN', 'Vietnam'),
('VI', 'Virgin Islands (American)'),
('WF', 'Wallis and Futuna Islands'),
('WS', 'Western Samoa'),
('YE', 'Yemen'),
('ZR', 'Zaire'),
('ZM', 'Zambia'),
('ZW', 'Zimbabwe');
