/*
//-------------------------------------------------------------------
/// File       : create.sql
/// Version    : 1.3
/// Created    : 2008-08-29
/// Modified   : 2009-04-20
///
/// Author     : William Chang
/// Email      : william@babybluebox.com
/// Website    : http://www.babybluebox.com
///
/// Compatible : Microsoft SQL Server 8+
//-------------------------------------------------------------------
/// References:
/// http://www.techonthenet.com/sql/tables/create_table.php
/// http://www.1keydata.com/sql/sqlcreate.html
/// http://www.1keydata.com/sql/sql-foreign-key.html
/// http://www.w3schools.com/SQL/sql_view.asp
/// http://www.techonthenet.com/sql/views.php
//-------------------------------------------------------------------
*/

/* Code Snippets
//-------------------------------------------------------------------*/
/*

Equivalent to varchar(max) for Microsoft SQL Server 2000:
nvarchar(8000)

Equivalent to nvarchar(max) for Microsoft SQL Server 2000:
nvarchar(4000)

Equivalent to varbinary(max) for Microsoft SQL Server 2000:
image

Create View:
CREATE VIEW view_name AS
SELECT columns FROM table WHERE condition

Update View:
CREATE OR REPLACE VIEW view_name AS
SELECT columns FROM table WHERE condition

Query View:
SELECT * FROM view_name;

Drop View:
DROP VIEW view_name; 

Add Foreign Key:
ALTER TABLE [dbo].[user_profiles] ADD FOREIGN KEY(user_id) REFERENCES users(user_id);
ALTER TABLE [dbo].[user_profiles] ADD CONSTRAINT fk_user_profiles_users FOREIGN KEY(user_id) REFERENCES users(user_id);

Drop Foreign Key:
ALTER TABLE [dbo].[user_profiles] DROP FOREIGN KEY fk_user_profiles_users;

*/

/* CREATE DATABASE
//-------------------------------------------------------------------*/
CREATE DATABASE [ucf_ent];
GO

/* USE
//-------------------------------------------------------------------*/
USE [ucf_ent];
GO

/* CREATE TABLE (database assumes "null" as default)
//-------------------------------------------------------------------*/
CREATE TABLE [dbo].[users](
    user_id int not null IDENTITY(1,1) PRIMARY KEY,
    user_alias nvarchar(64) not null,
    user_name_first nvarchar(64) not null,
    user_name_last nvarchar(64) not null,
    user_password nvarchar(64) not null,
    user_email nvarchar(128) not null,
    user_activation_key nvarchar(64) not null,
    user_activated bit not null DEFAULT 0,
    user_session_key nvarchar(64) not null DEFAULT '',
    user_date_login datetime null,
    user_date_activation_created datetime null,
    user_date_session_created datetime null,
    user_date_created datetime not null,
    user_deleted bit not null DEFAULT 0
);
INSERT INTO users(user_alias, user_name_first, user_name_last, user_password, user_email, user_activation_key, user_activated, user_date_activation_created, user_date_created) VALUES ('diehard', 'William', 'Chang', 'rCO9+p2VwqEQNvDGHCyUIg==', 'william@babybluebox.com', '', 1, GETDATE(), GETDATE());

CREATE TABLE [dbo].[user_profiles](
    user_id int not null,
    user_name_salutation nvarchar(32) not null,
    user_name_middle nvarchar(64) not null,
    user_occupation nvarchar(128) not null,
    user_phone nvarchar(64) not null,
    user_phone_extension nvarchar(16) not null,
    user_organization_location_id int not null
);

CREATE TABLE [dbo].[user_settings](
    setting_id int not null IDENTITY(1,1) PRIMARY KEY,
    setting_user_id int not null,
    setting_key nvarchar(256) not null,
    setting_value nvarchar(512) not null DEFAULT ''
);
INSERT INTO user_settings(setting_user_id, setting_key, setting_value) VALUES (1, 'role', '1');
INSERT INTO user_settings(setting_user_id, setting_key, setting_value) VALUES (1, 'upload_session_id', '');

CREATE TABLE [dbo].[locations](
    location_id int not null IDENTITY(1,1) PRIMARY KEY,
    location_name nvarchar(256) not null,
    location_address1 nvarchar(128) not null,
    location_address2 nvarchar(128) not null DEFAULT '',
    location_city nvarchar(64) not null,
    location_state nvarchar(64) not null,
    location_zip nvarchar(32) not null,
    location_country nvarchar(64) not null,
    location_deleted bit not null DEFAULT 0
);

