namespace PERMDAL.QueryBuilders;

// Required indexes:
//   MAPPRAISALPLAN(TENANTID, STATUS, PERFORMANCEFROM, PERFORMANCETO)
//   MAPPRAISALPLAN(TENANTID, APPRAISALTYPEID)
public static class AppraisalPlanQB
{
    public const string GET_BY_ID = @"
SELECT ap.APPRAISALPLANID    AS AppraisalPlanId,
       ap.NAME               AS Name,
       ap.DESCRIPTION        AS Description,
       ap.APPRAISALTYPEID    AS AppraisalTypeId,
       at2.APPRAISALTYPENAME AS AppraisalTypeName,
       ap.PERFORMANCEFROM    AS PerformanceFrom,
       ap.PERFORMANCETO      AS PerformanceTo,
       ap.ENTRYFROM          AS EntryFrom,
       ap.ENTRYTO            AS EntryTo,
       ap.NATURE             AS Nature,
       ap.SELFAPPRAISAL      AS SelfAppraisal,
       ap.CREATEDBYID        AS CreatedById,
       ap.CREATEDON          AS CreatedOn,
       ap.MODIFIEDBYID       AS ModifiedById,
       ap.MODIFIEDON         AS ModifiedOn,
       ap.SORTORDER          AS SortOrder,
       ap.STATUS             AS Status,
       ap.VERSION            AS Version,
       ap.SOURCETYPE         AS SourceType,
       ap.TENANTID           AS TenantId,
       ap.SECURITYLEVEL      AS SecurityLevel
FROM   MAPPRAISALPLAN ap
JOIN   MAPPRAISALTYPE at2 ON at2.APPRAISALTYPEID = ap.APPRAISALTYPEID
WHERE  ap.APPRAISALPLANID = @AppraisalPlanId
AND    ap.TENANTID        = @TenantId";

    public const string GET_LIST = @"
SELECT ap.APPRAISALPLANID    AS AppraisalPlanId,
       ap.NAME               AS Name,
       at2.APPRAISALTYPENAME AS AppraisalTypeName,
       ap.PERFORMANCEFROM    AS PerformanceFrom,
       ap.PERFORMANCETO      AS PerformanceTo,
       ap.NATURE             AS Nature,
       ap.STATUS             AS Status,
       COUNT(*) OVER()       AS TotalCount
FROM   MAPPRAISALPLAN ap
JOIN   MAPPRAISALTYPE at2 ON at2.APPRAISALTYPEID = ap.APPRAISALTYPEID
WHERE  ap.TENANTID = @TenantId
AND    (@Status IS NULL OR ap.STATUS = @Status)
ORDER  BY ap.PERFORMANCEFROM DESC
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

    public const string GET_SELECT_LIST = @"
SELECT APPRAISALPLANID AS AppraisalPlanId,
       NAME            AS Name,
       STATUS          AS Status
FROM   MAPPRAISALPLAN
WHERE  TENANTID = @TenantId
AND    STATUS   IN (0, 1)
{DYNAMIC_WHERE}
ORDER  BY NAME";

    public const string INSERT = @"
INSERT INTO MAPPRAISALPLAN
    (APPRAISALPLANID, NAME, DESCRIPTION, APPRAISALTYPEID, PERFORMANCEFROM, PERFORMANCETO,
     ENTRYFROM, ENTRYTO, NATURE, SELFAPPRAISAL, STATUS, VERSION, SOURCETYPE, TENANTID, SECURITYLEVEL,
     CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER)
VALUES
    (@AppraisalPlanId, @Name, @Description, @AppraisalTypeId, @PerformanceFrom, @PerformanceTo,
     @EntryFrom, @EntryTo, @Nature, @SelfAppraisal, 0, 1, @SourceType, @TenantId, @SecurityLevel,
     @CreatedById, GETUTCDATE(), @ModifiedById, GETUTCDATE(), @SortOrder)";

    public const string UPDATE = @"
UPDATE MAPPRAISALPLAN
SET    NAME            = @Name,
       DESCRIPTION     = @Description,
       APPRAISALTYPEID = @AppraisalTypeId,
       PERFORMANCEFROM = @PerformanceFrom,
       PERFORMANCETO   = @PerformanceTo,
       ENTRYFROM       = @EntryFrom,
       ENTRYTO         = @EntryTo,
       NATURE          = @Nature,
       SELFAPPRAISAL   = @SelfAppraisal,
       MODIFIEDBYID    = @ModifiedById,
       MODIFIEDON      = GETUTCDATE(),
       VERSION         = VERSION + 1
WHERE  APPRAISALPLANID = @AppraisalPlanId
AND    TENANTID        = @TenantId
AND    STATUS          = 0";

    public const string ACTIVATE = @"
UPDATE MAPPRAISALPLAN
SET    STATUS       = 1,
       MODIFIEDBYID = @ModifiedById,
       MODIFIEDON   = GETUTCDATE(),
       VERSION      = VERSION + 1
WHERE  APPRAISALPLANID = @AppraisalPlanId
AND    TENANTID        = @TenantId
AND    STATUS          = 0";

    public const string CLOSE = @"
UPDATE MAPPRAISALPLAN
SET    STATUS       = 2,
       MODIFIEDBYID = @ModifiedById,
       MODIFIEDON   = GETUTCDATE(),
       VERSION      = VERSION + 1
WHERE  APPRAISALPLANID = @AppraisalPlanId
AND    TENANTID        = @TenantId
AND    STATUS          = 1";

    public const string EXISTS_NAME = @"
SELECT COUNT(1)
FROM   MAPPRAISALPLAN
WHERE  NAME            = @Name
AND    TENANTID        = @TenantId
AND    APPRAISALPLANID <> @AppraisalPlanId";

    public const string EXISTS_ACTIVE_PLAN_FOR_TYPE = @"
SELECT COUNT(1)
FROM   MAPPRAISALPLAN
WHERE  APPRAISALTYPEID  = @AppraisalTypeId
AND    TENANTID         = @TenantId
AND    STATUS           = 1
AND    APPRAISALPLANID  <> @ExcludePlanId";
}