/*
//-------------------------------------------------------------------
/// File       : stored_procedures.sql
/// Version    : 1.2
/// Created    : 2008-08-29
/// Modified   : 2009-04-08
///
/// Author     : William Chang
/// Email      : william@babybluebox.com
/// Website    : http://www.babybluebox.com
///
/// Compatible : Microsoft SQL Server 8+
//-------------------------------------------------------------------
/// References:
/// http://www.sqlteam.com/article/temporary-tables
/// http://www.udel.edu/evelyn/SQL-Class3/SQL3_SQL.html
///
/// Returning Data:
/// http://www.sqlteam.com/article/stored-procedures-returning-data
///
/// Joining Tables:
/// http://en.wikipedia.org/wiki/Join_(SQL)
///
/// Delimiting Data:
/// http://www.sqlbook.com/SQL/Create-comma-delimited-list-27.aspx
///
/// Dynamic SQL:
/// http://www.sommarskog.se/dynamic_sql.html
///
/// Hierarchy (recursive stored procedure):
/// http://www.sqlteam.com/article/sql-for-threaded-discussion-part-ii
//-------------------------------------------------------------------
*/

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

/* Dynamic SQL
//-------------------------------------------------------------------*/
IF OBJECT_ID('[dbo].[spGeneralSelect]') IS NOT NULL
    DROP PROCEDURE [dbo].[spGeneralSelect]
GO
CREATE PROCEDURE [dbo].[spGeneralSelect](
    @tablename nvarchar(256),
    @key nvarchar(256),
    @debug bit = 0
)
AS
SET NOCOUNT ON
BEGIN
    DECLARE @sql_statement nvarchar(max)
    DECLARE @sql_params nvarchar(max)

    SET @sql_params = 
    N'@key nvarchar(256)'
    
    SET @sql_statement = 
    N' SELECT *' + 
    N' FROM dbo.' + @tablename + 
    N' WHERE id = @key'
    
    IF @debug = 1 PRINT @sql_statement
    
    EXEC sp_executesql @sql_statement, @sql_params, @key = @key
END
SET NOCOUNT OFF
GO

/* INSERT
//-------------------------------------------------------------------*/
IF OBJECT_ID('[dbo].[spInsertUser]') IS NOT NULL
    DROP PROCEDURE [dbo].[spInsertUser]
GO
CREATE PROCEDURE [dbo].[spInsertUser](
    @user_alias nvarchar(64),
    @user_name_first nvarchar(64),
    @user_name_last nvarchar(64),
    @user_password nvarchar(64) = '',
    @user_email nvarchar(128),
    @user_activation_key nvarchar(64) = '',
    @user_activated bit = 0,
    @user_session_key nvarchar(64) = '',
    @user_date_login datetime = null,
    @user_date_activation_created datetime,
    @user_date_session_created datetime = null,
    @user_date_created datetime,
    @user_deleted bit = 0,
    
    @setting_key__role nvarchar(256) = 'role',
    @setting_value__role nvarchar(512) = '4',
    
    @setting_key__upload_session_id nvarchar(256) = 'upload_session_id',
    @setting_value__upload_session_id varchar(512) = ''
)
AS
SET NOCOUNT ON
BEGIN
    DECLARE @user_id int

    INSERT INTO users(
        user_alias,
        user_name_first,
        user_name_last,
        user_password,
        user_email,
        user_activation_key,
        user_activated,
        user_session_key,
        user_date_login,
        user_date_activation_created,
        user_date_session_created,
        user_date_created,
        user_deleted
    )
    VALUES(
        @user_alias,
        @user_name_first,
        @user_name_last,
        @user_password,
        @user_email,
        @user_activation_key,
        @user_activated,
        @user_session_key,
        @user_date_login,
        @user_date_activation_created,
        @user_date_session_created,
        @user_date_created,
        @user_deleted
    )
    SET @user_id = IDENT_CURRENT('users')
    
    INSERT INTO user_settings(
        setting_user_id,
        setting_key,
        setting_value
    )
    VALUES (
        @user_id,
        @setting_key__role,
        @setting_value__role
    );
    
    INSERT INTO user_settings(
        setting_user_id,
        setting_key,
        setting_value
    )
    VALUES (
        @user_id,
        @setting_key__upload_session_id,
        @setting_value__upload_session_id
    );
    
    /*SELECT SCOPE_IDENTITY()*/
    SELECT @user_id
