namespace AccountsDAL.Query.AccountGroup
{
    public static class AccountGroupQB
    {
        public const string GET_ACCOUNTGROUP = @"
            SELECT
                AG.ACCOUNTGROUPID                   AS AccountGroupId,
                AG.ACCOUNTGROUPCODE                 AS AccountGroupCode,
                AG.ACCOUNTGROUPNAME                 AS AccountGroupName,
                AG.ACCOUNTGROUPSHORTNAME            AS AccountGroupShortName,
                AG.ACCOUNTGROUPNATURE               AS AccountGroupNature,
                CASE AG.ACCOUNTGROUPNATURE
                    WHEN 0 THEN 'Liability'
                    WHEN 1 THEN 'Asset'
                    WHEN 2 THEN 'Direct Income'
                    WHEN 3 THEN 'Direct Expense'
                    WHEN 4 THEN 'Indirect Income'
                    WHEN 5 THEN 'Indirect Expense'
                    ELSE 'Unknown'
                END                                 AS AccountGroupNatureName,
                AG.PARENTID                         AS ParentId,
                P.ACCOUNTGROUPCODE                  AS ParentCode,
                P.ACCOUNTGROUPNAME                  AS ParentName,
                AG.ACCOUNTSCHEDULEID                AS AccountGroupAccountScheduleId,
                S.ACCOUNTSCHEDULECODE               AS AccountGroupAccountScheduleCode,
                S.ACCOUNTSCHEDULENAME               AS AccountGroupAccountScheduleName,
                S.ACCOUNTSCHEDULENATURE             AS AccountGroupAccountScheduleNature,
                CASE S.ACCOUNTSCHEDULENATURE
                    WHEN 0 THEN 'Liability'
                    WHEN 1 THEN 'Asset'
                    WHEN 2 THEN 'Direct Income'
                    WHEN 3 THEN 'Direct Expense'
                    WHEN 4 THEN 'Indirect Income'
                    WHEN 5 THEN 'Indirect Expense'
                    ELSE 'Unknown'
                END                                 AS AccountGroupAccountScheduleNatureName,
                AG.OPPOSITEGROUPID                  AS OppositeGroupId,
                O.ACCOUNTGROUPCODE                  AS OppositeGroupCode,
                O.ACCOUNTGROUPNAME                  AS OppositeGroupName,
                AG.SORTORDER                        AS AccountGroupSortOrder,
                AG.STATUS                           AS AccountGroupStatus,
                AG.VERSION                          AS AccountGroupVersion,
                AG.SOURCETYPE                       AS AccountGroupSourceType,
                AG.CREATEDBYID                      AS AccountGroupCreatedById,
                CU.USERNAME                         AS AccountGroupCreatedByName,
                AG.CREATEDON                        AS AccountGroupCreatedOn,
                AG.MODIFIEDBYID                     AS AccountGroupModifiedById,
                MU.USERNAME                         AS AccountGroupModifiedByName,
                AG.MODIFIEDON                       AS AccountGroupModifiedOn
            FROM  MACCOUNTGROUP AG
            JOIN  MACCOUNTSCHEDULE  S  ON AG.ACCOUNTSCHEDULEID  = S.ACCOUNTSCHEDULEID
            LEFT JOIN MACCOUNTGROUP P  ON AG.PARENTID           = P.ACCOUNTGROUPID
            LEFT JOIN MACCOUNTGROUP O  ON AG.OPPOSITEGROUPID    = O.ACCOUNTGROUPID
            LEFT JOIN MUSER         CU ON AG.CREATEDBYID        = CU.USERID
            LEFT JOIN MUSER         MU ON AG.MODIFIEDBYID       = MU.USERID
            WHERE AG.ACCOUNTGROUPID = @AccountGroupId";

