namespace PERMDAL.QueryBuilders;

// Indexes required:
//   TPIPPLAN(EMPLOYEEID, TENANTID, STATUS)
//   TPIPPLAN(APPRAISALID, TENANTID)
//   TPIPACTION(PIPPLANID)
public static class PIPQB
{
    public const string EXISTS_ACTIVE_PIP = @"
        SELECT COUNT(1)
        FROM   TPIPPLAN
        WHERE  EMPLOYEEID = @EmployeeId
        AND    STATUS     IN (0, 1)
        AND    TENANTID   = @TenantId";

    public const string GET_BY_ID = @"
        SELECT  pp.PIPPLANID      AS PIPPlanId,
                pp.APPRAISALID    AS AppraisalId,
                pp.EMPLOYEEID     AS EmployeeId,
                e.EMPLOYEENAME    AS EmployeeName,
                e.EMPLOYEECODE    AS EmployeeCode,
                pp.MANAGERID      AS ManagerId,
                m.EMPLOYEENAME    AS ManagerName,
                pp.STARTDATE      AS StartDate,
                pp.ENDDATE        AS EndDate,
                pp.REASON         AS Reason,
                pp.STATUS         AS Status,
                pp.TENANTID       AS TenantId
        FROM    TPIPPLAN pp
        INNER JOIN MEMPLOYEE e ON e.EMPLOYEEID = pp.EMPLOYEEID AND e.TENANTID = pp.TENANTID
        INNER JOIN MEMPLOYEE m ON m.EMPLOYEEID = pp.MANAGERID  AND m.TENANTID = pp.TENANTID
        WHERE   pp.PIPPLANID = @PIPPlanId
        AND     pp.TENANTID  = @TenantId";

    public const string GET_LIST = @"
        SELECT  pp.PIPPLANID      AS PIPPlanId,
                pp.EMPLOYEEID     AS EmployeeId,
                e.EMPLOYEENAME    AS EmployeeName,
                e.EMPLOYEECODE    AS EmployeeCode,
                pp.STARTDATE      AS StartDate,
                pp.ENDDATE        AS EndDate,
                pp.STATUS         AS Status,
                COUNT(*) OVER()   AS TotalCount
        FROM    TPIPPLAN pp
        INNER JOIN MEMPLOYEE e ON e.EMPLOYEEID = pp.EMPLOYEEID AND e.TENANTID = pp.TENANTID
        WHERE   pp.TENANTID    = @TenantId
        AND     (@EmployeeId   = 0 OR pp.EMPLOYEEID = @EmployeeId)
        AND     (@Status IS NULL   OR pp.STATUS     = @Status)
        ORDER BY pp.STARTDATE DESC
        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

    public const string GET_ACTIONS_BY_PLAN = @"
        SELECT  pa.PIPACTIONID    AS PIPActionId,
                pa.PIPPLANID      AS PIPPlanId,
                pa.ACTIONTEXT     AS ActionText,
                pa.DUEDATE        AS DueDate,
                pa.STATUS         AS Status,
                pa.TENANTID       AS TenantId
        FROM    TPIPACTION pa
        WHERE   pa.PIPPLANID = @PIPPlanId
        ORDER BY pa.DUEDATE";

    public const string INSERT_PIPPLAN = @"
        INSERT INTO TPIPPLAN
            (PIPPLANID, APPRAISALID, EMPLOYEEID, MANAGERID, STARTDATE, ENDDATE,
             REASON, STATUS, TENANTID, CREATEDBY, CREATEDDATE)
        VALUES
            (@PIPPlanId, @AppraisalId, @EmployeeId, @ManagerId, GETUTCDATE(), @EndDate,
             @Reason, 0, @TenantId, @CreatedBy, GETUTCDATE())";

    public const string UPDATE_PIPPLAN = @"
        UPDATE  TPIPPLAN
        SET     ENDDATE      = @EndDate,
                REASON       = @Reason
        WHERE   PIPPLANID = @PIPPlanId
        AND     STATUS    = 0
        AND     TENANTID  = @TenantId";

    public const string ACTIVATE_PIPPLAN = @"
        UPDATE  TPIPPLAN
        SET     STATUS       = 1
        WHERE   PIPPLANID = @PIPPlanId
        AND     STATUS    = 0
        AND     TENANTID  = @TenantId";

    public const string CLOSE_PIPPLAN = @"
        UPDATE  TPIPPLAN
        SET     STATUS       = 2
        WHERE   PIPPLANID = @PIPPlanId
        AND     STATUS    = 1
        AND     TENANTID  = @TenantId";

    public const string INSERT_ACTION = @"
        INSERT INTO TPIPACTION
            (PIPACTIONID, PIPPLANID, ACTIONTEXT, DUEDATE, STATUS, TENANTID, CREATEDBY, CREATEDDATE)
        VALUES
            (@PIPActionId, @PIPPlanId, @ActionText, @DueDate, 0, @TenantId, @CreatedBy, GETUTCDATE())";

    public const string UPDATE_ACTION = @"
        UPDATE  TPIPACTION
        SET     ACTIONTEXT   = @ActionText,
                DUEDATE      = @DueDate
        WHERE   PIPACTIONID = @PIPActionId
        AND     STATUS      = 0";

    public const string COMPLETE_ACTION = @"
        UPDATE  TPIPACTION
        SET     STATUS       = 1
        WHERE   PIPACTIONID = @PIPActionId
        AND     STATUS      IN (0, 2)";

    public const string GET_ACTIVE_BY_EMPLOYEE = @"
        SELECT  TOP 1
                pp.PIPPLANID      AS PIPPlanId,
                pp.APPRAISALID    AS AppraisalId,
                pp.EMPLOYEEID     AS EmployeeId,
                e.EMPLOYEENAME    AS EmployeeName,
                e.EMPLOYEECODE    AS EmployeeCode,
                pp.MANAGERID      AS ManagerId,
                m.EMPLOYEENAME    AS ManagerName,
                pp.STARTDATE      AS StartDate,
                pp.ENDDATE        AS EndDate,
                pp.REASON         AS Reason,
                pp.STATUS         AS Status,
                pp.TENANTID       AS TenantId
        FROM    TPIPPLAN pp
        INNER JOIN MEMPLOYEE e ON e.EMPLOYEEID = pp.EMPLOYEEID AND e.TENANTID = pp.TENANTID
        INNER JOIN MEMPLOYEE m ON m.EMPLOYEEID = pp.MANAGERID  AND m.TENANTID = pp.TENANTID
        WHERE   pp.EMPLOYEEID = @EmployeeId
        AND     pp.STATUS     = 1
        AND     pp.TENANTID   = @TenantId
        ORDER BY pp.STARTDATE DESC";
}