namespace AnalyticsDAL.Query.KPILIST
{
    public static class KPILISTQB
    {
        // MKPILIST/MKPILISTDETAIL carry no TENANTID column (same tenant-via-database scoping as
        // the rest of the MKPI family) and MKPILISTDETAIL has no STATUS column either -- only a
        // hard DELETE+reinsert is possible on update, same as MROLEKPIDETAIL. Live column names
        // also drop the invented "KPILIST"/"KPILISTDETAIL" prefix entirely, and the detail row's
        // display name column is KPINAME (denormalized at save time), not KPILISTDETAILNAME; its
        // period-link column is PERIODID, not GBPERIODDETAILID. Confirmed live via
        // INFORMATION_SCHEMA.COLUMNS.
        public const string GET_KPILIST = @"
            SELECT  kl.KPILISTID,
                    kl.KPILISTNAME,
                    kl.DESCRIPTION   AS KPILISTDescription,
                    kl.LISTTYPE      AS KPILISTListType,
                    kl.ROLEID,
                    kl.USERID,
                    kl.USERGROUPID,
                    kl.REMARKS       AS KPILISTRemarks,
                    kl.SORTORDER     AS KPILISTSortOrder,
                    kl.STATUS        AS KPILISTStatus,
                    kl.VERSION       AS KPILISTVersion,
                    kl.SOURCETYPE    AS KPILISTSourceType,
                    kl.CREATEDBYID   AS KPILISTCreatedById,
                    kl.CREATEDON     AS KPILISTCreatedOn,
                    kl.MODIFIEDBYID  AS KPILISTModifiedById,
                    kl.MODIFIEDON    AS KPILISTModifiedOn
            FROM    MKPILIST kl
            WHERE   kl.KPILISTID = @KPILISTId
            AND     kl.STATUS   <> 2";

        public const string GET_KPILIST_DETAILS = @"
            SELECT  kld.KPILISTDETAILID,
                    kld.SLNO           AS KPILISTDETAILSLNo,
                    kld.KPILISTID,
                    kld.KPIID,
                    kld.KPINAME        AS KPILISTDETAILName,
                    kld.PERIODFILTER   AS KPILISTDETAILPeriodFilter,
                    kld.PERIODTYPE     AS KPILISTDETAILPeriodType,
                    kld.PERIODID       AS GBPeriodDetailId,
                    kld.DIMOUTYPE      AS KPILISTDETAILDIMOuType,
                    kld.LEVELOUID      AS LEVELOUId,
                    kld.VALUEOUID      AS ValueOuId,
                    kld.DIM1TYPE       AS KPILISTDETAILDIM1Type,
                    kld.LEVEL1ID       AS LEVEL1Id,
                    kld.VALUE1ID       AS Value1Id,
                    kld.DIM2TYPE       AS KPILISTDETAILDIM2Type,
                    kld.LEVEL2ID       AS LEVEL2Id,
                    kld.VALUE2ID       AS Value2Id,
                    kld.DIM3TYPE       AS KPILISTDETAILDIM3Type,
                    kld.LEVEL3ID       AS LEVEL3Id,
                    kld.VALUE3ID       AS Value3Id,
                    kld.DIM4TYPE       AS KPILISTDETAILDIM4Type,
                    kld.LEVEL4ID       AS LEVEL4Id,
                    kld.VALUE4ID       AS Value4Id,
                    kld.DIM5TYPE       AS KPILISTDETAILDIM5Type,
                    kld.LEVEL5ID       AS LEVEL5Id,
                    kld.VALUE5ID       AS Value5Id
            FROM    MKPILISTDETAIL kld
            WHERE   kld.KPILISTID = @KPILISTId
            ORDER BY kld.SLNO";

        public const string GET_SELECT_LIST = @"
            SELECT  kl.KPILISTID AS Id,
                    kl.KPILISTNAME AS Name
            FROM    MKPILIST kl
            WHERE   kl.STATUS <> 2
            ORDER BY kl.SORTORDER, kl.KPILISTNAME";