CREATE TABLE [dbo].[application_settings](
    setting_id int not null IDENTITY(1,1) PRIMARY KEY,
    setting_application_id int not null DEFAULT 0,
    setting_key nvarchar(256) not null,
    setting_value nvarchar(512) not null DEFAULT ''
);

CREATE TABLE [dbo].[application_logs](
    log_id int not null IDENTITY(1,1) PRIMARY KEY,
    log_user_id int not null,
    log_code int not null,
    log_message nvarchar(2048) not null,
    log_date_created datetime not null,
    log_deleted bit not null DEFAULT 0
);

CREATE TABLE [dbo].[lists](
    list_id int not null IDENTITY(1,1) PRIMARY KEY,
    list_type int not null,
    list_category_id int not null,
    list_symbol nvarchar(512) not null DEFAULT '',
    list_name nvarchar(1024) not null,
    list_value int not null,
    list_description nvarchar(2048) not null DEFAULT '',
    list_parent_id int not null DEFAULT 0,
    list_order int not null DEFAULT 10,
    list_deleted bit not null DEFAULT 0
);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (1, 0, 'User Roles', 0);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (1, 0, 'Content Types', 0);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (1, 0, 'Content Statuses', 0);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (1, 0, 'Content Order Types', 0);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (1, 0, 'File Types', 0);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 1, 'Administrator', 1);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 1, 'Moderator', 2);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 1, 'Author', 3);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 1, 'Subscriber', 4);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 2, 'Page', 1);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 2, 'Blog', 2);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 2, 'Post', 3);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 2, 'Template', 4);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 2, 'Style', 5);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 2, 'System', 6);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 2, 'Revision', 7);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 3, 'Publish', 1);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 3, 'Pending Review', 2);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 3, 'Draft', 3);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 4, 'Default', 1);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 4, 'Date Published ASC', 2);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 4, 'Date Modified ASC', 3);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 4, 'Date Created ASC', 4);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 4, 'Header ASC', 5);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 5, 'Document', 1);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 5, 'Image', 2);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 5, 'Sound', 3);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 5, 'Video', 4);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 5, 'Multimedia', 5);
INSERT INTO lists(list_type, list_category_id, list_name, list_value) VALUES (2, 5, 'Archive', 6);

CREATE TABLE [dbo].[contents](
    content_id int not null IDENTITY(1,1) PRIMARY KEY,
    content_type int not null,
    content_parent_id int not null,
    content_assignedto int not null DEFAULT 0,
    content_author_user_id int not null,
    content_editor_user_id int not null,
    content_title nvarchar(1024) not null,
    content_title_slug nvarchar(1024) not null,
    content_header nvarchar(2048) not null DEFAULT '',
    content_body nvarchar(max) not null DEFAULT '',
    content_footer nvarchar(2048) not null DEFAULT '',
    content_status int not null,
    content_style_id int not null DEFAULT 0,
    content_template_id int not null DEFAULT 0,
    content_order int not null DEFAULT 10,
    content_order_type int not null DEFAULT 1,
    content_order_menu int not null DEFAULT 10,
    content_date_modified datetime not null,
    content_date_published datetime null,
    content_date_created datetime not null,
    content_deleted bit not null DEFAULT 0
);
INSERT INTO contents(content_type, content_parent_id, content_author_user_id, content_editor_user_id, content_title, content_title_slug, content_body, content_status, content_date_modified, content_date_created) VALUES (1, 0, 1, 1, 'Page Header Test', 'page-header-test', 'Body Test', 1, GETDATE(), GETDATE());
INSERT INTO contents(content_type, content_parent_id, content_author_user_id, content_editor_user_id, content_title, content_title_slug, content_body, content_status, content_date_modified, content_date_created) VALUES (2, 0, 1, 1, 'Blog Header Test', 'blog-header-test', '', 1, GETDATE(), GETDATE());
INSERT INTO contents(content_type, content_parent_id, content_author_user_id, content_editor_user_id, content_title, content_title_slug, content_body, content_status, content_date_modified, content_date_created) VALUES (3, 2, 1, 1, 'Post Header Test 1', 'post-header-test-1', 'Body Test', 1, GETDATE(), GETDATE());
INSERT INTO contents(content_type, content_parent_id, content_author_user_id, content_editor_user_id, content_title, content_title_slug, content_body, content_status, content_date_modified, content_date_created) VALUES (3, 2, 1, 1, 'Post Header Test 2', 'post-header-test-2', 'Body Test', 1, GETDATE(), GETDATE());
INSERT INTO contents(content_type, content_parent_id, content_author_user_id, content_editor_user_id, content_title, content_title_slug, content_body, content_status, content_date_modified, content_date_created) VALUES (4, 0, 1, 1, 'Template Header Test', 'template-header-test', 'Code Test', 1, GETDATE(), GETDATE());
INSERT INTO contents(content_type, content_parent_id, content_author_user_id, content_editor_user_id, content_title, content_title_slug, content_body, content_status, content_date_modified, content_date_created) VALUES (5, 0, 1, 1, 'Style Header Test', 'style-header-test', 'Code Test', 1, GETDATE(), GETDATE());
INSERT INTO contents(content_type, content_parent_id, content_author_user_id, content_editor_user_id, content_title, content_title_slug, content_body, content_status, content_date_modified, content_date_created) VALUES (6, 0, 1, 1, 'System Header Test', 'system-header-test', 'Code Test', 1, GETDATE(), GETDATE());

