using System;

namespace FrameworkDAL.Query.CommandPanel
{
    public static class CommandPanelQB
    {
        // Column aliases below match CommandPanelDTO's property names exactly (Dapper maps by
        // name, case-insensitive) — the previous version of this file aliased to names like
        // CommandCategory/CommandFormId/CommandSortOrder that don't exist on the DTO at all, so
        // every field silently deserialized as null/default. See migration
        // 20260816_ActionPanel_CommandPanel_Schema.sql for the MCOMMANDPANEL table this now targets.
        // Covering index: IX_MCOMMANDPANEL_STATUS (STATUS) INCLUDE (DISPLAYORDER).
        public const string GET_COMMAND_PANEL = @"SELECT
                                                    CP.COMMANDPANELID   AS CommandPanelId,
                                                    CP.CATEGORY         AS Category,
                                                    CP.KEYSTRING        AS KeyString,
                                                    CP.ISCTRLKEY        AS IsCtrlKey,
                                                    CP.ISALTKEY         AS IsAltKey,
                                                    CP.ISSHIFTKEY       AS IsShiftKey,
                                                    CP.ISSPECIALKEY     AS IsSpecialKey,
                                                    CP.DESCRIPTION      AS Description,
                                                    CP.ACTIONNAME       AS ActionName,
                                                    CP.ISENABLED        AS IsEnabled,
                                                    CP.DISPLAYNAME      AS DisplayName,
                                                    CP.DISPLAYORDER     AS DisplayOrder,
                                                    CP.VERSION          AS Version,
                                                    CP.CREATEDBYID      AS CreatedById,
                                                    CP.CREATEDON        AS CreatedOn,
                                                    CP.MODIFIEDBYID     AS ModifiedById,
                                                    CP.MODIFIEDON       AS ModifiedOn,
                                                    CP.TENANTID         AS TenantId,
                                                    CP.STATUS           AS Status,
                                                    CP.SOURCETYPE       AS SourceType
                                                FROM MCOMMANDPANEL CP
                                                WHERE CP.COMMANDPANELID = @CommandPanelId
                                                  AND CP.TENANTID = @TenantId;";


        public const string SAVE_COMMAND_PANEL = @"INSERT INTO MCOMMANDPANEL
                                                (
                                                    COMMANDPANELID,
                                                    CATEGORY,
                                                    KEYSTRING,
                                                    ISCTRLKEY,
                                                    ISALTKEY,
                                                    ISSHIFTKEY,
                                                    ISSPECIALKEY,
                                                    DESCRIPTION,
                                                    ACTIONNAME,
                                                    ISENABLED,
                                                    DISPLAYNAME,
                                                    DISPLAYORDER,
                                                    VERSION,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON,
                                                    TENANTID,
                                                    STATUS,
                                                    SOURCETYPE
                                                )
                                                VALUES
                                                (
                                                    @CommandPanelId,
                                                    @Category,
                                                    @KeyString,
                                                    @IsCtrlKey,
                                                    @IsAltKey,
                                                    @IsShiftKey,
                                                    @IsSpecialKey,
                                                    @Description,
                                                    @ActionName,
                                                    @IsEnabled,
                                                    @DisplayName,
                                                    @DisplayOrder,
                                                    @Version,
                                                    @CreatedById,
                                                    @CreatedOn,
                                                    @ModifiedById,
                                                    @ModifiedOn,
                                                    @TenantId,
                                                    @Status,
                                                    @SourceType
                                                );";


        public const string UPDATE_COMMAND_PANEL = @"UPDATE MCOMMANDPANEL
                                                    SET
                                                        CATEGORY = @Category,
                                                        KEYSTRING = @KeyString,
                                                        ISCTRLKEY = @IsCtrlKey,
                                                        ISALTKEY = @IsAltKey,
                                                        ISSHIFTKEY = @IsShiftKey,
                                                        ISSPECIALKEY = @IsSpecialKey,
                                                        DESCRIPTION = @Description,
                                                        ACTIONNAME = @ActionName,
                                                        ISENABLED = @IsEnabled,
                                                        DISPLAYNAME = @DisplayName,
                                                        DISPLAYORDER = @DisplayOrder,
                                                        STATUS = @Status,
                                                        MODIFIEDBYID = @ModifiedById,
                                                        MODIFIEDON = @ModifiedOn
                                                    WHERE
                                                        COMMANDPANELID = @CommandPanelId
                                                        AND TENANTID = @TenantId;";


        public const string GET_SELECTLIST_COMMAND_PANEL = @"WITH CommandPanel AS (
                                                            SELECT
                                                                CP.COMMANDPANELID AS Id,
                                                                CP.DISPLAYNAME AS Name,
                                                                ROW_NUMBER() OVER (ORDER BY CP.DISPLAYORDER, CP.COMMANDPANELID) AS RowNum
                                                            FROM MCOMMANDPANEL CP
                                                            WHERE CP.STATUS = 1 AND CP.TENANTID = @TenantId
                                                        )
                                                        SELECT Id, Name
                                                        FROM CommandPanel
                                                        WHERE
                                                            (@firstnumber = -1 AND @maxresult = -1)
                                                            OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SELECTLIST_COMMAND_PANEL_COUNT = @"SELECT COUNT(1)
                                                        FROM MCOMMANDPANEL CP
                                                        WHERE CP.STATUS = 1 AND CP.TENANTID = @TenantId;";


        public const string DELETE_COMMAND_PANEL = @"DELETE FROM MCOMMANDPANEL
                                                    WHERE COMMANDPANELID = @CommandPanelId
                                                      AND TENANTID = @TenantId;";

        // Full-row load for gbcommandpalette.component.ts — replaces the static gbcommandpalette.json
        // bundle. Unlike GET_SELECTLIST_COMMAND_PANEL (Id/Name picklist only), the palette needs every
        // column (KeyString/IsCtrlKey/.../Category) to reconstruct its generalCommands/defaultCommands/
        // reportCommands groups client-side.
        public const string GET_ALL_COMMAND_PANEL = @"SELECT
                                                    CP.COMMANDPANELID   AS CommandPanelId,
                                                    CP.CATEGORY         AS Category,
                                                    CP.KEYSTRING        AS KeyString,
                                                    CP.ISCTRLKEY        AS IsCtrlKey,
                                                    CP.ISALTKEY         AS IsAltKey,
                                                    CP.ISSHIFTKEY       AS IsShiftKey,
                                                    CP.ISSPECIALKEY     AS IsSpecialKey,
                                                    CP.DESCRIPTION      AS Description,
                                                    CP.ACTIONNAME       AS ActionName,
                                                    CP.ISENABLED        AS IsEnabled,
                                                    CP.DISPLAYNAME      AS DisplayName,
                                                    CP.DISPLAYORDER     AS DisplayOrder,
                                                    CP.VERSION          AS Version,
                                                    CP.CREATEDBYID      AS CreatedById,
                                                    CP.CREATEDON        AS CreatedOn,
                                                    CP.MODIFIEDBYID     AS ModifiedById,
                                                    CP.MODIFIEDON       AS ModifiedOn,
                                                    CP.TENANTID         AS TenantId,
                                                    CP.STATUS           AS Status,
                                                    CP.SOURCETYPE       AS SourceType
                                                FROM MCOMMANDPANEL CP
                                                WHERE CP.STATUS = 1 
                                                ORDER BY CP.CATEGORY, CP.DISPLAYORDER, CP.COMMANDPANELID;";
    }
}