        public const string CHECK_DUPLICATE_NAME = @"
            SELECT  COUNT(1)
            FROM    MKPILIST
            WHERE   KPILISTNAME = @KPILISTName
            AND     STATUS     <> 2
            AND     KPILISTID   <> @KPILISTId";

        // MKPILIST.KPILISTID is NOT an identity column (confirmed live via sys.columns.is_identity
        // — unlike MROLEKPI.ROLEKPIID, which is) — a real MAUTONUMBER row for EntityCode='KPILIST'
        // already exists live (previously unwired, same class of pre-seeded-but-never-consumed
        // scaffold as MAUTONUMBER's own BUDGET/BUDGETDATA rows found earlier this session), so the
        // DAL generates the id via AutoNumber.GetNumberAsync before this INSERT rather than relying
        // on SCOPE_IDENTITY().
        public const string SAVE_KPILIST = @"
            INSERT INTO MKPILIST
                (KPILISTID, KPILISTNAME, DESCRIPTION, LISTTYPE,
                 ROLEID, USERID, USERGROUPID, REMARKS,
                 SORTORDER, STATUS, VERSION, SOURCETYPE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@KPILISTId, @KPILISTName, @KPILISTDescription, @KPILISTListType,
                 @RoleId, @UserId, @UserGroupId, @KPILISTRemarks,
                 @KPILISTSortOrder, 1, 1, @KPILISTSourceType,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE())";

        public const string UPDATE_KPILIST = @"
            UPDATE MKPILIST
            SET    KPILISTNAME  = @KPILISTName,
                   DESCRIPTION  = @KPILISTDescription,
                   LISTTYPE     = @KPILISTListType,
                   ROLEID       = @RoleId,
                   USERID       = @UserId,
                   USERGROUPID  = @UserGroupId,
                   REMARKS      = @KPILISTRemarks,
                   SORTORDER    = @KPILISTSortOrder,
                   STATUS       = @KPILISTStatus,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE()
            WHERE  KPILISTID = @KPILISTId";

        public const string DELETE_KPILIST = @"
            UPDATE MKPILIST
            SET    STATUS       = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE()
            WHERE  KPILISTID = @KPILISTId";

        public const string DELETE_DETAIL_ROWS = @"
            DELETE FROM MKPILISTDETAIL
            WHERE  KPILISTID = @KPILISTId";

        // KPILISTDETAILID is NOT an identity column and had no MAUTONUMBER row at all (a gap this
        // pass fixes with a new 'KPILISTDETAIL' MAUTONUMBER seed row, matching KPI/KPILIST/ROLEKPI's
        // own pre-existing rows).
        public const string SAVE_DETAIL_ROW = @"
            INSERT INTO MKPILISTDETAIL
                (KPILISTDETAILID, SLNO, KPILISTID, KPIID, KPINAME,
                 PERIODFILTER, PERIODTYPE, PERIODID,
                 DIMOUTYPE, LEVELOUID, VALUEOUID,
                 DIM1TYPE, LEVEL1ID, VALUE1ID,
                 DIM2TYPE, LEVEL2ID, VALUE2ID,
                 DIM3TYPE, LEVEL3ID, VALUE3ID,
                 DIM4TYPE, LEVEL4ID, VALUE4ID,
                 DIM5TYPE, LEVEL5ID, VALUE5ID)
            VALUES
                (@KPILISTDETAILId, @KPILISTDETAILSLNo, @KPILISTId, @KPIId, @KPILISTDETAILName,
                 @KPILISTDETAILPeriodFilter, @KPILISTDETAILPeriodType, @GBPeriodDetailId,
                 @KPILISTDETAILDIMOuType, @LEVELOUId, @ValueOuId,
                 @KPILISTDETAILDIM1Type, @LEVEL1Id, @Value1Id,
                 @KPILISTDETAILDIM2Type, @LEVEL2Id, @Value2Id,
                 @KPILISTDETAILDIM3Type, @LEVEL3Id, @Value3Id,
                 @KPILISTDETAILDIM4Type, @LEVEL4Id, @Value4Id,
                 @KPILISTDETAILDIM5Type, @LEVEL5Id, @Value5Id)";
    }
}