END
SET NOCOUNT OFF
GO

IF OBJECT_ID('[dbo].[spInsertUserProfile]') IS NOT NULL 
    DROP PROCEDURE [dbo].[spInsertUserProfile]
GO
CREATE PROCEDURE [dbo].[spInsertUserProfile](
    @location_name nvarchar(256),
    @location_address1 nvarchar(128),
    @location_address2 nvarchar(128) = '',
    @location_city nvarchar(64),
    @location_state nvarchar(64),
    @location_zip nvarchar(32),
    @location_country nvarchar(64),
    @location_deleted bit = 0,

    @user_id int,
    @user_name_first nvarchar(64),
    @user_name_last nvarchar(64),

    @user_name_salutation nvarchar(32),
    @user_name_middle nvarchar(64) = '',
    @user_occupation nvarchar(128),
    @user_phone nvarchar(32),
    @user_phone_extension nvarchar(16) = ''
)
AS
SET NOCOUNT ON
BEGIN
    DECLARE @user_organization_location_id int

    IF EXISTS(SELECT * FROM locations WHERE location_name = @location_name AND location_address1 = @location_address1 AND location_zip = @location_zip)
    BEGIN
        UPDATE locations SET
            location_name = @location_name,
            location_address1 = @location_address1,
            location_address2 = @location_address2,
            location_city = @location_city,
            location_state = @location_state,
            location_zip = @location_zip,
            location_country = @location_country,
            location_deleted = @location_deleted
        WHERE location_name = @location_name AND location_address1 = @location_address1 AND location_zip = @location_zip
    END
    ELSE
    BEGIN
        INSERT INTO locations(
            location_name,
            location_address1,
            location_address2,
            location_city,
            location_state,
            location_zip,
            location_country,
            location_deleted
        )
        VALUES(
            @location_name,
            @location_address1,
            @location_address2,
            @location_city,
            @location_state,
            @location_zip,
            @location_country,
            @location_deleted
        )
    END
    SET @user_organization_location_id = IDENT_CURRENT('locations')
    
    UPDATE users SET
        user_name_first = @user_name_first,
        user_name_last = @user_name_last
    WHERE user_id = @user_id
    
    INSERT INTO user_profiles(
        user_id,
        user_name_salutation,
        user_name_middle,
        user_occupation,
        user_phone,
        user_phone_extension,
        user_organization_location_id
    )
    VALUES(
        @user_id,
        @user_name_salutation,
        @user_name_middle,
        @user_occupation,
        @user_phone,
        @user_phone_extension,
        @user_organization_location_id
    )
END
SET NOCOUNT OFF
GO

IF OBJECT_ID('[dbo].[spInsertContent]') IS NOT NULL
    DROP PROCEDURE [dbo].[spInsertContent]
GO
CREATE PROCEDURE [dbo].[spInsertContent](
    @content_type int,
    @content_parent_id int,
    @content_assignedto int = 0,
    @content_author_user_id int,
    @content_editor_user_id int,
    @content_title nvarchar(1024),
    @content_title_slug nvarchar(1024),
    @content_header nvarchar(2048) = '',
    @content_body nvarchar(max) = '',
    @content_footer nvarchar(2048) = '',
    @content_status int,
    @content_style_id int = 0,
    @content_template_id int = 0,
    @content_order int = 10,
    @content_order_type int = 1,
    @content_order_menu int = 10,
    @content_date_modified datetime,
    @content_date_published datetime = null,
    @content_date_created datetime,
    @content_deleted bit = 0
)
AS
SET NOCOUNT ON
BEGIN
    DECLARE @content_id int

    INSERT INTO contents(
        content_type,
        content_parent_id,
        content_assignedto,
        content_author_user_id,
        content_editor_user_id,
        content_title,
        content_title_slug,
        content_header,
        content_body,
        content_footer,
        content_status,
        content_style_id,
        content_template_id,
        content_order,
        content_order_type,
        content_order_menu,
        content_date_modified,
        content_date_published,
        content_date_created,
        content_deleted
    )
    VALUES(
        @content_type,
        @content_parent_id,
        @content_assignedto,
        @content_author_user_id,
        @content_editor_user_id,
        @content_title,
        @content_title_slug,
        @content_header,
        @content_body,
        @content_footer,
        @content_status,
        @content_style_id,
        @content_template_id,
        @content_order,
        @content_order_type,
        @content_order_menu,
        @content_date_modified,
        @content_date_published,
        @content_date_created,
        @content_deleted
    )
    SET @content_id = IDENT_CURRENT('contents')

    SELECT @content_id
