namespace AnalyticsDAL.Query.KPIManual
{
    public static class KPIManualQB
    {
        // The physical table is TKPIMANUAL (a transaction table, T-prefix), not MKPIMANUAL --
        // confirmed live via INFORMATION_SCHEMA.TABLES; this DAL has never once run successfully
        // against real GB5DEMO. Column names also drop the invented "KPIMANUAL" prefix, its
        // period-link column is PERIODID (not GBPERIODDETAILID), and there is no TENANTID column
        // on either TKPIMANUAL or MKPI to join/filter on.
        public const string GET_KPIMANUAL = @"
            SELECT  m.KPIMANUALID,
                    m.SCENARIOID       AS KPIManualScenarioId,
                    m.PERIODID         AS GBPeriodDetailId,
                    m.KPIID,
                    k.KPINAME,
                    m.MEMBEROUID       AS KPIManualMemberOUId,
                    m.MEMBER1ID        AS KPIManualMember1Id,
                    m.MEMBER2ID        AS KPIManualMember2Id,
                    m.MEMBER3ID        AS KPIManualMember3Id,
                    m.MEMBER4ID        AS KPIManualMember4Id,
                    m.MEMBER5ID        AS KPIManualMember5Id,
                    m.DIMOUID          AS KPIManualDIMOUId,
                    m.DIM1ID           AS KPIManualDIM1Id,
                    m.DIM2ID           AS KPIManualDIM2Id,
                    m.DIM3ID           AS KPIManualDIM3Id,
                    m.DIM4ID           AS KPIManualDIM4Id,
                    m.DIM5ID           AS KPIManualDIM5Id,
                    m.ACTUAL           AS KPIManualActual,
                    m.TARGET           AS KPIManualTarget,
                    m.SORTORDER        AS KPIManualSortOrder,
                    m.STATUS           AS KPIManualStatus,
                    m.VERSION          AS KPIManualVersion,
                    m.SOURCETYPE       AS KPIManualSourceType,
                    m.CREATEDBYID      AS KPIManualCreatedById,
                    m.CREATEDON        AS KPIManualCreatedOn,
                    m.MODIFIEDBYID     AS KPIManualModifiedById,
                    m.MODIFIEDON       AS KPIManualModifiedOn
            FROM    TKPIMANUAL m
            INNER JOIN MKPI k ON k.KPIID = m.KPIID
            WHERE   m.KPIMANUALID = @KPIManualId
            AND     m.STATUS     <> 2";

        public const string GET_LIST_KPIMANUAL = @"
            SELECT  m.KPIMANUALID,
                    m.SCENARIOID   AS KPIManualScenarioId,
                    m.PERIODID     AS GBPeriodDetailId,
                    m.KPIID,
                    k.KPINAME,
                    m.MEMBEROUID   AS KPIManualMemberOUId,
                    m.MEMBER1ID    AS KPIManualMember1Id,
                    m.MEMBER2ID    AS KPIManualMember2Id,
                    m.MEMBER3ID    AS KPIManualMember3Id,
                    m.MEMBER4ID    AS KPIManualMember4Id,
                    m.MEMBER5ID    AS KPIManualMember5Id,
                    m.ACTUAL       AS KPIManualActual,
                    m.TARGET       AS KPIManualTarget,
                    m.SORTORDER    AS KPIManualSortOrder
            FROM    TKPIMANUAL m
            INNER JOIN MKPI k ON k.KPIID = m.KPIID
            WHERE   m.STATUS     <> 2
            AND     (@KPIId          = 0 OR m.KPIID    = @KPIId)
            AND     (@ScenarioId     = 0 OR m.SCENARIOID = @ScenarioId)
            AND     (@PeriodDetailId = 0 OR m.PERIODID   = @PeriodDetailId)
            ORDER BY k.KPINAME, m.SCENARIOID, m.PERIODID";

        public const string CHECK_DUPLICATE = @"
            SELECT  COUNT(1)
            FROM    TKPIMANUAL
            WHERE   KPIID       = @KPIId
            AND     SCENARIOID  = @ScenarioId
            AND     PERIODID    = @PeriodDetailId
            AND     MEMBEROUID  = @MemberOUId
            AND     MEMBER1ID   = @Member1Id
            AND     MEMBER2ID   = @Member2Id
            AND     MEMBER3ID   = @Member3Id
            AND     MEMBER4ID   = @Member4Id
            AND     MEMBER5ID   = @Member5Id
            AND     STATUS     <> 2
            AND     KPIMANUALID <> @KPIManualId";

        public const string SAVE_KPIMANUAL = @"
            INSERT INTO TKPIMANUAL
                (SCENARIOID, PERIODID, KPIID,
                 MEMBEROUID, MEMBER1ID, MEMBER2ID,
                 MEMBER3ID,  MEMBER4ID, MEMBER5ID,
                 DIMOUID,    DIM1ID,    DIM2ID,
                 DIM3ID,     DIM4ID,    DIM5ID,
                 ACTUAL, TARGET,
                 SORTORDER, STATUS, VERSION, SOURCETYPE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@ScenarioId, @PeriodDetailId, @KPIId,
                 @MemberOUId, @Member1Id, @Member2Id, @Member3Id, @Member4Id, @Member5Id,
                 @DIMOUId,    @DIM1Id,    @DIM2Id,    @DIM3Id,    @DIM4Id,    @DIM5Id,
                 @KPIManualActual, @KPIManualTarget,
                 @KPIManualSortOrder, 1, 1, @KPIManualSourceType,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE());
            SELECT SCOPE_IDENTITY();";

        public const string UPDATE_KPIMANUAL = @"
            UPDATE TKPIMANUAL
            SET    ACTUAL       = @KPIManualActual,
                   TARGET       = @KPIManualTarget,
                   SORTORDER    = @KPIManualSortOrder,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE()
            WHERE  KPIMANUALID = @KPIManualId";

        public const string DELETE_KPIMANUAL = @"
            UPDATE TKPIMANUAL
            SET    STATUS       = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE()
            WHERE  KPIMANUALID = @KPIManualId";
    }
}