# OpenSocial MySQL database entity creation script
# Chris Schalk - Google


DROP TABLE IF EXISTS people;
DROP TABLE IF EXISTS person_friends;
DROP TABLE IF EXISTS person_appdata;
DROP TABLE IF EXISTS activity_streams, activities, activity_media_items; 

DROP TABLE IF EXISTS addresses;
DROP TABLE IF EXISTS organizations;
DROP TABLE IF EXISTS movies;
DROP TABLE IF EXISTS music;
DROP TABLE IF EXISTS urls;
DROP TABLE IF EXISTS quotes;

CREATE TABLE people ( id INT NOT NULL AUTO_INCREMENT, 
  # core field mappings from samplecontainer example
  os_id VARCHAR(255) NOT NULL,  # corresponds to Person.id
  name VARCHAR(255) NOT NULL,  
  phoneNumbers  VARCHAR(255),  
  gender  VARCHAR(255),  
  dateOfBirth DATE, 
  updated DATE,  

  # additional field mappings from ...opensocial.model 
  aboutMe VARCHAR(1000),
  /* activities detail table */
  /* addresses detail table */
  age INT,
  /* bodytype fields just added as columns*/
  build VARCHAR(255),
  eyeColor VARCHAR(255),
  hairColor VARCHAR(255),
  height  VARCHAR(255),
  weight  VARCHAR(255),

  books VARCHAR(2000), /* comma separated */
  cars VARCHAR(2000) , /* comma separated */
  children VARCHAR(255),
  currentLocation VARCHAR(500),
  drinker VARCHAR(255), /* just used VARCHAR instead of ENUM */
  ethnicity VARCHAR(255),
  fashion VARCHAR(500),
  food VARCHAR(5000), /*  comma separated */
  happiestWhen VARCHAR(2000),
  heroes VARCHAR(2000),  /* comma separated */
  humor  VARCHAR(2000),
  interests VARCHAR(2000),  /* comma separated */
  jobInterests VARCHAR(2000),
  /* jobs mapped to organizations detail table with org_type col = job  */
  languagesSpoken VARCHAR(2000),  /* comma separated */
  livingArrangement VARCHAR(2000),
  lookingFor VARCHAR(2000),
  /* movies detail table */
  /* music detail table */
  nickname VARCHAR(2000),
  pets VARCHAR(2000),
  politicalViews VARCHAR(2000),
  /* profileSong mapped to urls detail table with url_type col = profileSong
     profileUrl mapped to urls detail table with url_type col = profileURL
     profileVideo mapped to urls detail table with url_type col = profileVideo */
  /* quotes mapped to quotes detail table */
  relationshipStatus VARCHAR(500),
  religion VARCHAR(500),
  romance VARCHAR(500),
  scaredOf VARCHAR(500),
  /* schools mapped to organizations detail table with org_type col = school  */
  sexualOrientation VARCHAR(500),
  smoker VARCHAR(255),
  sports VARCHAR(2000),  /* comma delimited */
  status VARCHAR(255),
  tags VARCHAR(5000),  /* comma delimited */
  thumbnailUrl VARCHAR(5000),
  timeZone VARCHAR(255),
  turnOffs VARCHAR(3000),  /* comma delimited */
  turnOns VARCHAR(3000),   /* comma delimited */
  tvShows VARCHAR(3000),   /* comma delimited */ 
  /* urls mapped to urls detail table with url_type col = generic */
  PRIMARY KEY (id)
);



create table addresses ( id INT NOT NULL AUTO_INCREMENT, 
  country VARCHAR(500),
  extendedAddress VARCHAR(2000),
  latitude FLOAT,
  longitude FLOAT,
  locality VARCHAR(500),
  poBox VARCHAR(255),
  postalCode VARCHAR(255),
  region VARCHAR(255),
  streetAddress VARCHAR(500),
  atype VARCHAR(255),
  unstructuredAddress VARCHAR(500),
  person_id INT,
  organization_id INT,
  PRIMARY KEY (id),
  CONSTRAINT person_fk FOREIGN KEY person_fk (person_id)
    REFERENCES people (id),
  CONSTRAINT organization_fk FOREIGN KEY organization_fk (organization_id)
    REFERENCES organizations (id) 
);


