using System;

namespace RecruitmentDAL.Query.InterviewPanel
{
    public static class InterviewPanelQB
    {
        // -------------------- GET --------------------
        public const string GET_INTERVIEWPANEL = @"
            SELECT
                IP.INTERVIEWPANELID AS InterviewPanelId,
                IP.INTERVIEWID      AS InterviewId,
                IP.INTERVIEWERID    AS InterviewerId,
                IU.USERNAME         AS InterviewerName,
                IP.ISLEAD           AS IsLead,

                IP.SORTORDER        AS SortOrder,
                IP.STATUS           AS Status,
                IP.VERSION          AS Version,
                IP.TENANTID         AS TenantId,

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

                IP.MODIFIEDBYID     AS ModifiedById,
                IP.MODIFIEDON       AS ModifiedOn,
                MU.USERNAME         AS ModifiedByName
            FROM TINTERVIEWPANEL IP
            LEFT JOIN MUSER IU ON IU.USERID = IP.INTERVIEWERID
            LEFT JOIN MUSER CU ON CU.USERID = IP.CREATEDBYID
            LEFT JOIN MUSER MU ON MU.USERID = IP.MODIFIEDBYID
            WHERE IP.INTERVIEWPANELID = @InterviewPanelId
            AND   IP.TENANTID         = @TenantId;
        ";

        // All active panelists for one interview — what the FE needs to render a panel.
        public const string GET_INTERVIEWPANEL_BY_INTERVIEW = @"
            SELECT
                IP.INTERVIEWPANELID AS InterviewPanelId,
                IP.INTERVIEWID      AS InterviewId,
                IP.INTERVIEWERID    AS InterviewerId,
                IU.USERNAME         AS InterviewerName,
                IP.ISLEAD           AS IsLead,

                IP.SORTORDER        AS SortOrder,
                IP.STATUS           AS Status,
                IP.VERSION          AS Version,
                IP.TENANTID         AS TenantId,

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

                IP.MODIFIEDBYID     AS ModifiedById,
                IP.MODIFIEDON       AS ModifiedOn,
                MU.USERNAME         AS ModifiedByName
            FROM TINTERVIEWPANEL IP
            LEFT JOIN MUSER IU ON IU.USERID = IP.INTERVIEWERID
            LEFT JOIN MUSER CU ON CU.USERID = IP.CREATEDBYID
            LEFT JOIN MUSER MU ON MU.USERID = IP.MODIFIEDBYID
            WHERE IP.INTERVIEWID = @InterviewId
            AND   IP.TENANTID    = @TenantId
            AND   IP.STATUS      = 1
            ORDER BY IP.ISLEAD DESC, IP.SORTORDER;
        ";

        // -------------------- INSERT --------------------
        public const string SAVE_INTERVIEWPANEL = @"
            INSERT INTO TINTERVIEWPANEL
            (
                INTERVIEWPANELID,
                INTERVIEWID,
                INTERVIEWERID,
                ISLEAD,
                SORTORDER,
                STATUS,
                VERSION,
                TENANTID,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON
            )
            VALUES
            (
                @InterviewPanelId,
                @InterviewId,
                @InterviewerId,
                @IsLead,
                @SortOrder,
                @Status,
                @Version,
                @TenantId,
                @CreatedById,
                @CreatedOn,
                @ModifiedById,
                @ModifiedOn
            );
        ";

        // -------------------- UPDATE --------------------
        public const string UPDATE_INTERVIEWPANEL = @"
            UPDATE TINTERVIEWPANEL
            SET
                INTERVIEWID   = @InterviewId,
                INTERVIEWERID = @InterviewerId,
                ISLEAD        = @IsLead,
                SORTORDER     = @SortOrder,
                STATUS        = @Status,
                VERSION       = @Version,
                MODIFIEDBYID  = @ModifiedById,
                MODIFIEDON    = @ModifiedOn
            WHERE INTERVIEWPANELID = @InterviewPanelId
            AND   TENANTID         = @TenantId;
        ";

        // Soft delete (STATUS = 2 / Deleted) — TINTERVIEWPANEL rows are part of an interview's
        // audit trail, so they are marked deleted rather than physically removed, matching the
        // convention already used by JobRequisitionQB.DELETE_JOBREQUISITION /
        // ApplicationQB.DELETE_APPLICATION / SelectionProcessQB's soft-delete queries.
        public const string DELETE_INTERVIEWPANEL = @"
            UPDATE TINTERVIEWPANEL
            SET    STATUS       = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = @ModifiedOn
            WHERE  INTERVIEWPANELID = @InterviewPanelId
            AND    TENANTID         = @TenantId;
        ";
    }
}