namespace TCMSDAL.QueryBuilders;

/// <summary>
/// SQL constants for MTCMSPROJECT.
/// Required indexes:
///   IX_MTCMSPROJECT_TENANTID  (TENANTID)
///   IX_MTCMSPROJECT_STATUS    (TENANTID, STATUS, ISACTIVE)
/// </summary>
public static class TCMSProjectQB
{
    public const string GET_PROJECT = @"
        SELECT P.PROJECTID          AS ProjectId,
               P.PROJECTCODE        AS ProjectCode,
               P.PROJECTNAME        AS ProjectName,
               P.DESCRIPTION        AS Description,
               P.PROJECTTYPE        AS ProjectType,
               P.CLIENTID           AS ClientId,
               C.CLIENTNAME         AS ClientName,
               P.PRODUCTID          AS ProductId,
               PR.PRODUCTNAME       AS ProductName,
               P.STATUS             AS Status,
               P.LEADCONSULTANT     AS LeadConsultant,
               P.STARTDATE          AS StartDate,
               P.GOLIVEDATE         AS GoLiveDate,
               P.ISACTIVE           AS IsActive,
               P.TENANTID           AS TenantId,
               P.CREATEDBY          AS CreatedBy,
               P.CREATEDDATE        AS CreatedDate,
               P.MODIFIEDBY         AS ModifiedBy,
               P.MODIFIEDDATE       AS ModifiedDate
        FROM   MTCMSPROJECT P
        LEFT JOIN MTCMSCLIENT  C  ON C.CLIENTID  = P.CLIENTID
        LEFT JOIN MTCMSPRODUCT PR ON PR.PRODUCTID = P.PRODUCTID
        WHERE  P.PROJECTID = @ProjectId
        AND    P.TENANTID  = @TenantId";

    public const string GET_PROJECT_LIST = @"
        SELECT P.PROJECTID      AS ProjectId,
               P.PROJECTCODE    AS ProjectCode,
               P.PROJECTNAME    AS ProjectName,
               P.PROJECTTYPE    AS ProjectType,
               C.CLIENTNAME     AS ClientName,
               P.STATUS         AS Status,
               P.ISACTIVE       AS IsActive
        FROM   MTCMSPROJECT P
        LEFT JOIN MTCMSCLIENT C ON C.CLIENTID = P.CLIENTID
        WHERE  P.TENANTID = @TenantId
        AND    (@IsActive IS NULL OR P.ISACTIVE = @IsActive)
        AND    (@ProjectType IS NULL OR P.PROJECTTYPE = @ProjectType)
        AND    (@Status IS NULL OR P.STATUS = @Status)
        ORDER BY P.PROJECTNAME";

    public const string GET_SELECT_LIST = @"
        SELECT PROJECTID   AS ProjectId,
               PROJECTCODE AS ProjectCode,
               PROJECTNAME AS ProjectName
        FROM   MTCMSPROJECT
        WHERE  TENANTID = @TenantId
        AND    ISACTIVE  = 1
        ORDER BY PROJECTNAME";

    public const string SAVE_PROJECT = @"
        INSERT INTO MTCMSPROJECT
            (PROJECTCODE, PROJECTNAME, DESCRIPTION, PROJECTTYPE,
             CLIENTID, PRODUCTID, STATUS, LEADCONSULTANT,
             STARTDATE, GOLIVEDATE, ISACTIVE,
             TENANTID, CREATEDBY, CREATEDDATE)
        VALUES
            (@ProjectCode, @ProjectName, @Description, @ProjectType,
             @ClientId, @ProductId, @Status, @LeadConsultant,
             @StartDate, @GoLiveDate, 1,
             @TenantId, @CreatedBy, GETUTCDATE())";

    public const string UPDATE_PROJECT = @"
        UPDATE MTCMSPROJECT
        SET    PROJECTNAME    = @ProjectName,
               DESCRIPTION    = @Description,
               PROJECTTYPE    = @ProjectType,
               CLIENTID       = @ClientId,
               PRODUCTID      = @ProductId,
               STATUS         = @Status,
               LEADCONSULTANT = @LeadConsultant,
               STARTDATE      = @StartDate,
               GOLIVEDATE     = @GoLiveDate,
               ISACTIVE       = @IsActive,
               MODIFIEDBY     = @ModifiedBy,
               MODIFIEDDATE   = GETUTCDATE()
        WHERE  PROJECTID = @ProjectId
        AND    TENANTID  = @TenantId";

    public const string CHECK_CODE_EXISTS = @"
        SELECT COUNT(1) FROM MTCMSPROJECT
        WHERE  PROJECTCODE = @ProjectCode
        AND    TENANTID    = @TenantId
        AND    PROJECTID  <> ISNULL(@ProjectId, 0)";
}