# clean house first
DROP DATABASE IF EXISTS cluelesspc;

# create a new instance
CREATE DATABASE IF NOT EXISTS cluelesspc;

USE cluelesspc;

DROP TABLE IF EXISTS tblUser;
CREATE TABLE IF NOT EXISTS tblUser
(
	UserID INT AUTO_INCREMENT NOT NULL,
	LoginID VARCHAR(100),
	Password VARCHAR(100),
	FirstName VARCHAR(100),
	LastName VARCHAR(100),
	PRIMARY KEY (UserID)
);


DROP TABLE IF EXISTS tblUserLanguage;
CREATE TABLE IF NOT EXISTS tblUserLanguage
(
	UserID INT NOT NULL,
	LanguageID INT NOT NULL,
	SortOrder INT,
	PRIMARY KEY (UserID, LanguageID)
);


DROP TABLE IF EXISTS tblLanguage;
CREATE TABLE IF NOT EXISTS tblLanguage
(
	LanguageID INT AUTO_INCREMENT NOT NULL,
	Name varchar(100),
	PRIMARY KEY (LanguageID)
);

DROP TABLE IF EXISTS tblDocument;
CREATE TABLE IF NOT EXISTS tblDocument
(
	DocumentID INT AUTO_INCREMENT NOT NULL,
	LanguageID INT NOT NULL,
	Name varchar(100),
	FileName varchar(100),
	PRIMARY KEY (DocumentID)
);

DROP TABLE IF EXISTS tblDocCom;
CREATE TABLE IF NOT EXISTS tblDocCom
(
	DocumentID INT NOT NULL,
	ComponentID INT NOT NULL,
	SortOrder INT,
	PRIMARY KEY (DocumentID, ComponentID)
);

DROP TABLE IF EXISTS tblComponent;
CREATE TABLE IF NOT EXISTS tblComponent
(
	ComponentID INT AUTO_INCREMENT NOT NULL,
	ContentTypeID INT NOT NULL,
	Name varchar(100),
	Content TEXT,
	FileName varchar(100),
	Path varchar(100),
	Size varchar(100),
	PRIMARY KEY (ComponentID)
);

DROP TABLE IF EXISTS tblContentType;
CREATE TABLE IF NOT EXISTS tblContentType
(
	ContentTypeID INT AUTO_INCREMENT NOT NULL,
	Name varchar(100),
	PRIMARY KEY (ContentTypeID)
);