create table organizations ( id INT NOT NULL AUTO_INCREMENT, 
  description VARCHAR(5000),
  org_type VARCHAR(255),
  endDate DATE,
  field  VARCHAR(500),
  name  VARCHAR(1000),
  salary VARCHAR(500),
  startDate DATE,
  subField VARCHAR(500),
  title VARCHAR(1000),
  webpage VARCHAR(1000),
  PRIMARY KEY (id)
); 

create table movies ( id INT NOT NULL AUTO_INCREMENT, 
  title VARCHAR(500),
  person_id INT,
  PRIMARY KEY (id),
  CONSTRAINT person_movie_fk FOREIGN KEY person_movie_fk (person_id)
    REFERENCES people (id)
);


create table music ( id INT NOT NULL AUTO_INCREMENT, 
  title VARCHAR(500),
  person_id INT,
  PRIMARY KEY (id),
  CONSTRAINT person_fk FOREIGN KEY person_fk (person_id)
    REFERENCES people (id)
);

create table urls ( id INT NOT NULL AUTO_INCREMENT, 
  url_type VARCHAR(255),
  address  VARCHAR(1000),
  linkText  VARCHAR(5000),
  type VARCHAR(500),
  person_id INT,
  PRIMARY KEY (id),
  CONSTRAINT person_fk FOREIGN KEY person_fk (person_id)
    REFERENCES people (id)
); 

create table quotes ( id INT NOT NULL AUTO_INCREMENT, 
  text VARCHAR(500),
  person_id INT,
  PRIMARY KEY (id),
  CONSTRAINT person_fk FOREIGN KEY person_fk (person_id)
    REFERENCES people (id)
);

# Insert sample people from xml example
Insert into people (os_id, name, gender,  updated) 
  values ('john.doe','John Doe', 'MALE',  SYSDATE());
Insert into people (os_id, name, phoneNumbers, gender,  updated) 
  values ('jane.doe','Jane Doe', '867-5309', 'FEMALE',  SYSDATE());
Insert into people (os_id, name, gender,  updated) 
  values ('george.doe','George Doe', 'MALE',  SYSDATE());
Insert into people (os_id, name, gender,  updated) 
  values ('mario.rossi','Mario Rossi', 'MALE',  SYSDATE());
Insert into people (os_id, name,  gender,  updated) 
  values ('maija.m', 'Maija Meikäläin', 'FEMALE',  SYSDATE());


# Insert some new sample people
Insert into people (os_id, name, phoneNumbers, gender, dateOfBirth, updated) 
  values ('mickey','Mickey Mouse','1-800-disneyland', 'MALE', '1928-05-15', SYSDATE());
Insert into people (os_id, name, phoneNumbers, gender, dateOfBirth, updated) 
  values ('minnie','Minnie Mouse','1-800-disneyland', 'FEMALE', '1928-11-21', SYSDATE());
Insert into people (os_id, name, phoneNumbers, gender, dateOfBirth, updated) 
  values ('dduck','Donald Duck','1-800-disneyland', 'MALE', '1934-06-09', SYSDATE());
Insert into people (os_id, name, phoneNumbers, gender, dateOfBirth, updated) 
  values ('daffy','Daffy Duck','1-800-warnerbros', 'MALE','1937-04-17', SYSDATE());
Insert into people (os_id, name, phoneNumbers, gender, dateOfBirth, updated) 
  values ('porky','Porky Pig','1-800-warnerbros', 'MALE','1931-01-31', SYSDATE());
Insert into people (os_id, name, phoneNumbers, gender, dateOfBirth, updated) 
  values ('bugs','Bugs Bunny','1-800-warnerbros', 'MALE','1938-07-27', SYSDATE());



# Create person_data table.
# This table holds the relationship between people and their respective friends

CREATE TABLE person_friends (
  person VARCHAR(255),
  friend VARCHAR(255)
);


# Insert friends relationships

# john.doe friends
insert into person_friends values ('john.doe','jane.doe');
insert into person_friends values ('john.doe','george.doe');
insert into person_friends values ('john.doe','maija.m');

# jane.doe friends
insert into person_friends values ('jane.doe','john.doe');

# george.doe friends
insert into person_friends values ('george.doe','john.doe');



# mickey friends
insert into person_friends values ('mickey','minnie');
insert into person_friends values ('mickey','dduck');

# minnie friends
insert into person_friends values ('minnie','mickey');
insert into person_friends values ('minnie','dduck');