        // Full-DTO account group list (AccountGroupDTO, not AccountGroupPicklistDTO) — same
        // column/join set as GET_ACCOUNTGROUP but for every matching row instead of one,
        // criteria-filtered (search text + status) like AccountQB.GET_ACCOUNT_LIST, unpaged —
        // mirrors AccountList's shape exactly (see AccountQB.GET_ACCOUNT_LIST's own header note).
        public const string GET_ACCOUNTGROUP_LIST = @"
            SELECT
                AG.ACCOUNTGROUPID                   AS AccountGroupId,
                AG.ACCOUNTGROUPCODE                 AS AccountGroupCode,
                AG.ACCOUNTGROUPNAME                 AS AccountGroupName,
                AG.ACCOUNTGROUPSHORTNAME            AS AccountGroupShortName,
                AG.ACCOUNTGROUPNATURE               AS AccountGroupNature,
                CASE AG.ACCOUNTGROUPNATURE
                    WHEN 0 THEN 'Liability'
                    WHEN 1 THEN 'Asset'
                    WHEN 2 THEN 'Direct Income'
                    WHEN 3 THEN 'Direct Expense'
                    WHEN 4 THEN 'Indirect Income'
                    WHEN 5 THEN 'Indirect Expense'
                    ELSE 'Unknown'
                END                                 AS AccountGroupNatureName,
                AG.PARENTID                         AS ParentId,
                P.ACCOUNTGROUPCODE                  AS ParentCode,
                P.ACCOUNTGROUPNAME                  AS ParentName,
                AG.ACCOUNTSCHEDULEID                AS AccountGroupAccountScheduleId,
                S.ACCOUNTSCHEDULECODE               AS AccountGroupAccountScheduleCode,
                S.ACCOUNTSCHEDULENAME               AS AccountGroupAccountScheduleName,
                S.ACCOUNTSCHEDULENATURE             AS AccountGroupAccountScheduleNature,
                CASE S.ACCOUNTSCHEDULENATURE
                    WHEN 0 THEN 'Liability'
                    WHEN 1 THEN 'Asset'
                    WHEN 2 THEN 'Direct Income'
                    WHEN 3 THEN 'Direct Expense'
                    WHEN 4 THEN 'Indirect Income'
                    WHEN 5 THEN 'Indirect Expense'
                    ELSE 'Unknown'
                END                                 AS AccountGroupAccountScheduleNatureName,
                AG.OPPOSITEGROUPID                  AS OppositeGroupId,
                O.ACCOUNTGROUPCODE                  AS OppositeGroupCode,
                O.ACCOUNTGROUPNAME                  AS OppositeGroupName,
                AG.SORTORDER                        AS AccountGroupSortOrder,
                AG.STATUS                           AS AccountGroupStatus,
                AG.VERSION                          AS AccountGroupVersion,
                AG.SOURCETYPE                       AS AccountGroupSourceType,
                AG.CREATEDBYID                      AS AccountGroupCreatedById,
                CU.USERNAME                         AS AccountGroupCreatedByName,
                AG.CREATEDON                        AS AccountGroupCreatedOn,
                AG.MODIFIEDBYID                     AS AccountGroupModifiedById,
                MU.USERNAME                         AS AccountGroupModifiedByName,
                AG.MODIFIEDON                       AS AccountGroupModifiedOn
            FROM  MACCOUNTGROUP AG
            JOIN  MACCOUNTSCHEDULE  S  ON AG.ACCOUNTSCHEDULEID  = S.ACCOUNTSCHEDULEID
            LEFT JOIN MACCOUNTGROUP P  ON AG.PARENTID           = P.ACCOUNTGROUPID
            LEFT JOIN MACCOUNTGROUP O  ON AG.OPPOSITEGROUPID    = O.ACCOUNTGROUPID
            LEFT JOIN MUSER         CU ON AG.CREATEDBYID        = CU.USERID
            LEFT JOIN MUSER         MU ON AG.MODIFIEDBYID       = MU.USERID
            WHERE
                (@SearchText IS NULL OR @SearchText = ''
                 OR AG.ACCOUNTGROUPCODE LIKE '%' + @SearchText + '%'
                 OR AG.ACCOUNTGROUPNAME LIKE '%' + @SearchText + '%'
                 OR AG.ACCOUNTGROUPSHORTNAME LIKE '%' + @SearchText + '%')
                AND (@StatusEquals IS NULL OR AG.STATUS = @StatusEquals)
                AND (@StatusNotEquals IS NULL OR AG.STATUS <> @StatusNotEquals)
            ORDER BY AG.ACCOUNTGROUPCODE";

