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

namespace AccountsDAL.Query.AccountSchedule
{
    public class AccountScheduleQB
    {
        public const string GET_ACCOUNTSCHEDULE = @"SELECT
                                                    A.ACCOUNTSCHEDULEID            AS AccountScheduleId,
                                                    A.ACCOUNTSCHEDULECODE          AS AccountScheduleCode,
                                                    A.ACCOUNTSCHEDULENAME          AS AccountScheduleName,
                                                    A.ACCOUNTSCHEDULESHORTNAME     AS AccountScheduleShortName,
                                                    A.ACCOUNTSCHEDULENATURE        AS AccountScheduleNature,
                                                CASE A.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 AccountScheduleNatureName,
                                                    A.PARENTID                     AS ParentId,
                                                    P.ACCOUNTSCHEDULECODE          AS ParentCode,
                                                    P.ACCOUNTSCHEDULENAME          AS ParentName,
                                                    A.SORTORDER                    AS AccountScheduleSortOrder,
                                                    A.STATUS                       AS AccountScheduleStatus,
                                                    A.VERSION                      AS AccountScheduleVersion,
                                                    A.SOURCETYPE                   AS AccountScheduleSourceType,
                                                    A.CREATEDBYID                  AS AccountScheduleCreatedById,
                                                    CU.USERNAME                    AS AccountScheduleCreatedByName,
                                                    A.CREATEDON                    AS AccountScheduleCreatedOn,
                                                    A.MODIFIEDBYID                 AS AccountScheduleModifiedById,
                                                    MU.USERNAME                    AS AccountScheduleModifiedByName,
                                                    A.MODIFIEDON                   AS AccountScheduleModifiedOn
                                                FROM MACCOUNTSCHEDULE A
                                                LEFT JOIN MACCOUNTSCHEDULE P ON A.PARENTID = P.ACCOUNTSCHEDULEID
                                                LEFT JOIN MUSER CU ON A.CREATEDBYID = CU.USERID
                                                LEFT JOIN MUSER MU ON A.MODIFIEDBYID = MU.USERID
                                                WHERE A.ACCOUNTSCHEDULEID = @accountscheduleid;";

        // Full-DTO account schedule list (AccountScheduleDTO, not AccountSchedulePicklistDTO) —
        // same column/join set as GET_ACCOUNTSCHEDULE but for every matching row instead of one,
        // criteria-filtered (search text + status) like AccountQB.GET_ACCOUNT_LIST, unpaged —
        // mirrors AccountList/AccountGroupList's shape exactly.
        public const string GET_ACCOUNTSCHEDULE_LIST = @"SELECT
                                                    A.ACCOUNTSCHEDULEID            AS AccountScheduleId,
                                                    A.ACCOUNTSCHEDULECODE          AS AccountScheduleCode,
                                                    A.ACCOUNTSCHEDULENAME          AS AccountScheduleName,
                                                    A.ACCOUNTSCHEDULESHORTNAME     AS AccountScheduleShortName,
                                                    A.ACCOUNTSCHEDULENATURE        AS AccountScheduleNature,
                                                CASE A.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 AccountScheduleNatureName,
                                                    A.PARENTID                     AS ParentId,
                                                    P.ACCOUNTSCHEDULECODE          AS ParentCode,
                                                    P.ACCOUNTSCHEDULENAME          AS ParentName,
                                                    A.SORTORDER                    AS AccountScheduleSortOrder,
                                                    A.STATUS                       AS AccountScheduleStatus,
                                                    A.VERSION                      AS AccountScheduleVersion,
                                                    A.SOURCETYPE                   AS AccountScheduleSourceType,
                                                    A.CREATEDBYID                  AS AccountScheduleCreatedById,
                                                    CU.USERNAME                    AS AccountScheduleCreatedByName,
                                                    A.CREATEDON                    AS AccountScheduleCreatedOn,
                                                    A.MODIFIEDBYID                 AS AccountScheduleModifiedById,
                                                    MU.USERNAME                    AS AccountScheduleModifiedByName,
                                                    A.MODIFIEDON                   AS AccountScheduleModifiedOn
                                                FROM MACCOUNTSCHEDULE A
                                                LEFT JOIN MACCOUNTSCHEDULE P ON A.PARENTID = P.ACCOUNTSCHEDULEID
                                                LEFT JOIN MUSER CU ON A.CREATEDBYID = CU.USERID
                                                LEFT JOIN MUSER MU ON A.MODIFIEDBYID = MU.USERID
                                                WHERE
                                                    (@SearchText IS NULL OR @SearchText = ''
                                                     OR A.ACCOUNTSCHEDULECODE LIKE '%' + @SearchText + '%'
                                                     OR A.ACCOUNTSCHEDULENAME LIKE '%' + @SearchText + '%'
                                                     OR A.ACCOUNTSCHEDULESHORTNAME LIKE '%' + @SearchText + '%')
                                                    AND (@StatusEquals IS NULL OR A.STATUS = @StatusEquals)
                                                    AND (@StatusNotEquals IS NULL OR A.STATUS <> @StatusNotEquals)
                                                ORDER BY A.ACCOUNTSCHEDULECODE;";

