namespace AnalyticsDAL.Query.KPI
{
    public static class KPIQB
    {
        // MKPI/MKPISTATUS carry no TENANTID column -- this data is scoped by the tenant's own
        // database (DatabaseName), not a shared-database TENANTID filter. Confirmed live via
        // INFORMATION_SCHEMA.COLUMNS; the prior SQL referenced a TENANTID column that never
        // existed on either table, on top of an invented "KPI"/"KPISTATUS" column-name prefix
        // the live schema never used (plus a KPITARGETVALUEEXPESSION typo) -- every one of these
        // queries would throw "Invalid column name" the instant it ran.
        // Index: PK_MKPI_KPIID
        public const string GET_KPI = @"
            SELECT  k.KPIID                     AS KPIId,
                    k.KPINAME                   AS KPIName,
                    k.DESCRIPTION                AS KPIDescription,
                    k.SECTION                    AS KPISection,
                    k.MODULEID                  AS ModuleId,
                    k.ANALYSISID                AS AnalysisId,
                    k.ANALYSISQUERYID           AS AnalysisQueryId,
                    k.MEASURETYPE                AS KPIMeasureType,
                    k.DESIREDDIRECTION           AS KPIDesireDirection,
                    k.ANALYSISLINKTYPE          AS KPIAnalysisLinkType,
                    k.ACTUALTYPE                 AS KPIActualType,
                    k.ACTUALVALUEEXPRESSION      AS KPIActualValueExpression,
                    k.ACTUALAGGREGATION          AS KPIActualAggregation,
                    k.TARGETTYPE                 AS KPITargetType,
                    k.TARGETVALUEEXPRESSION      AS KPITargetValueExpession,
                    k.TARGETAGGREGATION          AS KPITargetAggregation,
                    k.CALCULATIONFREQUENCY       AS KPICalculationFrequency,
                    k.OWNERID                   AS OwnerId,
                    k.DATAFORMAT                 AS KPIDataFormat,
                    k.AGGREGATION                AS KPIAggregation,
                    k.SECURITYLEVEL              AS KPISecurityLevel,
                    k.LEVELPERIOD                AS KPILevelPeriod,
                    k.STATUSTYPE                 AS KPIStatusType,
                    k.DIMOUID                   AS DIMOuId,
                    k.DIM1ID                    AS DIM1Id,
                    k.DIM2ID                    AS DIM2Id,
                    k.DIM3ID                    AS DIM3Id,
                    k.DIM4ID                    AS DIM4Id,
                    k.DIM5ID                    AS DIM5Id,
                    k.SORTORDER                 AS KPISortOrder,
                    k.STATUS                    AS KPIStatus,
                    k.VERSION                   AS KPIVersion,
                    k.SOURCETYPE                AS KPISourceType,
                    k.CREATEDBYID               AS KPICreatedById,
                    k.CREATEDON                 AS KPICreatedOn,
                    k.MODIFIEDBYID              AS KPIModifiedById,
                    k.MODIFIEDON                AS KPIModifiedOn
            FROM    MKPI k
            WHERE   k.KPIID    = @KPIId
            AND     k.STATUS  <> 2";

        // Index: IX_MKPISTATUS_KPIID
        public const string GET_KPI_STATUS = @"
            SELECT  s.KPISTATUSID       AS KPISTATUSId,
                    s.SLNO               AS KPISTATUSSLNo,
                    s.VALUEFROM          AS KPISTATUSValueFrom,
                    s.VALUETO            AS KPISTATUSValueTo,
                    s.SCORE              AS KPISTATUSScore,
                    s.COLOR              AS KPISTATUSColor,
                    s.KPIID             AS KPIId,
                    s.STATUS            AS KPISTATUSStatus
            FROM    MKPISTATUS s
            WHERE   s.KPIID    = @KPIId
            AND     s.STATUS  <> 2
            ORDER BY s.SLNO";

        // Index: IX_MKPI_STATUS_KPINAME
        public const string GET_SELECT_LIST = @"
            SELECT  k.KPIID             AS Id,
                    k.KPINAME           AS Name,
                    k.SECTION           AS Section,
                    k.DESCRIPTION       AS Description,
                    k.ACTUALTYPE        AS ActualType,
                    k.TARGETTYPE        AS TargetType
            FROM    MKPI k
            WHERE   k.STATUS  <> 2
            ORDER BY k.SORTORDER, k.KPINAME";