END
SET NOCOUNT OFF
GO

IF OBJECT_ID('[dbo].[spInsertFile]') IS NOT NULL
    DROP PROCEDURE [dbo].[spInsertFile]
GO
CREATE PROCEDURE [dbo].[spInsertFile](
    @file_type int = 0,
    @file_uploader_user_id int,
    @file_name nvarchar(1024),
    @file_name_slug nvarchar(2048),
    @file_description nvarchar(2048) = '',
    @file_binary varbinary(max),
    @file_extension nvarchar(64),
    @file_size_bytes int,
    @file_count_download int = 0,
    @file_date_created datetime,
    @file_deleted bit = 0
)
AS
SET NOCOUNT ON
BEGIN
    DECLARE @file_id int

    INSERT INTO files(
        file_type,
        file_uploader_user_id,
        file_name,
        file_name_slug,
        file_description,
        file_binary,
        file_extension,
        file_size_bytes,
        file_count_download,
        file_date_created,
        file_deleted
    )
    VALUES(
        @file_type,
        @file_uploader_user_id,
        @file_name,
        @file_name_slug,
        @file_description,
        @file_binary,
        @file_extension,
        @file_size_bytes,
        @file_count_download,
        @file_date_created,
        @file_deleted
    )
    SET @file_id = IDENT_CURRENT('files')

    SELECT @file_id
END
SET NOCOUNT OFF
GO

/* SELECT
//-------------------------------------------------------------------*/
IF OBJECT_ID('[dbo].[spSelectUserProfile]') IS NOT NULL 
    DROP PROCEDURE [dbo].[spSelectUserProfile]
GO
CREATE PROCEDURE [dbo].[spSelectUserProfile](
    @user_id int
)
AS
SET NOCOUNT ON
BEGIN
    DECLARE @user_organization_location_id int

    SET @user_organization_location_id = (
        SELECT user_organization_location_id FROM user_profiles WHERE user_id = @user_id
    )

    SELECT
        user_name_alias,
        user_name_first,
        user_name_last,
        user_email,
        user_date_created,
        user_deleted,
        
        user_name_salutation,
        user_name_middle,
        user_occupation,
        user_phone,
        user_phone_extension,
        
        location_name,
        location_address1,
        location_address2,
        location_city,
        location_state,
        location_zip,
        location_country,
        location_deleted
    FROM users, user_profiles, locations
    WHERE user_profiles.user_id = @user_id
    AND users.user_id = user_profiles.user_id
    AND locations.location_id = @user_organization_location_id
END
SET NOCOUNT OFF
GO

IF OBJECT_ID('[dbo].[spSelectListCategories]') IS NOT NULL 
    DROP PROCEDURE [dbo].[spSelectListCategories]
GO
CREATE PROCEDURE [dbo].[spSelectListCategories](
    @list_id int = 0,
    @list_deleted bit = 0
)
AS
SET NOCOUNT ON
BEGIN
    IF(@list_id > 0)
    BEGIN
        SELECT *
        FROM lists
        WHERE list_type = 1
        AND list_id = @list_id
        AND list_deleted = @list_deleted
    END
    ELSE
    BEGIN
        SELECT *
        FROM lists
        WHERE list_type = 1
        AND list_deleted = @list_deleted
    END
