-- Drop the previous tables
DROP TABLE EDAlist;
DROP TABLE EDAOption;
DROP TABLE EDAdocument;
DROP TABLE EDApath;
DROP TABLE EDAfileaccess;
DROP TABLE EDAStorepath;
DROP TABLE BBSList;
DROP TABLE BBSDocumentState;
DROP TABLE BBSOptionProperty;
DROP TABLE BBSBoardName;
DROP TABLE Club;
DROP TABLE Team;
DROP TABLE Official;
DROP TABLE Department;


-- Creat EDAlist table
CREATE TABLE EDAlist (
    docID				VARCHAR2(60)	CONSTRAINT pk_edalist  PRIMARY KEY,
    personID    		VARCHAR2(60),
    listID				VARCHAR2(60),
    visited				VARCHAR2(60)   
);


-- Creat EDAOption table
CREATE TABLE EDAOption (
    personID			VARCHAR2(60)	CONSTRAINT pk_edaoption  PRIMARY KEY,
    ingBoxSort    		VARCHAR2(60),                        
    ingSortMethod		VARCHAR2(60),
    completeBoxSort		VARCHAR2(60),   
    completeSortMethod	VARCHAR2(60),
    rejectBoxSort		VARCHAR2(60),
    rejectSortMethod	VARCHAR2(60),
    publicBoxSort		VARCHAR2(60),
    publicSortMethod	VARCHAR2(60),
    defaultPath			VARCHAR2(60),
    numOfItem			NUMBER(10)
);

-- Creat EDAdocument table
CREATE TABLE EDAdocument (
    docID				VARCHAR2(60)	CONSTRAINT pk_edadocument  PRIMARY KEY,
    initDay    			VARCHAR2(60),
    initPersonID		VARCHAR2(60),
    conditionCode		VARCHAR2(60),   
    title				VARCHAR2(60),
    description			VARCHAR2(4000),
    docCount			NUMBER(10)
);

-- Creat EDApath table
CREATE TABLE EDApath (
    docID				VARCHAR2(60)	CONSTRAINT pk_edapath  PRIMARY KEY,
    personID    		VARCHAR2(60),
    sequence			NUMBER(10),
    conditionCode		VARCHAR2(60),    
    description			VARCHAR2(4000),
    type				NUMBER(10),
    workDate			VARCHAR2(60)
);


-- Creat EDAfileaccess table
CREATE TABLE EDAfileaccess (
    docID				VARCHAR2(60)	CONSTRAINT pk_edafileaccess  PRIMARY KEY,
    fileName    		VARCHAR2(60),
    filePath			VARCHAR2(60),
    fileSize			NUMBER(10)
);

-- Creat EDAStorepath table
CREATE TABLE EDAStorepath(
    owner				VARCHAR2(60)	CONSTRAINT pk_edastorepath  PRIMARY KEY,
    courseName    		NUMBER(10),
    sequence			NUMBER(10),
    type				NUMBER(10)
);

-- Creat BBSList table
CREATE TABLE BBSList(
    TeamID				VARCHAR2(60)	CONSTRAINT pk_bbslist  PRIMARY KEY,
    BBSBoardName    	VARCHAR2(60),
    num					NUMBER(10),
    author				VARCHAR2(60),
    email				VARCHAR2(60),
    title				VARCHAR2(60),
    itemType			VARCHAR2(60),
    content				VARCHAR2(4000),
    attachedFile		VARCHAR2(60),
    bbsdate				VARCHAR2(60),
    viewCount			NUMBER(10),
    reference			NUMBER(10),
    step				NUMBER(10),
    lev					NUMBER(10)
);
	
-- Creat BBSDocumentState table
CREATE TABLE BBSDocumentState(
    id					VARCHAR2(60)	CONSTRAINT pk_bbsdocumentstate  PRIMARY KEY,
    num 				NUMBER(10)
);

-- Creat BBSOptionProperty table
CREATE TABLE BBSOptionProperty(
    id					VARCHAR2(60)	CONSTRAINT pk_bbsoptionproperty  PRIMARY KEY,
    sortMethod			VARCHAR2(60),
    firstBoard			VARCHAR2(60),
    period				NUMBER(10),
    numOfItemsInPage	NUMBER(10),
    title				VARCHAR2(60),
    author				VARCHAR2(60),
    content				VARCHAR2(4000)
);

-- Creat BBSBoardName table
CREATE TABLE BBSBoardName(
    BoardName			VARCHAR2(60)	CONSTRAINT pk_bbsboardname  PRIMARY KEY,
    Department			VARCHAR2(60),
    Team				VARCHAR2(60)
);

-- Creat club table
CREATE TABLE club(
    id					VARCHAR2(60)	CONSTRAINT pk_club  PRIMARY KEY,
    name				VARCHAR2(60),
    memeberName			VARCHAR2(60)
);
				
-- Creat Team table
CREATE TABLE Team(
    name				VARCHAR2(60)	CONSTRAINT pk_team  PRIMARY KEY,
    upDepartment		VARCHAR2(60),
    weightWork			VARCHAR2(60),
    webURL				VARCHAR2(60),
    developField		VARCHAR2(60),
    teamExplain			VARCHAR2(1000)
);

-- Creat Official table
CREATE TABLE Official(
    id						VARCHAR2(60)	CONSTRAINT pk_official  PRIMARY KEY,
    password				VARCHAR2(60),
    residentRegistrationNum	VARCHAR2(60),
    firstName				VARCHAR2(60),
    FamilyName				VARCHAR2(60),
    sex						VARCHAR2(60),
    age						VARCHAR2(60),
    address					VARCHAR2(1000),
    zipCode					VARCHAR2(60),
    phoneNumber				VARCHAR2(60),
    email					VARCHAR2(60),
    personalPhone			VARCHAR2(60),
    weightWork				VARCHAR2(60),
    department				VARCHAR2(60),
    team					VARCHAR2(60),
    orders					VARCHAR2(60),
    enterCompanyDay			VARCHAR2(60),
    homePhoneNumber			VARCHAR2(60),
    faxNumber				VARCHAR2(60),
    others					VARCHAR2(4000),
    userKind				VARCHAR2(60),
    imagePath				VARCHAR2(60)
);

-- Creat Department table
CREATE TABLE Department(
    name				VARCHAR2(60)	CONSTRAINT pk_department  PRIMARY KEY,
    weightWork			VARCHAR2(60),
    phoneNumber			VARCHAR2(60),
    faxNumber			VARCHAR2(60),
    webURL				VARCHAR2(60),
    developField		VARCHAR2(60),
    departmentExplain	VARCHAR2(1000)
);  	      		