namespace AnalyticsDAL.Query.RoleKPI
{
    public static class RoleKPIQB
    {
        // MROLEKPI carries no TENANTID column (same tenant-via-database scoping as the rest of
        // the MKPI family). MROLEKPIDETAIL is a plain 3-column assignment table
        // (ROLEKPIDETAILID, ROLEKPIID, KPIID) -- no STATUS, no SELECTED, no TENANTID -- so
        // membership itself (a row exists) is the only signal; there is no per-row "selected"
        // flag to persist and no soft-delete, only a hard DELETE+reinsert on update. Confirmed
        // live via INFORMATION_SCHEMA.COLUMNS.
        public const string GET_ROLEKPI = @"
            SELECT  rk.ROLEKPIID,
                    rk.ROLEID,
                    rk.SORTORDER  AS ROLEKPISortOrder,
                    rk.STATUS     AS ROLEKPIStatus,
                    rk.VERSION    AS ROLEKPIVersion,
                    rk.SOURCETYPE AS ROLEKPISourceType,
                    rk.CREATEDBYID  AS ROLEKPICreatedById,
                    rk.CREATEDON    AS ROLEKPICreatedOn,
                    rk.MODIFIEDBYID AS ROLEKPIModifiedById,
                    rk.MODIFIEDON   AS ROLEKPIModifiedOn
            FROM    MROLEKPI rk
            WHERE   rk.ROLEKPIID = @RoleKPIId
            AND     rk.STATUS   <> 2";

        public const string GET_ROLEKPI_DETAILS = @"
            SELECT  d.ROLEKPIDETAILID,
                    d.ROLEKPIID,
                    d.KPIID,
                    k.KPINAME,
                    k.SECTION      AS Section,
                    k.DESCRIPTION  AS Description
            FROM    MROLEKPIDETAIL d
            INNER JOIN MKPI k ON k.KPIID = d.KPIID AND k.STATUS <> 2
            WHERE   d.ROLEKPIID = @RoleKPIId
            ORDER BY k.SECTION, k.KPINAME";

        public const string GET_SELECT_LIST = @"
            SELECT  rk.ROLEKPIID AS Id,
                    rk.ROLEID
            FROM    MROLEKPI rk
            WHERE   rk.STATUS <> 2
            ORDER BY rk.SORTORDER";

        public const string CHECK_DUPLICATE_ROLE = @"
            SELECT  COUNT(1)
            FROM    MROLEKPI
            WHERE   ROLEID    = @RoleId
            AND     STATUS   <> 2
            AND     ROLEKPIID <> @RoleKPIId";

        public const string SAVE_ROLEKPI = @"
            INSERT INTO MROLEKPI
                (ROLEID, SORTORDER, STATUS, VERSION, SOURCETYPE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@RoleId, @RoleKPISortOrder, 1, 1, @RoleKPISourceType,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE());
            SELECT SCOPE_IDENTITY();";

        public const string UPDATE_ROLEKPI = @"
            UPDATE MROLEKPI
            SET    ROLEID       = @RoleId,
                   SORTORDER    = @RoleKPISortOrder,
                   STATUS       = @RoleKPIStatus,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE()
            WHERE  ROLEKPIID = @RoleKPIId";

        public const string DELETE_ROLEKPI = @"
            UPDATE MROLEKPI
            SET    STATUS       = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE()
            WHERE  ROLEKPIID = @RoleKPIId";

        public const string DELETE_DETAIL_ROWS = @"
            DELETE FROM MROLEKPIDETAIL
            WHERE  ROLEKPIID = @RoleKPIId";

        public const string SAVE_DETAIL_ROW = @"
            INSERT INTO MROLEKPIDETAIL
                (ROLEKPIID, KPIID)
            VALUES
                (@RoleKPIId, @KPIId)";
    }
}