END
SET NOCOUNT OFF
GO

IF OBJECT_ID('[dbo].[spSelectContentsRecent_Dynamic]') IS NOT NULL 
    DROP PROCEDURE [dbo].[spSelectContentsRecent_Dynamic]
GO
CREATE PROCEDURE [dbo].[spSelectContentsRecent_Dynamic](
    @content_assignedto int = -1000,
    @content_deleted bit = 0,
    @option__content_assignedto___session_user_id int = 0,
    @limit int = 6
)
AS
SET NOCOUNT ON
BEGIN
    /* Declaration for Dynamic SQL. */
    DECLARE @sql_statement nvarchar(4000)
    DECLARE @sql_parameters_definition nvarchar(2000)

    /* BEGIN: Statement Static */
    SET @sql_statement = ('
        SELECT TOP(@limit)
            content_id,
            content_title,
            content_title_slug,
            content_status,
            content_assignedto,
            content_author_user_id,
            content_editor_user_id,
            content_date_modified,
            content_date_published,
            content_date_created
        FROM
            contents
        WHERE
            content_deleted = @content_deleted
            AND content_type IN (1, 2, 3)
            AND content_status = 1
    ')
    /* END: Statement Static */

    /* BEGIN: Business Logic */  
    IF(@content_assignedto != -1000 AND @option__content_assignedto___session_user_id <= 0)
    BEGIN
        SET @sql_statement = @sql_statement + ('
            AND (content_assignedto >= @content_assignedto)
        ')
    END
    ELSE IF(@content_assignedto != -1000 AND @option__content_assignedto___session_user_id > 0)
    BEGIN
        SET @sql_statement = @sql_statement + ('
            AND (content_assignedto = @content_assignedto OR content_assignedto = @option__content_assignedto___session_user_id)
        ')
    END
    /* END: Business Logic */    
        
    /* BEGIN: Statement Static */
    SET @sql_statement = @sql_statement + ('
        ORDER BY
            content_date_modified DESC,
            content_date_created DESC
    ')
    /* END: Statement Static */
    
    /* BEGIN: Statement Parameters Definition */
    SET @sql_parameters_definition = ('
        @content_assignedto int,
        @content_deleted bit,
        @option__content_assignedto___session_user_id int,
        @limit int
    ')
    /* END: Statement Parameters Definition */
    
    /* BEGIN: Execution of Statement */
    EXECUTE sp_executesql @sql_statement, @sql_parameters_definition,
        @content_assignedto,
        @content_deleted,
        @option__content_assignedto___session_user_id,
        @limit
    /* END: Execution of Statement */
END
SET NOCOUNT OFF
GO

IF OBJECT_ID('[dbo].[spSelectContents_Dynamic]') IS NOT NULL 
    DROP PROCEDURE [dbo].[spSelectContents_Dynamic]
GO
CREATE PROCEDURE [dbo].[spSelectContents_Dynamic](
    @content_id int = 0,
    @content_type int = 0,
    @content_parent_id int = 0,
    @content_assignedto int = -1000,
    @content_title_slug nvarchar(1024) = '',
    @content_deleted bit = 0,
    @option__content_id___include_children bit = 0,
    @option__content_parent_id___include_parent bit = 1,
    @option__content_assignedto___session_user_id int = 0,
    @option_select int = 0
)
AS
SET NOCOUNT ON
BEGIN
    /* Declaration for Dynamic SQL. */
    DECLARE @sql_statement nvarchar(4000)
    DECLARE @sql_parameters_definition nvarchar(2000)

    /* BEGIN: Statement Static */
    SET @sql_statement = ('
        SELECT
    ')
    /* END: Statement Static */

    /* BEGIN: Business Logic */
    IF(@option_select = 1)
    BEGIN
        SET @sql_statement = @sql_statement + ('
            c1.content_id,
            c1.content_title,
            c1.content_status,
            c1.content_parent_id,
            c1.content_assignedto,
            c1.content_author_user_id,
            c1.content_editor_user_id,
            c1.content_order,
            c1.content_date_modified,
            c1.content_date_published,
            c1.content_date_created,
            c1.content_deleted,
            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,
            l3.list_name AS content_order_type_name
        ')
    END
    ELSE
    BEGIN
        SET @sql_statement = @sql_statement + ('
            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,
            l3.list_name AS content_order_type_name
        ')       
    END
    /* END: Business Logic */

    /* BEGIN: Statement Static */
    SET @sql_statement = @sql_statement + ('
        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
            LEFT OUTER JOIN lists AS l3 ON c1.content_order_type = l3.list_value AND l3.list_category_id =  4
        WHERE
            c1.content_deleted = @content_deleted
    ')
    /* END: Statement Static */

    /* BEGIN: Business Logic */
    IF(@content_id <= 0 AND @content_title_slug != '')
    BEGIN
        SET @sql_statement = @sql_statement + ('
            AND (LOWER(c1.content_title_slug) = LOWER(@content_title_slug))
        ')
    END
    ELSE IF(@content_id > 0 AND @option__content_id___include_children = 0)
    BEGIN
        SET @sql_statement = @sql_statement + ('
            AND (c1.content_id = @content_id)
        ')
    END
    ELSE IF(@content_id > 0 AND @option__content_id___include_children = 1)
    BEGIN
        SET @sql_statement = @sql_statement + ('
            AND (c1.content_id = @content_id OR c1.content_parent_id = @content_id)
        ')
    END
    
    IF(@content_type > 0)
    BEGIN
        SET @sql_statement = @sql_statement + ('
            AND (c1.content_type = @content_type)
        ')
    END
    
    IF(@content_assignedto != -1000 AND @option__content_assignedto___session_user_id <= 0)
    BEGIN
        SET @sql_statement = @sql_statement + ('
            AND (c1.content_assignedto >= @content_assignedto)
        ')
    END
    ELSE IF(@content_assignedto != -1000 AND @option__content_assignedto___session_user_id > 0)
    BEGIN
        SET @sql_statement = @sql_statement + ('
            AND (c1.content_assignedto = @content_assignedto OR c1.content_assignedto = @option__content_assignedto___session_user_id)
        ')
    END

    IF(@content_parent_id > 0 AND @option__content_parent_id___include_parent = 0)
    BEGIN
        SET @sql_statement = @sql_statement + ('
            AND (c1.content_parent_id = @content_parent_id)
        ')
    END
    ELSE IF(@content_parent_id > 0 AND @option__content_parent_id___include_parent = 1)
    BEGIN
        SET @sql_statement = @sql_statement + ('
            AND (c1.content_id = @content_parent_id OR c1.content_parent_id = @content_parent_id)
        ')
    END
    /* END: Business Logic */

    /* BEGIN: Statement Static */
    SET @sql_statement = @sql_statement + ('
        ORDER BY
            c1.content_order ASC,
            c1.content_date_created DESC
    ')
    /* END: Statement Static */
    
    /* BEGIN: Statement Parameters Definition */
    SET @sql_parameters_definition = ('
        @content_id int,
        @content_type int,
        @content_parent_id int,
        @content_assignedto int,
        @content_title_slug nvarchar(1024),
        @content_deleted bit,
        @option__content_id___include_children bit,
        @option__content_parent_id___include_parent bit,
        @option__content_assignedto___session_user_id int,
        @option_select int
    ')
    /* END: Statement Parameters Definition */
    
    /* BEGIN: Execution of Statement */
    EXECUTE sp_executesql @sql_statement, @sql_parameters_definition,
        @content_id,
        @content_type,
        @content_parent_id,
        @content_assignedto,
        @content_title_slug,
        @content_deleted,
        @option__content_id___include_children,
        @option__content_parent_id___include_parent,
        @option__content_assignedto___session_user_id,
        @option_select
    /* END: Execution of Statement */
END
SET NOCOUNT OFF
GO

IF OBJECT_ID('[dbo].[spSelectContentsBySearch]') IS NOT NULL 
    DROP PROCEDURE [dbo].[spSelectContentsBySearch]
GO
CREATE PROCEDURE [dbo].[spSelectContentsBySearch](
    @search nvarchar(1024)
)
AS
SET NOCOUNT ON
BEGIN
    SELECT *
    FROM contents
    WHERE
        ((LOWER(content_title) LIKE '%' + @search + ' %' OR LOWER(content_title) LIKE @search) OR (LOWER(content_title) LIKE @search + ' %' OR LOWER(content_title) LIKE '% ' + @search)
        OR ((LOWER(content_body) LIKE '%' + @search + ' %' OR LOWER(content_body) LIKE '% ' + @search + '%') OR LOWER(content_body) LIKE @search) OR (LOWER(content_body) LIKE @search + ' %' OR LOWER(content_body) LIKE '% ' + @search))
        AND content_type IN (1, 2, 3)
        AND content_status = 1
        AND content_deleted = 0
END
SET NOCOUNT OFF
GO

IF OBJECT_ID('[dbo].[spSelectContents_Hierarchy]') IS NOT NULL 
    DROP PROCEDURE [dbo].[spSelectContents_Hierarchy]
GO
CREATE PROCEDURE [dbo].[spSelectContents_Hierarchy]
AS
SET NOCOUNT ON
BEGIN
    CREATE TABLE #tmp_tbl_contents_nested(
        sort_id int IDENTITY (1,1),
        content_id int,
        content_key varchar(200),
        content_level int
    );

    EXEC spSelectContents_HierarchyChildren 0, 1, ''

    SELECT
        n.sort_id,
        c.content_id,
        c.content_parent_id,
        n.content_level,
        content_key = LEFT(n.content_key, 10),
        content_title = LEFT(SPACE((n.content_level - 1) * 2) + c.content_title, 40),
        c.content_title_slug,
        c.content_assignedto
    FROM
        #tmp_tbl_contents_nested AS n
        INNER JOIN vw_contents_hierarchy AS c ON n.content_id = c.content_id
    ORDER BY
        n.sort_id

    DROP TABLE #tmp_tbl_contents_nested
END
SET NOCOUNT OFF
GO

IF OBJECT_ID('[dbo].[spSelectContents_HierarchyChildren]') IS NOT NULL 
    DROP PROCEDURE [dbo].[spSelectContents_HierarchyChildren]
GO
CREATE PROCEDURE [dbo].[spSelectContents_HierarchyChildren](
    @content_parent_id int,
    @content_level int,
    @parent_content_key varchar(200)
)
AS
SET NOCOUNT ON
BEGIN
    DECLARE @next_level int, @counter int, @content_key varchar(200)
    
    SET @counter = 1
    
    /* Build a cursor to loop through all the records. */
    DECLARE cursor1 CURSOR LOCAL FOR
        SELECT content_id
        FROM vw_contents_hierarchy
        WHERE content_parent_id = @content_parent_id
        ORDER BY content_order_menu ASC

    OPEN cursor1
    
    FETCH NEXT FROM cursor1
    INTO @content_parent_id
    
    /* Recursive stored procedure loop limited 32 levels of nesting. */
    WHILE @@FETCH_STATUS = 0
    BEGIN
        /* Build and append to key. */
        IF @content_level = 1
            SET @content_key = CONVERT(varchar, @counter)
        ELSE 
            SET @content_key = @parent_content_key + '.' + convert(varchar, @counter)

        /* Insert a record to temporary table. */
        INSERT #tmp_tbl_contents_nested(
            content_id, content_key, content_level
        )
        VALUES (
            @content_parent_id, @content_key, @content_level
        )

        SET @next_level = @content_level + 1
        
        /* Process all children for this record. */
        EXEC spSelectContents_HierarchyChildren @content_parent_id, @next_level, @content_key
        
        SET @counter = @counter + 1
        
        /* Get the next record at this level. */
        FETCH NEXT FROM cursor1
        INTO @content_parent_id 
    END
    
    CLOSE cursor1
    DEALLOCATE cursor1
END
SET NOCOUNT OFF
GO

IF OBJECT_ID('[dbo].[spSelectFilesRecent]') IS NOT NULL 
    DROP PROCEDURE [dbo].[spSelectFilesRecent]
GO
CREATE PROCEDURE [dbo].[spSelectFilesRecent](
    @file_uploader_user_id int,
    @file_deleted bit = 0,
    @limit int = 6
)
AS
SET NOCOUNT ON
BEGIN
    SELECT TOP(@limit) *
    FROM
        files
    WHERE
        file_deleted = @file_deleted
        AND file_uploader_user_id = @file_uploader_user_id
    ORDER BY
        file_date_created DESC
END
SET NOCOUNT OFF
GO

/* UPDATE
//-------------------------------------------------------------------*/
IF OBJECT_ID('[dbo].[spUpdateUserProfile]') IS NOT NULL 
    DROP PROCEDURE [dbo].[spUpdateUserProfile]
GO
CREATE PROCEDURE [dbo].[spUpdateUserProfile](
    @user_id int,
    @user_name_first nvarchar(63),
    @user_name_last nvarchar(63),

    @user_name_salutation nvarchar(32),
    @user_name_middle nvarchar(63) = '',
    @user_occupation nvarchar(127),
    @user_phone nvarchar(31),

    @location_name nvarchar(255),
    @location_address1 nvarchar(127),
    @location_address2 nvarchar(127) = '',
    @location_city nvarchar(63),
    @location_state nvarchar(63),
    @location_zip nvarchar(31),
    @location_country nvarchar(63)
)
AS
SET NOCOUNT ON
BEGIN
    DECLARE @user_organization_location_id int

    UPDATE users SET
        user_name_first = @user_name_first,
        user_name_last = @user_name_last
    WHERE user_id = @user_id

    UPDATE user_profiles SET
        user_name_first = @user_name_first,
        user_name_middle = @user_name_middle,
        user_name_last = @user_name_last,
        user_occupation = @user_occupation,
        user_phone = @user_phone
    WHERE user_id = @user_id
    SET @user_organization_location_id = (
        SELECT user_organization_location_id FROM user_profiles WHERE user_id = @user_id
    )

    UPDATE locations SET
        location_name = @location_name,
        location_address1 = @location_address1,
        location_address2 = @location_address2,
        location_city = @location_city,
        location_state = @location_state,
        location_zip = @location_zip,
        location_country = @location_country
    WHERE location_id = @user_organization_location_id
END
SET NOCOUNT OFF
GO

IF OBJECT_ID('[dbo].[spUpdateContent]') IS NOT NULL
    DROP PROCEDURE [dbo].[spUpdateContent]
GO
CREATE PROCEDURE [dbo].[spUpdateContent](
    @content_id int,
    @content_type int,
    @content_parent_id int,
    @content_assignedto int = 0,
    @content_editor_user_id int,
    @content_title nvarchar(1024),
    @content_title_slug nvarchar(1024),
    @content_header nvarchar(2048) = '',
    @content_body nvarchar(max),
    @content_footer nvarchar(2048) = '',
    @content_status int,
    @content_style_id int = 0,
    @content_template_id int = 0,
    @content_order int = 10,
    @content_order_type int = 1,
    @content_order_menu int = 10,
    @content_date_modified datetime,
    @content_date_published datetime = null
)
AS
SET NOCOUNT ON
BEGIN
    UPDATE contents SET
        content_type = @content_type,
        content_parent_id = @content_parent_id,
        content_assignedto = @content_assignedto,
        content_editor_user_id = @content_editor_user_id,
        content_title = @content_title,
        content_title_slug = @content_title_slug,
        content_header = @content_header,
        content_body = @content_body,
        content_footer = @content_footer,
        content_status = @content_status,
        content_style_id = @content_style_id,
        content_template_id = @content_template_id,
        content_order = @content_order,
        content_order_type = @content_order_type,
        content_order_menu = @content_order_menu,
        content_date_modified = @content_date_modified,
        content_date_published = @content_date_published
    WHERE content_id = @content_id

    SELECT 1
END
SET NOCOUNT OFF
GO

IF OBJECT_ID('[dbo].[spUpdateContent_Ajax]') IS NOT NULL
    DROP PROCEDURE [dbo].[spUpdateContent_Ajax]
GO
CREATE PROCEDURE [dbo].[spUpdateContent_Ajax](
    @content_id int,
    @content_editor_user_id int,
    @content_title nvarchar(1024),
    @content_title_slug nvarchar(1024),
    @content_body nvarchar(max),
    @content_order int = 10,
    @content_date_modified datetime
)
AS
SET NOCOUNT ON
BEGIN
    UPDATE contents SET
        content_editor_user_id = @content_editor_user_id,
        content_title = @content_title,
        content_title_slug = @content_title_slug,
        content_body = @content_body,
        content_order = @content_order,
        content_date_modified = @content_date_modified
    WHERE content_id = @content_id

    SELECT 1
END
SET NOCOUNT OFF
GO

IF OBJECT_ID('[dbo].[spUpdateFile]') IS NOT NULL
    DROP PROCEDURE [dbo].[spUpdateFile]
GO
CREATE PROCEDURE [dbo].[spUpdateFile](
    @file_id int,
    @file_type int = 0,
    @file_uploader_user_id int,
    @file_name nvarchar(1024),
    @file_name_slug nvarchar(2048),
    @file_binary varbinary(max),
    @file_extension nvarchar(64),
    @file_size_bytes int,
    @file_date_created datetime
)
AS
SET NOCOUNT ON
BEGIN  
    UPDATE files SET
        file_type = @file_type,
        file_uploader_user_id = @file_uploader_user_id,
        file_name = @file_name,
        file_name_slug = @file_name_slug,
        file_binary = @file_binary,
        file_extension = @file_extension,
        file_size_bytes = @file_size_bytes,
        file_date_created = @file_date_created
    WHERE file_id = @file_id

    SELECT 1
END
SET NOCOUNT OFF
GO

/* DELETE
//-------------------------------------------------------------------*/
IF OBJECT_ID('[dbo].[spDeleteUser]') IS NOT NULL 
    DROP PROCEDURE [dbo].[spDeleteUser]
GO
CREATE PROCEDURE [dbo].[spDeleteUser](
    @user_id int
)
AS
SET NOCOUNT ON
BEGIN
    DELETE FROM locations
    WHERE location_id IN (
        SELECT user_organization_location_id FROM user_profiles WHERE user_id = @user_id
    )
    
    DELETE FROM user_settings WHERE setting_user_id = @user_id
    
    DELETE FROM user_profiles WHERE user_id = @user_id
    
    DELETE FROM users WHERE user_id = @user_id
END
SET NOCOUNT OFF
GO

IF OBJECT_ID('[dbo].[spDeleteUsers]') IS NOT NULL 
    DROP PROCEDURE [dbo].[spDeleteUsers]
GO
CREATE PROCEDURE [dbo].[spDeleteUsers]
AS
SET NOCOUNT ON
BEGIN
    DELETE FROM locations
    WHERE location_id IN (
        SELECT user_organization_location_id FROM user_profiles
    )
    
    DELETE FROM user_profiles
    
    DELETE FROM user_settings
    
    DELETE FROM users
    
    SET IDENTITY_INSERT users ON

    INSERT INTO users(
        user_id,
        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 (
        1,
        'diehard',
        'William',
        'Chang',
        'rCO9+p2VwqEQNvDGHCyUIg==',
        'william@babybluebox.com',
        '',
        1,
        GETDATE(),
        GETDATE()
    )

    SET IDENTITY_INSERT users OFF

    SET IDENTITY_INSERT user_settings ON
    
    INSERT INTO user_settings (
        setting_id,
        setting_user_id,
        setting_key,
        setting_value
    )
    VALUES (
        1,
        1,
        'role',
        '1'
    )
    
    SET IDENTITY_INSERT user_settings OFF
END
SET NOCOUNT OFF
GO