using System;

namespace RecruitmentDAL.Query.Interview
{
    public static class InterviewQB
    {
        // -------------------- GET --------------------
        public const string GET_INTERVIEW = @"
            SELECT
                I.INTERVIEWID        AS InterviewId,
                I.APPLICANTID        AS ApplicantId,
                A.APPLICANTNAME      AS ApplicantName,

                I.APPLICATIONID     AS ApplicationId,

                I.JOBPOSITIONID     AS JobPositionId,
                JP.JOBPOSITIONNAME  AS JobPositionName,

                I.INTERVIEWERID     AS InterviewerId,
                IU.USERNAME         AS InterviewerName,

                I.INTERVIEWTYPEID   AS InterviewTypeId,
                IT.GCMNAME          AS InterviewTypeName,

                I.INTERVIEWDATE     AS InterviewDate,
                I.INTERVIEWTIME     AS InterviewTime,
                I.DURATIONMINUTES   AS DurationMinutes,

                I.LOCATIONID        AS LocationId,
                L.LOCATIONNAME      AS LocationName,

                I.NOTES             AS Notes,

                I.INTERVIEWMODE     AS InterviewMode,
                I.ECPMEETINGID      AS EcpMeetingId,

                I.TENANTID          AS TenantId,

                I.CREATEDBYID       AS CreatedById,
                I.CREATEDON         AS CreatedOn,
                CU.USERNAME         AS CreatedByName,

                I.MODIFIEDBYID      AS ModifiedById,
                I.MODIFIEDON        AS ModifiedOn,
                MU.USERNAME         AS ModifiedByName,

                I.STATUS            AS Status,
                I.VERSION           AS Version
            FROM TINTERVIEW I
            LEFT JOIN TAPPLICANT   A  ON A.APPLICANTID   = I.APPLICANTID
            LEFT JOIN MJOBPOSITION JP ON JP.JOBPOSITIONID = I.JOBPOSITIONID
            LEFT JOIN MGCM         IT ON IT.GCMID        = I.INTERVIEWTYPEID
            LEFT JOIN MLOCATION    L  ON L.LOCATIONID   = I.LOCATIONID
            LEFT JOIN MUSER        IU ON IU.USERID      = I.INTERVIEWERID
            LEFT JOIN MUSER        CU ON CU.USERID      = I.CREATEDBYID
            LEFT JOIN MUSER        MU ON MU.USERID      = I.MODIFIEDBYID
            LEFT JOIN MCLIENT      T  ON T.CLIENTID     = I.TENANTID
            WHERE I.INTERVIEWID = @InterviewId;
        ";

        // -------------------- INSERT --------------------
        public const string SAVE_INTERVIEW = @"
            INSERT INTO TINTERVIEW
            (
                INTERVIEWID,
                APPLICANTID,
                APPLICATIONID,
                JOBPOSITIONID,
                INTERVIEWERID,
                INTERVIEWTYPEID,
                INTERVIEWDATE,
                INTERVIEWTIME,
                DURATIONMINUTES,
                LOCATIONID,
                NOTES,
                INTERVIEWMODE,
                ECPMEETINGID,
                STATUS,
                VERSION,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON,
                TENANTID
            )
            VALUES
            (
                @InterviewId,
                @ApplicantId,
                @ApplicationId,
                @JobPositionId,
                @InterviewerId,
                @InterviewTypeId,
                @InterviewDate,
                @InterviewTime,
                @DurationMinutes,
                @LocationId,
                @Notes,
                @InterviewMode,
                @EcpMeetingId,
                @Status,
                @Version,
                @CreatedById,
                @CreatedOn,
                @ModifiedById,
                @ModifiedOn,
                @TenantId
            );
        ";

        // -------------------- UPDATE --------------------
        public const string UPDATE_INTERVIEW = @"
            UPDATE TINTERVIEW
            SET
                INTERVIEWID     = @InterviewId,
                APPLICANTID      = @ApplicantId,
                APPLICATIONID    = @ApplicationId,
                JOBPOSITIONID   = @JobPositionId,
                INTERVIEWERID   = @InterviewerId,
                INTERVIEWTYPEID = @InterviewTypeId,
                INTERVIEWDATE   = @InterviewDate,
                INTERVIEWTIME   = @InterviewTime,
                DURATIONMINUTES = @DurationMinutes,
                LOCATIONID      = @LocationId,
                NOTES           = @Notes,
                INTERVIEWMODE   = @InterviewMode,
                ECPMEETINGID    = @EcpMeetingId,
                STATUS          = @Status,
                VERSION         = @Version,
                MODIFIEDBYID    = @ModifiedById,
                MODIFIEDON      = @ModifiedOn,
                TENANTID        = @TenantId
            WHERE INTERVIEWID = @InterviewId;
        ";

        // -------------------- ECP MEETING ID FOLLOW-UP --------------------
        // Small, explicit non-transactional follow-up UPDATE fired by InterviewBLL.SaveInterview
        // right after EcpIntegrationService.ScheduleMeetingAsync returns — mirrors
        // ApplicationQB.UPDATE_APPLICATION_CURRENTSTAGE's pattern (see
        // IApplicationDAL.UpdateApplicationCurrentStage doc comment). Deliberately outside
        // SaveInterview's own ExecuteSaveAsync transaction since the ECP call happens after the
        // interview save has already committed.
        public const string UPDATE_INTERVIEW_ECPMEETINGID = @"
            UPDATE TINTERVIEW
            SET
                ECPMEETINGID = @EcpMeetingId,
                MODIFIEDBYID = @ModifiedById,
                MODIFIEDON   = @ModifiedOn
            WHERE INTERVIEWID = @InterviewId
            AND   TENANTID    = @TenantId;
        ";

        // -------------------- DELETE --------------------
        public const string DELETE_INTERVIEW = @"
            DELETE FROM TINTERVIEW
            WHERE INTERVIEWID = @InterviewId;
        ";

        // -------------------- SELECT LIST --------------------
        public const string GETSELECTLIST_INTERVIEW = @"
            SELECT
                I.INTERVIEWID    AS InterviewId,
                I.APPLICANTID    AS ApplicantId,
                I.JOBPOSITIONID AS JobPositionId
            FROM TINTERVIEW I
            WHERE I.STATUS = 1
            ORDER BY I.INTERVIEWDATE DESC;
        ";
    }
}