        public const string SAVE_ACCOUNTGROUP = @"
            INSERT INTO MACCOUNTGROUP
            (
                ACCOUNTGROUPID, ACCOUNTGROUPCODE, ACCOUNTGROUPNAME, ACCOUNTGROUPSHORTNAME,
                ACCOUNTGROUPNATURE, PARENTID, ACCOUNTSCHEDULEID, OPPOSITEGROUPID,
                SORTORDER, STATUS, VERSION, SOURCETYPE,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
            )
            VALUES
            (
                @AccountGroupId, @AccountGroupCode, @AccountGroupName, @AccountGroupShortName,
                @AccountGroupNature, @ParentId, @AccountGroupAccountScheduleId, @OppositeGroupId,
                @AccountGroupSortOrder, @AccountGroupStatus, @AccountGroupVersion, @AccountGroupSourceType,
                @AccountGroupCreatedById, @AccountGroupCreatedOn, @AccountGroupModifiedById, @AccountGroupModifiedOn
            )";

        public const string UPDATE_ACCOUNTGROUP = @"
            UPDATE MACCOUNTGROUP SET
                ACCOUNTGROUPCODE        = @AccountGroupCode,
                ACCOUNTGROUPNAME        = @AccountGroupName,
                ACCOUNTGROUPSHORTNAME   = @AccountGroupShortName,
                ACCOUNTGROUPNATURE      = @AccountGroupNature,
                PARENTID                = @ParentId,
                ACCOUNTSCHEDULEID       = @AccountGroupAccountScheduleId,
                OPPOSITEGROUPID         = @OppositeGroupId,
                SORTORDER               = @AccountGroupSortOrder,
                STATUS                  = @AccountGroupStatus,
                VERSION                 = @AccountGroupVersion,
                SOURCETYPE              = @AccountGroupSourceType,
                MODIFIEDBYID            = @AccountGroupModifiedById,
                MODIFIEDON              = @AccountGroupModifiedOn
            WHERE ACCOUNTGROUPID = @AccountGroupId";

        public const string DELETE_ACCOUNTGROUP = @"
            DELETE FROM MACCOUNTGROUP WHERE ACCOUNTGROUPID = @AccountGroupId";

        public const string GET_SELECTLIST_ACCOUNTGROUP = @"
            SELECT
                AG.ACCOUNTGROUPID           AS Id,
                AG.ACCOUNTGROUPCODE         AS Code,
                AG.ACCOUNTGROUPNAME         AS Name,
                AG.ACCOUNTGROUPSHORTNAME    AS ShortName,
                AG.ACCOUNTGROUPNATURE       AS Nature,
                AG.ACCOUNTSCHEDULEID        AS AccountScheduleId,
                S.ACCOUNTSCHEDULENAME       AS AccountScheduleName,
                S.ACCOUNTSCHEDULENATURE     AS AccountSchedulNature
            FROM  MACCOUNTGROUP    AG
            JOIN  MACCOUNTSCHEDULE S ON AG.ACCOUNTSCHEDULEID = S.ACCOUNTSCHEDULEID
            ORDER BY AG.ACCOUNTGROUPNAME";

        // Returns flat list including ParentId — used by BLL to build the tree
        public const string GET_ACCOUNTGROUP_FLAT_LIST = @"
            SELECT
                AG.ACCOUNTGROUPID           AS Id,
                AG.ACCOUNTGROUPCODE         AS Code,
                AG.ACCOUNTGROUPNAME         AS Name,
                AG.ACCOUNTGROUPSHORTNAME    AS ShortName,
                AG.ACCOUNTGROUPNATURE       AS Nature,
                AG.PARENTID                 AS ParentId,
                AG.ACCOUNTSCHEDULEID        AS AccountScheduleId,
                S.ACCOUNTSCHEDULENAME       AS AccountScheduleName,
                S.ACCOUNTSCHEDULENATURE     AS AccountSchedulNature
            FROM  MACCOUNTGROUP    AG
            JOIN  MACCOUNTSCHEDULE S ON AG.ACCOUNTSCHEDULEID = S.ACCOUNTSCHEDULEID
            ORDER BY AG.ACCOUNTGROUPNAME";
    }
}