CREATE TABLE [dbo].[files](
    file_id int not null IDENTITY(1,1) PRIMARY KEY,
    file_type int not null,
    file_uploader_user_id int not null,
    file_name nvarchar(1024) not null,
    file_name_slug nvarchar(1024) not null,
    file_description nvarchar(2048) not null DEFAULT '',
    file_binary varbinary(max) not null,
    file_extension varchar(64) not null,
    file_size_bytes int not null,
    file_count_download int not null DEFAULT 0,
    file_date_created datetime not null,
    file_deleted bit not null DEFAULT 0
);

CREATE TABLE [dbo].[file_filter_extensions](
    extension_id int not null IDENTITY(1,1) PRIMARY KEY,
    extension_file_type int not null,
    extension_name nvarchar(64) not null,
    extension_description nvarchar(2048) not null,
    extension_deleted bit not null DEFAULT 0
);
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (1, 'txt', 'Text Document');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (1, 'rtf', 'Rich Text Document');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (1, 'odt', 'OpenDocument Text');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (1, 'ods', 'OpenDocument Spreadsheet');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (1, 'odp', 'OpenDocument Presentation');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (1, 'odg', 'OpenDocument Graphics');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (1, 'odf', 'OpenDocument Math');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (1, 'pdf', 'Adobe Acrobat Document');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (1, 'doc', 'Microsoft Word Document');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (1, 'docx', 'Microsoft Word Document');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (1, 'ppt', 'Microsoft PowerPoint Presentation');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (1, 'pptx', 'Microsoft PowerPoint Presentation');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (1, 'xls', 'Microsoft Excel Worksheet');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (1, 'xlsx', 'Microsoft Excel Worksheet');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (2, 'png', 'PNG Image');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (2, 'jpg', 'JPEG Image');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (2, 'jpeg', 'JPEG Image');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (2, 'gif', 'GIF Image');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (2, 'bmp', 'BMP Image');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (2, 'psd', 'Adobe Photoshop Image');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (3, 'ogg', 'MP3 Sound');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (3, 'wav', 'WAV Sound');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (3, 'mp3', 'MP3 Sound');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (3, 'aac', 'AAC Sound');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (5, 'mp4', 'MP4 Multimedia');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (5, 'mov', 'Apple QuickTime');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (6, 'zip', 'ZIP Archive');
INSERT INTO file_filter_extensions(extension_file_type, extension_name, extension_description) VALUES (6, 'rar', 'RAR Archive');

/* CREATE VIEW
//-------------------------------------------------------------------*/
CREATE VIEW [dbo].[vw_contents] AS(
    SELECT
        t1.*
    FROM
        contents AS c1
        INNER JOIN contents AS c2 ON c1.content_id = c2.content_parent_id
)

CREATE VIEW [dbo].[vw_contents_extended] AS(
    SELECT
        c1.*,
        u1.user_name_first AS author_name_first, u1.user_name_last AS author_name_first, u1.user_email AS author_email,
        u2.user_name_first AS editor_name_first, u2.user_name_last AS editor_name_last, u2.user_email AS editor_email,
        l1.list_name AS content_type_name,
        l2.list_name AS content_status_name,
    FROM
        contents AS c1
        LEFT OUTER JOIN users AS u1 ON c1.content_author_user_id = u1.user_id
        LEFT OUTER JOIN users AS u2 ON c1.content_editor_user_id = u2.user_id
        LEFT OUTER JOIN lists AS l1 ON c1.content_type = l1.list_value AND l1.list_category_id =  2
        LEFT OUTER JOIN lists AS l2 ON c1.content_status = l2.list_value AND l2.list_category_id =  3
)

CREATE VIEW [dbo].[vw_contents_hierarchy] AS(
    SELECT
        content_id,
        content_parent_id,
        content_assignedto,
        content_title,
        content_title_slug,
        content_order_menu
    FROM
        contents
    WHERE
        content_type IN (1, 2)
        AND content_status = 1
        AND content_deleted = 0
)