# dduck friends
insert into person_friends values ('dduck','mickey');
insert into person_friends values ('dduck','minnie');
insert into person_friends values ('dduck','daffy');

# daffy friends
insert into person_friends values ('daffy','dduck');

# bugs friends 
insert into person_friends values ('bugs','porky');
insert into person_friends values ('bugs','daffy');
insert into person_friends values ('bugs','minnie');

# porky friends 
insert into person_friends values ('porky','bugs');
insert into person_friends values ('porky','daffy');


# Create person_appdata table.
# This table holds the persistent application data for each user

CREATE TABLE person_appdata (
  id INT NOT NULL AUTO_INCREMENT,
  person VARCHAR(255),
  field VARCHAR(255),
  data VARCHAR(10000),
  PRIMARY KEY (id)
);

# Insert initial application data
insert into person_appdata (person, field, data) values ('george.doe', 'count', '2');
insert into person_appdata (person, field, data) values ('jane.doe', 'count', '7');
insert into person_appdata (person, field, data) values ('minnie', 'realname', 'Minerva');
insert into person_appdata (person, field, data) values ('dduck', 'middlename', 'Fauntleroy');
insert into person_appdata (person, field, data) values ('mickey', 'nickname', 'steamboat willie');


# Create activity_streams table
# This is the master table that records activity streams.
CREATE TABLE activity_streams (
  id INT NOT NULL AUTO_INCREMENT,
  userid VARCHAR(255),
  title VARCHAR(255),
  PRIMARY KEY (id)
);

# An initial activity stream is inserted
insert into activity_streams (userid, title) values ('jane.doe', 'jane''s photos');
insert into activity_streams (userid, title) values ('minnie', 'Minnie''s diary');


# Create Activities table
# This table holds multiple child activities per activity stream
CREATE TABLE activities (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(2000),
  body VARCHAR(5000),
  stream_id INT,
  PRIMARY KEY (id),
  CONSTRAINT stream_fk FOREIGN KEY stream_fk (stream_id)
    REFERENCES activity_streams (id)
);

# Initial activities data
insert into activities (title, body, stream_id) 
   values ('Jane just posted a photo of a monkey', 'and she thinks you look like him!','1');

insert into activities (title, body, stream_id) 
   values ('Jane says George likes Yoda!', 'or is it you?','1');

insert into activities (title, body, stream_id) 
   values ('Minnie bought a new blue dress', 'Minnie just bought a new blue dress. Check it out!','2');

insert into activities (title, body, stream_id) 
   values ('Minnie was in a parade', 'Minnie was in the parade at the Magic Kingdom','2');


# Create activity_media_items table
# This table holds the child activity_media_items per each activity.
CREATE TABLE activity_media_items (
  id INT NOT NULL AUTO_INCREMENT,
  type VARCHAR(255),
  mimetype VARCHAR(255),
  url VARCHAR(1000),
  activity_id INT,
  PRIMARY KEY (id),
  CONSTRAINT ai_fk FOREIGN KEY ai_fk (activity_id)
    REFERENCES activities (id)
);

# Insert initial media items

insert into activity_media_items (type, mimetype, url, activity_id) 
   values ('IMAGE', 'image/jpeg', 'http://animals.nationalgeographic.com/staticfiles/NGS/Shared/StaticFiles/animals/images/primary/black-spider-monkey.jpg', 1);

insert into activity_media_items (type, mimetype, url, activity_id) 
   values ('IMAGE', 'image/jpeg', 'http://image.guardian.co.uk/sys-images/Guardian/Pix/gallery/2002/01/03/monkey300.jpg', 1);

insert into activity_media_items (type, mimetype, url, activity_id) 
   values ('IMAGE', 'image/jpeg', 'http://www.funnyphotos.net.au/images/fancy-dress-dog-yoda-from-star-wars1.jpg', 2);

insert into activity_media_items (type, mimetype, url, activity_id) 
   values ('IMAGE', 'image/jpeg', 'http://imagecache2.allposters.com/images/pic/ADVG/660~Minnie-Mouse-Posters.jpg', 3);

insert into activity_media_items (type, mimetype, url, activity_id) 
   values ('IMAGE', 'image/jpeg', 'http://www.jtcent.com/disneyland/special/spedp7/images/spedp7p30.jpg', 4);