        public const string SAVE_ACCOUNTSCHEDULE = @"INSERT INTO MACCOUNTSCHEDULE
                                                    (
                                                        ACCOUNTSCHEDULEID,
                                                        ACCOUNTSCHEDULECODE,
                                                        ACCOUNTSCHEDULENAME,
                                                        ACCOUNTSCHEDULESHORTNAME,
                                                        ACCOUNTSCHEDULENATURE,
                                                        PARENTID,
                                                        SORTORDER,
                                                        STATUS,
                                                        VERSION,
                                                        CREATEDBYID,
                                                        CREATEDON,
                                                        MODIFIEDBYID,
                                                        MODIFIEDON,
                                                        SOURCETYPE
                                                    )
                                                    VALUES
                                                    (
                                                        @AccountScheduleId,
                                                        @AccountScheduleCode,
                                                        @AccountScheduleName,
                                                        @AccountScheduleShortName,
                                                        @AccountScheduleNature,
                                                        @ParentId,
                                                        @AccountScheduleSortOrder,
                                                        @AccountScheduleStatus,
                                                        @AccountScheduleVersion,
                                                        @AccountScheduleCreatedById,
                                                        @AccountScheduleCreatedOn,
                                                        @AccountScheduleModifiedById,
                                                        @AccountScheduleModifiedOn,
                                                        @AccountScheduleSourceType
                                                    );";

        public const string UPDATE_ACCOUNTSCHEDULE = @"UPDATE MACCOUNTSCHEDULE
                                                        SET
                                                            ACCOUNTSCHEDULECODE       = @AccountScheduleCode,
                                                            ACCOUNTSCHEDULENAME       = @AccountScheduleName,
                                                            ACCOUNTSCHEDULESHORTNAME  = @AccountScheduleShortName,
                                                            ACCOUNTSCHEDULENATURE     = @AccountScheduleNature,
                                                            PARENTID                  = @ParentId,
                                                            SORTORDER                 = @AccountScheduleSortOrder,
                                                            STATUS                    = @AccountScheduleStatus,
                                                            VERSION                   = @AccountScheduleVersion,
                                                            MODIFIEDBYID              = @AccountScheduleModifiedById,
                                                            MODIFIEDON                = @AccountScheduleModifiedOn,
                                                            SOURCETYPE                = @AccountScheduleSourceType
                                                        WHERE
                                                            ACCOUNTSCHEDULEID = @AccountScheduleId;";

        public const string DELETE_ACCOUNTSCHEDULE = @"DELETE FROM MACCOUNTSCHEDULE
                                                        WHERE ACCOUNTSCHEDULEID = @accountscheduleid;";

        public const string GET_SELECTLIST_ACCOUNTSCHEDULE = @"SELECT
                                                                ACCOUNTSCHEDULEID           AS Id,
                                                                ACCOUNTSCHEDULECODE         AS Code,
                                                                ACCOUNTSCHEDULENAME         AS Name,
                                                                ACCOUNTSCHEDULESHORTNAME    AS ShortName
                                                                FROM MACCOUNTSCHEDULE;";

        // Returns flat list including ParentId/SortOrder/Nature — used by AccountTreeBLL to build
        // the Account Tree navigator (Type -> Schedule -> Group -> Account). Nature
        // (ACCOUNTSCHEDULENATURE) is required here — not just ParentId/SortOrder — because the BLL
        // maps a root schedule (PARENTID=-1) into one of the 4 synthetic Type buckets via Nature.
        // Mirrors AccountGroupQB.GET_ACCOUNTGROUP_FLAT_LIST's aliasing convention.
        public const string GET_ACCOUNTSCHEDULE_FLAT_LIST = @"SELECT
                                                                A.ACCOUNTSCHEDULEID           AS Id,
                                                                A.ACCOUNTSCHEDULECODE         AS Code,
                                                                A.ACCOUNTSCHEDULENAME         AS Name,
                                                                A.PARENTID                    AS ParentId,
                                                                A.SORTORDER                   AS SortOrder,
                                                                A.ACCOUNTSCHEDULENATURE       AS Nature
                                                                FROM MACCOUNTSCHEDULE A
                                                                ORDER BY A.SORTORDER, A.ACCOUNTSCHEDULENAME;";
    }
}