        // Paged KPI list filterable by MEASURETYPE (the "Type" query param on the legacy
        // GB4 KPI.svc/KPIList endpoint). NOTE: this is a straightforward paged master-record
        // list, not a port of GB4's KPIList — that legacy endpoint is actually a dynamic
        // pivot/temp-table KPI *report* engine (KPIBLL.GetKKPIList, driven by a saved MKPILIST
        // definition across TKPIVALUE + up to 5 hierarchy dimensions), which has no equivalent
        // in this codebase's Dapper/QB conventions. Scoped down deliberately per product decision.
        // Index: IX_MKPI_STATUS_MEASURETYPE
        public const string GET_KPI_LIST_PAGED = @"
            SELECT  k.KPIID       AS Id,
                    k.KPINAME     AS Name,
                    k.SECTION     AS Section,
                    k.DESCRIPTION AS Description,
                    k.MEASURETYPE AS MeasureType,
                    k.ACTUALTYPE  AS ActualType,
                    k.TARGETTYPE  AS TargetType,
                    k.STATUSTYPE  AS StatusType,
                    k.SORTORDER   AS SortOrder
            FROM    MKPI k
            WHERE   k.STATUS  <> 2
            AND     (@MeasureType IS NULL OR k.MEASURETYPE = @MeasureType)
            ORDER BY k.SORTORDER, k.KPINAME
            OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string CHECK_DUPLICATE_NAME = @"
            SELECT COUNT(1)
            FROM   MKPI
            WHERE  KPINAME  = @KPIName
            AND    STATUS  <> 2
            AND    KPIID   <> @KPIId";

        public const string SAVE_KPI = @"
            INSERT INTO MKPI
                (KPIID, KPINAME, DESCRIPTION, SECTION,
                 MODULEID, ANALYSISID, ANALYSISQUERYID,
                 MEASURETYPE, DESIREDDIRECTION, ANALYSISLINKTYPE,
                 ACTUALTYPE, ACTUALVALUEEXPRESSION, ACTUALAGGREGATION,
                 TARGETTYPE, TARGETVALUEEXPRESSION, TARGETAGGREGATION,
                 CALCULATIONFREQUENCY, OWNERID,
                 DATAFORMAT, AGGREGATION, SECURITYLEVEL, LEVELPERIOD,
                 DIMOUID, DIM1ID, DIM2ID, DIM3ID, DIM4ID, DIM5ID,
                 VERSION, STATUS, SORTORDER, SOURCETYPE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@KPIId, @KPIName, @KPIDescription, @KPISection,
                 @ModuleId, @AnalysisId, @AnalysisQueryId,
                 @KPIMeasureType, @KPIDesireDirection, @KPIAnalysisLinkType,
                 @KPIActualType, @KPIActualValueExpression, @KPIActualAggregation,
                 @KPITargetType, @KPITargetValueExpession, @KPITargetAggregation,
                 @KPICalculationFrequency, @OwnerId,
                 @KPIDataFormat, @KPIAggregation, @KPISecurityLevel, @KPILevelPeriod,
                 @DIMOuId, @DIM1Id, @DIM2Id, @DIM3Id, @DIM4Id, @DIM5Id,
                 0, 1, @KPISortOrder, @KPISourceType,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE())";

        public const string UPDATE_KPI = @"
            UPDATE MKPI
            SET    KPINAME             = @KPIName,
                   DESCRIPTION         = @KPIDescription,
                   SECTION             = @KPISection,
                   MODULEID            = @ModuleId,
                   ANALYSISID          = @AnalysisId,
                   ANALYSISQUERYID     = @AnalysisQueryId,
                   MEASURETYPE         = @KPIMeasureType,
                   DESIREDDIRECTION    = @KPIDesireDirection,
                   ANALYSISLINKTYPE    = @KPIAnalysisLinkType,
                   ACTUALTYPE          = @KPIActualType,
                   ACTUALVALUEEXPRESSION = @KPIActualValueExpression,
                   ACTUALAGGREGATION   = @KPIActualAggregation,
                   TARGETTYPE          = @KPITargetType,
                   TARGETVALUEEXPRESSION = @KPITargetValueExpession,
                   TARGETAGGREGATION   = @KPITargetAggregation,
                   CALCULATIONFREQUENCY = @KPICalculationFrequency,
                   OWNERID             = @OwnerId,
                   DATAFORMAT          = @KPIDataFormat,
                   AGGREGATION         = @KPIAggregation,
                   SECURITYLEVEL       = @KPISecurityLevel,
                   LEVELPERIOD         = @KPILevelPeriod,
                   DIMOUID             = @DIMOuId,
                   DIM1ID              = @DIM1Id,
                   DIM2ID              = @DIM2Id,
                   DIM3ID              = @DIM3Id,
                   DIM4ID              = @DIM4Id,
                   DIM5ID              = @DIM5Id,
                   SORTORDER           = @KPISortOrder,
                   STATUS              = @KPIStatus,
                   VERSION             = VERSION + 1,
                   MODIFIEDBYID        = @ModifiedById,
                   MODIFIEDON          = GETDATE()
            WHERE  KPIID    = @KPIId";

        public const string DELETE_KPI = @"
            UPDATE MKPI
            SET    STATUS      = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE()
            WHERE  KPIID    = @KPIId";

        // Delete all status rows before re-inserting on update
        public const string DELETE_STATUS_ROWS = @"
            DELETE FROM MKPISTATUS
            WHERE  KPIID    = @KPIId";

        public const string SAVE_STATUS_ROW = @"
            INSERT INTO MKPISTATUS
                (KPISTATUSID, SLNO, VALUEFROM, VALUETO,
                 SCORE, COLOR, KPIID, STATUS)
            VALUES
                (@KPISTATUSId, @KPISTATUSSLNo, @KPISTATUSValueFrom, @KPISTATUSValueTo,
                 @KPISTATUSScore, @KPISTATUSColor, @KPIId, 1)";
    }
}