using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AccountsDAL.Query.CostCategory
{
    public class CostCategoryQB
    {
        public const string GET_COSTCATEGORY = @"SELECT
                                                   ----CostCategoryDTO (Parent)----
                                                CC.COSTCATEGORYID              AS CostCategoryId,
                                                CC.COSTCATEGORYCODE            AS CostCategoryCode,
                                                CC.COSTCATEGORYNAME            AS CostCategoryName,
                                                CC.COSTCATEGORYSHORTNAME       AS CostCategoryShortName,
                                                CC.COSTCENTERTYPEID            AS CostCategoryCostCenterTypeId,
                                                CCT.COSTCENTERTYPECODE         AS CostCategoryCostCenterTypeCode,
                                                CCT.COSTCENTERTYPENAME         AS CostCategoryCostCenterTypeName,
                                                CC.SORTORDER                   AS CostCategorySortOrder,
                                                CC.STATUS                      AS CostCategoryStatus,
                                                CC.VERSION                     AS CostCategoryVersion,
                                                CC.CREATEDBYID                 AS CostCategoryCreatedById,
                                                CC.CREATEDON                   AS CostCategoryCreatedOn,
                                                UC.USERNAME                    AS CostCategoryCreatedByName,
                                                CC.MODIFIEDBYID                AS CostCategoryModifiedById,
                                                CC.MODIFIEDON                  AS CostCategoryModifiedOn,
                                                UM.USERNAME                    AS CostCategoryModifiedByName,

                                                           ----CostCategoryConditionDTO (Child)-----
                                                        CCD.COSTCATEGORYCONDITIONID      AS CostCategoryConditionId,
                                                        CCD.COSTCATEGORYID               AS CostCategoryId,
                                                        CC.COSTCATEGORYCODE              AS CostCategoryCode,
                                                        CC.COSTCATEGORYNAME              AS CostCategoryName,
                                                        CC.COSTCATEGORYSHORTNAME         AS CostCategoryShortName,
                                                        CCD.COSTCATEGORYCONDITIONTYPE    AS CostCategoryConditionType,
                                                        CCD.ACCOUNTGROUPID               AS AccountGroupId,
                                                        AG.ACCOUNTGROUPCODE              AS AccountGroupCode,
                                                        AG.ACCOUNTGROUPNAME              AS AccountGroupName,    
                                                        AG.ACCOUNTGROUPSHORTNAME         AS AccountGroupShortName,
                                                        CCD.NATURE                       AS CostCategoryConditionNature
                                            FROM MCOSTCATEGORY CC
                                            LEFT JOIN MCOSTCENTERTYPE CCT ON CC.COSTCENTERTYPEID = CCT.COSTCENTERTYPEID
                                            LEFT JOIN MUSER UC ON CC.CREATEDBYID = UC.USERID
                                            LEFT JOIN MUSER UM ON CC.MODIFIEDBYID = UM.USERID
                                            LEFT JOIN MCOSTCATEGORYCONDITION CCD ON CC.COSTCATEGORYID = CCD.COSTCATEGORYID
                                            LEFT JOIN MACCOUNTGROUP AG ON CCD.ACCOUNTGROUPID = AG.ACCOUNTGROUPID
                                            WHERE CC.COSTCATEGORYID = @costcategoryid;";

        // Full-DTO cost category list — same header columns/joins as GET_COSTCATEGORY's parent
        // (CostCategoryDTO) portion, but for every matching row instead of one and without the
        // MCOSTCATEGORYCONDITION child join (mirrors AccountQB.GET_ACCOUNT_LIST's shape: criteria
        // filtered, no FirstNumber/MaxResult paging, no condition rows in the result).
        public const string GET_COSTCATEGORY_LIST = @"SELECT
                                                CC.COSTCATEGORYID              AS CostCategoryId,
                                                CC.COSTCATEGORYCODE            AS CostCategoryCode,
                                                CC.COSTCATEGORYNAME            AS CostCategoryName,
                                                CC.COSTCATEGORYSHORTNAME       AS CostCategoryShortName,
                                                CC.COSTCENTERTYPEID            AS CostCategoryCostCenterTypeId,
                                                CCT.COSTCENTERTYPECODE         AS CostCategoryCostCenterTypeCode,
                                                CCT.COSTCENTERTYPENAME         AS CostCategoryCostCenterTypeName,
                                                CC.SORTORDER                   AS CostCategorySortOrder,
                                                CC.STATUS                      AS CostCategoryStatus,
                                                CC.VERSION                     AS CostCategoryVersion,
                                                CC.CREATEDBYID                 AS CostCategoryCreatedById,
                                                CC.CREATEDON                   AS CostCategoryCreatedOn,
                                                UC.USERNAME                    AS CostCategoryCreatedByName,
                                                CC.MODIFIEDBYID                AS CostCategoryModifiedById,
                                                CC.MODIFIEDON                  AS CostCategoryModifiedOn,
                                                UM.USERNAME                    AS CostCategoryModifiedByName
                                            FROM MCOSTCATEGORY CC
                                            LEFT JOIN MCOSTCENTERTYPE CCT ON CC.COSTCENTERTYPEID = CCT.COSTCENTERTYPEID
                                            LEFT JOIN MUSER UC ON CC.CREATEDBYID = UC.USERID
                                            LEFT JOIN MUSER UM ON CC.MODIFIEDBYID = UM.USERID
                                            WHERE (@SearchText IS NULL OR @SearchText = '' OR CC.COSTCATEGORYCODE LIKE '%' + @SearchText + '%')
                                                AND (@StatusEquals IS NULL OR CC.STATUS = @StatusEquals)
                                                AND (@StatusNotEquals IS NULL OR CC.STATUS <> @StatusNotEquals)
                                            ORDER BY CC.COSTCATEGORYCODE;";

        public const string SAVE_COSTCATEGORY = @"INSERT INTO MCOSTCATEGORY
                                                (
                                                    COSTCATEGORYID,
                                                    COSTCATEGORYCODE,
                                                    COSTCATEGORYNAME,
                                                    COSTCATEGORYSHORTNAME,
                                                    COSTCENTERTYPEID,
                                                    SORTORDER,
                                                    STATUS,
                                                    VERSION,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON
                                                )
                                                VALUES
                                                (
                                                    @CostCategoryId,
                                                    @CostCategoryCode,
                                                    @CostCategoryName,
                                                    @CostCategoryShortName,
                                                    @CostCategoryCostCenterTypeId,
                                                    @CostCategorySortOrder,
                                                    @CostCategoryStatus,
                                                    @CostCategoryVersion,
                                                    @CostCategoryCreatedById,
                                                    @CostCategoryCreatedOn,
                                                    @CostCategoryModifiedById,
                                                    @CostCategoryModifiedOn
                                                );";

        public const string SAVE_COSTCATEOGRY_CONDITION = @"INSERT INTO MCOSTCATEGORYCONDITION
                                                            (
                                                                COSTCATEGORYCONDITIONID,
                                                                COSTCATEGORYID,
                                                                COSTCATEGORYCONDITIONTYPE,
                                                                ACCOUNTGROUPID,
                                                                NATURE
                                                            )
                                                            VALUES
                                                            (
                                                                @CostCategoryConditionId,
                                                                @CostCategoryId,
                                                                @CostCategoryConditionType,
                                                                @AccountGroupId,
                                                                @CostCategoryConditionNature
                                                            );";

        public const string UPDATE_COSTCATEGORY = @"UPDATE MCOSTCATEGORY
                                                SET
                                                    COSTCATEGORYCODE          = @CostCategoryCode,
                                                    COSTCATEGORYNAME          = @CostCategoryName,
                                                    COSTCATEGORYSHORTNAME     = @CostCategoryShortName,
                                                    COSTCENTERTYPEID          = @CostCategoryCostCenterTypeId,
                                                    SORTORDER                 = @CostCategorySortOrder,
                                                    STATUS                    = @CostCategoryStatus,
                                                    VERSION                   = @CostCategoryVersion,
                                                    MODIFIEDBYID              = @CostCategoryModifiedById,
                                                    MODIFIEDON                = @CostCategoryModifiedOn
                                                WHERE COSTCATEGORYID = @CostCategoryId;";

        public const string UPDATE_COSTCATEGORY_CONDITION = @"UPDATE MCOSTCATEGORYCONDITION
                                                            SET
                                                                COSTCATEGORYID            = @CostCategoryId,
                                                                COSTCATEGORYCONDITIONTYPE = @CostCategoryConditionType,
                                                                ACCOUNTGROUPID            = @AccountGroupId,
                                                                NATURE                    = @CostCategoryConditionNature
                                                            WHERE COSTCATEGORYCONDITIONID = @CostCategoryConditionId;";

        public const string DELETE_COSTCATEGORY = @"DELETE FROM MCOSTCATEGORY
                                                    WHERE COSTCATEGORYID = @costcategoryid;";

        public const string DELETE_COSTCATEGORY_CONDITION = @"DELETE FROM MCOSTCATEGORYCONDITION
                                                                WHERE COSTCATEGORYID = @costcategoryid;";

        public const string GET_SELECTLIST_COSTCATEGORY = @"
                SELECT
                    COSTCATEGORYID AS Id,
                    COSTCATEGORYCODE AS Code,
                    COSTCATEGORYNAME AS Name
                FROM MCOSTCATEGORY
                ;";        

        public const string GET_COSTCATEGORY_COUNT = @"SELECT COUNT(1)
                                                    FROM dbo.MCOSTCATEGORY
                                                    WHERE
                                                        (
                                                            @CostCategoryId <> -1 AND COSTCATEGORYID = @CostCategoryId
                                                        )
                                                        AND COSTCATEGORYID <> @CostCategoryId";
    }
}