-- $Id: init_lams_tool_twitter.sql,v 1.14 2007/02/14 11:17:54 fmalikoff Exp $

-- Ensure an existing table does not exist
DROP TABLE IF EXISTS tl_fptwit10_user;
DROP TABLE IF EXISTS tl_fptwit10_attachment;
DROP TABLE IF EXISTS tl_fptwit10_session;
DROP TABLE IF EXISTS tl_fptwit10_content;

-- Create a fresh copy of the twitter table
CREATE TABLE tl_fptwit10_content (
       uid BIGINT(20) NOT NULL AUTO_INCREMENT
     , twitter_content_id BIGINT(20) NOT NULL
     , title TEXT
     , content TEXT
     , online_instructions TEXT
     , offline_instructions TEXT
     , define_later TINYINT(1)
     , force_offline TINYINT(1)
     , content_in_use TINYINT(1)
     , creator_user_id BIGINT(20)
     , date_created DATETIME
     , date_updated DATETIME
     , teacherTwitterUsername VARCHAR(255)
     , learnersTwitterUsernames VARCHAR(511)
     , hashtags VARCHAR(255)
     , tweetNumber TINYINT(2) DEFAULT 1
     , reflect_instructions TEXT
     , reflect_on_activity TINYINT(1)
     , activity_hashtag VARCHAR(12)
     , PRIMARY KEY (uid)
)TYPE=InnoDB;

CREATE TABLE tl_fptwit10_session (
       uid BIGINT(20) NOT NULL AUTO_INCREMENT
     , twitter_session_id BIGINT(20) UNIQUE NOT NULL
     , twitter_content_uid BIGINT(20) NOT NULL
     , session_start_date DATETIME
     , session_end_date DATETIME
     , session_status VARCHAR(100)
     , PRIMARY KEY (uid)
     , INDEX (twitter_content_uid)
     , CONSTRAINT FK_NEW_1207806667__tl_fptwit10_session_1 FOREIGN KEY (twitter_content_uid)
                  REFERENCES lams.tl_fptwit10_content (uid)
)TYPE=InnoDB;

CREATE TABLE tl_fptwit10_user (
       uid BIGINT(20) NOT NULL AUTO_INCREMENT
     , user_id BIGINT(20) NOT NULL
     , twitter_session_uid BIGINT(20) NOT NULL
     , username VARCHAR(255)
     , fullname VARCHAR(255)
     , user_status VARCHAR(50)
     , PRIMARY KEY (uid)
     , INDEX (twitter_session_uid)
     , CONSTRAINT FK_NEW_1207806667__tl_fptwit10_user_1 FOREIGN KEY (twitter_session_uid)
                  REFERENCES lams.tl_fptwit10_session (uid)
)TYPE=InnoDB;

CREATE TABLE lams.tl_fptwit10_user_tweet (
       tweet_id BIGINT(20) NOT NULL AUTO_INCREMENT
     , twitter_user_uid BIGINT(20) NOT NULL
     , twitter_content_uid BIGINT(20) NOT NULL
     , tweet VARCHAR(140)
     , twitter_username VARCHAR(255)
     , creation_date DATE
     , twitter_picture VARCHAR(511)
     , PRIMARY KEY (tweet_id)
     , INDEX (twitter_user_uid)
     , CONSTRAINT FK_tl_fptwit10_user_tweet_1 FOREIGN KEY (twitter_user_uid)
                  REFERENCES lams.tl_fptwit10_user (uid)
     , INDEX (twitter_content_uid)
     , CONSTRAINT FK_tl_fptwit10_user_tweet_2 FOREIGN KEY (twitter_content_uid)
                  REFERENCES lams.tl_fptwit10_content (uid)
)TYPE=InnoDB;

CREATE TABLE tl_fptwit10_attachment (
       attachment_id BIGINT(20) NOT NULL AUTO_INCREMENT
     , twitter_content_uid BIGINT(20) NOT NULL
     , filename VARCHAR(255) NOT NULL
     , uuid BIGINT(20) NOT NULL 
     , version_id BIGINT(20)
     , online_file TINYINT(1) NOT NULL
     , PRIMARY KEY (attachment_id)
     , INDEX (twitter_content_uid)
     , CONSTRAINT FK_NEW_1207806667__tl_fptwit10_attachment_1 FOREIGN KEY (twitter_content_uid)
                  REFERENCES lams.tl_fptwit10_content (uid)
)TYPE=InnoDB;
