CREATE TABLE IF NOT EXISTS student (
id_user INT NOT NULL AUTO_INCREMENT ,
name VARCHAR( 128 ) NOT NULL ,
email VARCHAR( 64 ) NOT NULL ,
username VARCHAR( 16 ) NOT NULL ,
password VARCHAR( 32 ) NOT NULL ,
confirmcode VARCHAR(32) ,
token INT NOT NULL DEFAULT '3' ,
cvdir VARCHAR( 256 ), 
status VARCHAR(32) NOT NULL,
create_time DATETIME NOT NULL,
PRIMARY KEY ( id_user ),
);

INSERT INTO student 
(id_user,name,email,username,password,confirmcode,token,cvdir,status,create_time)
VALUES
('1','Kevin Huang','kehuang.pku@gmail.com','khuang24','6d431ba450f3b2912df5f2ea5355c86e','y','992','/home1/offerlad/public_html/CV/Kevin_kehuang.pku@gmail.com/CV_Ke(Kevin)Huang_PharmaR&D.pdf','registered','2015-07-30 20:00:00');

CREATE TABLE IF NOT EXISTS job (".
"jobId INT NOT NULL AUTO_INCREMENT ,".
"company VARCHAR (255) NOT NULL ,".
"title VARCHAR (255) NOT NULL ,".
"link VARCHAR (255) NOT NULL ,".
"location VARCHAR (100) NOT NULL ,".
"description TEXT NOT NULL ,".
"requirement TEXT ,".
"tag_state VARCHAR (255) NOT NULL DEFAULT 'Other', ".
"tag_company VARCHAR (255) NOT NULL DEFAULT 'Other', ".
"tag_type VARCHAR (255) NOT NULL DEFAULT 'Other', ".
"tag_salary VARCHAR (255) NOT NULL DEFAULT 'Other', ".
"tag_category VARCHAR (255) NOT NULL DEFAULT 'FULL TIME', ".
"tag_source VARCHAR (255) NOT NULL DEFAULT 'Referral', ".
"contact VARCHAR (255), ".
"display BOOLEAN NOT NULL DEFAULT TRUE,".
"FULLTEXT (company,title,location,description,requirement,tag_state,tag_company,tag_type,tag_category),".
"PRIMARY KEY (jobId)".
")";

CREATE TABLE IF NOT EXISTS mentor
(
mentorId  INT NOT NULL AUTO_INCREMENT,
name VARCHAR(128) NOT NULL,
photo_url  VARCHAR(255) NOT NULL,
title  VARCHAR(1000) NOT NULL,
email  VARCHAR(255) NOT NULL,
industry  VARCHAR(500) NOT NULL,
summary TEXT NOT NULL ,
education TEXT NOT NULL ,
positions TEXT NOT NULL ,
tag_service_career TINYINT NOT NULL DEFAULT 0,
tag_service_cv TINYINT NOT NULL DEFAULT 0,
tag_service_interview TINYINT NOT NULL DEFAULT 0,
tag_workyear VARCHAR(255) NOT NULL,
tag_function VARCHAR(255) NOT NULL,
tag_company VARCHAR(255) NOT NULL,
availability VARCHAR(2500) NOT NULL,
confirmcode VARCHAR(32),
display TINYINT NOT NULL DEFAULT 0,
create_time DATETIME NOT NULL,
resume_url VARCHAR(255) NOT NULL,
status VARCHAR(32) NOT NULL,
resume TEXT NOT NULL,
PRIMARY KEY (mentorId)
);

INSERT INTO mentor (mentorId,name,photo_url,title,email,industry,summary,education,positions,tag_service_career,tag_service_cv,tag_service_interview,tag_workyear,tag_function,tag_company,availability,confirmcode,display,create_time,resume_url,status,resume)
VALUES (1,'Ke Wu','https://media.licdn.com/mpr/mprx/0_ukJ160dfZ75oq8ezuT4u6yOu4w-b4ieza5Du6y4rFm6nFbxvhCd0epuO9LtMUXWJSbRDdR6MOIRD','PhD student in Cheminformatics; Master in Computer Science and M.Eng in Environmental Engineering','wkcoke.work@gmail.com','Research','I have multiple backgrounds on environmental sciences, environmental engineering, chemistry and computer science. Before joining Breneman's cheminformatics group, I participated in researches including environmental sciences, quantum chemistry calculation and fluid mechanics modeling. Currently, my research interest is to use machine learning methods to solve practical industrial problems and look into a systematic way of material informatics research. I am familiar with Python and C++, as well as machine learning algorithms.','Nanjing University','Research Intern, Boehringer Ingelheim.','1','1','1','<3','IT','AbbVie','Weekend 11pm','e5146b89678ecd87d9fd3f1ae58baa53','1','2015-01-01 00:00:01','https://media.licdn.com/mpr/mprx/0_ukJ160dfZ75oq8ezuT4u6yOu4w-b4ieza5Du6y4rFm6nFbxvhCd0epuO9LtMUXWJSbRDdR6MOIRD','accepted','Nanjing University');

CREATE TABLE IF NOT EXISTS mentor_account
(
mentorId INT NOT NULL,
type  VARCHAR(32) NOT NULL,
create_time DATETIME NOT NULL,
account_info TEXT NOT NULL,
balance INT NOT NULL DEFAULT '0',
PRIMARY KEY(mentorId),
FOREIGN KEY (mentorId) REFERENCES mentor(mentorId) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TRIGGER Mentor_Update
AFTER INSERT ON mentor
REFERENCING NEW ROW AS New
FOR EACH ROW
INSERT INTO mentor_account(mentorId,type,create_time,account_info)  VALUES (New.mentorId,'new',GETDATE(),'regular');
 
 ?

CREATE TABLE IF NOT EXISTS student_account
(
studentId INT NOT NULL,
type  VARCHAR(32) NOT NULL,
create_time DATETIME NOT NULL,
account_info VARCHAR(255) NOT NULL,
token INT NOT NULL DEFAULT '3',
PRIMARY KEY(studentId),
FOREIGN KEY(studentId) REFERENCES student(id_user)  ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE IF NOT EXISTS production
(
productionId INT NOT NULL,
owner INT NOT NULL,
name VARCHAR(128) NOT NULL,
description TEXT NOT NULL,
provider INT NOT NULL,
create_time DATETIME NOT NULL,
PRICE FLOAT NOT NULL,
PRIMARY KEY(productionId),
FOREIGN KEY(owner) REFERENCES mentor(mentorId) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE IF NOT EXISTS transaction
(
transactionId INT NOT NULL,
studentId INT NOT NULL,
productId INT NOT NULL,
status VARCHAR(32) NOT NULL,
create_time DATETIME NOT NULL,
close_time DATETIME NOT NULL,
PRIMARY KEY(transactionId),
FOREIGN KEY(studentId) REFERENCES student(id_user) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(productId) REFERENCES production(productionId) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE IF NOT EXISTS payment
(
paymentId INT NOT NULL,
type VARCHAR(32) NOT NULL,
transactionId INT NOT NULL,
status VARCHAR(32) NOT NULL,
PRIMARY KEY(paymentId),
FOREIGN KEY(transactionId) REFERENCES transaction(transactionId) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE IF NOT EXISTS transaction_activity
(
activityId INT NOT NULL,
transactionId INT NOT NULL,
activityType VARCHAR(32) NOT NULL,
activityTime DATETIME NOT NULL,
created_by VARCHAR(128) NOT NULL,
PRIMARY KEY(activityId),
FOREIGN KEY(transactionId) REFERENCES transaction(transactionId) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE IF NOT EXISTS studentproduction(
studentProductionId INT NOT NULL,
studentId INT NOT NULL,
productionId INT NOT NULL,
PRIMARY KEY(studentProductionId),
FOREIGN KEY(studentId) REFERENCES student(studentId) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(productionId) REFERENCES production(productionId) ON DELETE CASCADE ON UPDATE CASCADE
)