using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace RecruitmentDAL.Query.Candidate
{
    public static class CandidateQB
    {
        // Indexes required (already in 20260901_Recruitment_Phase1_CoreATS_Schema_SqlServer.sql):
        //   PK_MCANDIDATE (CANDIDATEID), IX_MCANDIDATE_TENANTID (TENANTID),
        //   IX_MCANDIDATE_STATUS (STATUS), IX_MCANDIDATE_EMAIL (EMAIL)
        public const string GET_CANDIDATE = @"
SELECT
    CD.CANDIDATEID          AS CandidateId,
    CD.FIRSTNAME            AS FirstName,
    CD.LASTNAME             AS LastName,
    CD.EMAIL                AS Email,
    CD.PHONE                AS Phone,
    CD.RESUMEURL            AS ResumeUrl,
    CD.SOURCE               AS Source,
    CD.SOURCEDETAIL         AS SourceDetail,
    CD.SKILLTAGS            AS SkillTags,
    CD.EMPLOYEEID           AS EmployeeId,
    CD.AGENCYID             AS AgencyId,
    CD.CONSENTGIVEN         AS ConsentGiven,
    CD.CONSENTDATE          AS ConsentDate,
    CD.CANDIDATETYPE        AS CandidateType,
    CD.ENGAGEMENTTYPE       AS EngagementType,
    CD.CONTRACTDURATIONMONTHS AS ContractDurationMonths,

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

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

    CD.SORTORDER            AS SortOrder,
    CD.STATUS               AS Status,
    CD.VERSION              AS Version,
    CD.TENANTID             AS TenantId
FROM MCANDIDATE CD
LEFT JOIN MUSER CU ON CU.USERID = CD.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = CD.MODIFIEDBYID
WHERE CD.CANDIDATEID = @CandidateId
AND   CD.TENANTID    = @TenantId;
";

        public const string SAVE_CANDIDATE = @"
INSERT INTO MCANDIDATE (
    CANDIDATEID,
    FIRSTNAME,
    LASTNAME,
    EMAIL,
    PHONE,
    RESUMEURL,
    SOURCE,
    SOURCEDETAIL,
    SKILLTAGS,
    EMPLOYEEID,
    AGENCYID,
    CONSENTGIVEN,
    CONSENTDATE,
    CANDIDATETYPE,
    ENGAGEMENTTYPE,
    CONTRACTDURATIONMONTHS,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SORTORDER,
    STATUS,
    VERSION,
    TENANTID
)
VALUES (
    @CandidateId,
    @FirstName,
    @LastName,
    @Email,
    @Phone,
    @ResumeUrl,
    @Source,
    @SourceDetail,
    @SkillTags,
    @EmployeeId,
    @AgencyId,
    @ConsentGiven,
    @ConsentDate,
    @CandidateType,
    @EngagementType,
    @ContractDurationMonths,
    @CreatedById,
    @CreatedOn,
    @ModifiedById,
    @ModifiedOn,
    @SortOrder,
    @Status,
    @Version,
    @TenantId
);
";

        public const string UPDATE_CANDIDATE = @"
UPDATE MCANDIDATE
SET
    FIRSTNAME     = @FirstName,
    LASTNAME      = @LastName,
    EMAIL         = @Email,
    PHONE         = @Phone,
    RESUMEURL     = @ResumeUrl,
    SOURCE        = @Source,
    SOURCEDETAIL  = @SourceDetail,
    SKILLTAGS     = @SkillTags,
    EMPLOYEEID    = @EmployeeId,
    AGENCYID      = @AgencyId,
    CONSENTGIVEN  = @ConsentGiven,
    CONSENTDATE   = @ConsentDate,
    CANDIDATETYPE = @CandidateType,
    ENGAGEMENTTYPE = @EngagementType,
    CONTRACTDURATIONMONTHS = @ContractDurationMonths,
    MODIFIEDBYID  = @ModifiedById,
    MODIFIEDON    = @ModifiedOn,
    SORTORDER     = @SortOrder,
    STATUS        = @Status,
    VERSION       = @Version
WHERE CANDIDATEID = @CandidateId
AND   TENANTID    = @TenantId;
";

        // Soft-delete via STATUS=2 (Deleted) — standardized across every Phase-1 Recruitment
        // entity (JobRequisition, JobProfileLibrary, SelectionProcess*, Application). A hard
        // DELETE here would also fail with an FK violation the moment a Candidate has any
        // TAPPLICATION row referencing it, since TAPPLICATION.CANDIDATEID FKs to this table —
        // soft-delete is both the consistent choice and the only one that actually works once
        // a candidate has application history.
        public const string DELETE_CANDIDATE = @"
UPDATE MCANDIDATE SET
    STATUS       = 2,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE CANDIDATEID = @CandidateId
AND   TENANTID    = @TenantId;
";

        // Candidate pool browse list — filtered to active rows for the current tenant, with an
        // optional name/email search. Unlike most master-data GetSelectList queries in this
        // module, the candidate pool is expected to grow large and is never manually pruned, so
        // this one is paged via QueryPagedAsync (OFFSET/FETCH below), matching the working
        // GET_KMARTIFACTS_PAGED / GET_LOYALTY_PROGRAM_LIST / GET_KMJOBS_PAGED precedents
        // (KmArtifactQB, LoyaltyProgramQB, KmDiscoveryQB) — @Offset/@PageSize are computed by the
        // DAL from FirstNumber/MaxResult. Note: IQueryExecutor.QueryPagedAsync(login, sql,
        // parameters, transaction, useReadUncommitted) has no separate count-query parameter — it
        // derives TotalCount by wrapping the given sql in SELECT COUNT(*) FROM (...); no precedent
        // in the repo defines a hand-written COUNT sibling for it, so none is added here either
        // (one would be unused dead code against the real signature).
        public const string GET_SELECTLIST_CANDIDATE = @"
SELECT
    CD.CANDIDATEID          AS CandidateId,
    CD.FIRSTNAME            AS FirstName,
    CD.LASTNAME             AS LastName,
    CD.EMAIL                AS Email,
    CD.PHONE                AS Phone,
    CD.RESUMEURL            AS ResumeUrl,
    CD.SOURCE               AS Source,
    CD.SOURCEDETAIL         AS SourceDetail,
    CD.SKILLTAGS            AS SkillTags,
    CD.EMPLOYEEID           AS EmployeeId,
    CD.AGENCYID             AS AgencyId,
    CD.CONSENTGIVEN         AS ConsentGiven,
    CD.CONSENTDATE          AS ConsentDate,
    CD.CANDIDATETYPE        AS CandidateType,
    CD.ENGAGEMENTTYPE       AS EngagementType,
    CD.CONTRACTDURATIONMONTHS AS ContractDurationMonths,

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

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

    CD.SORTORDER            AS SortOrder,
    CD.STATUS               AS Status,
    CD.VERSION              AS Version,
    CD.TENANTID             AS TenantId
FROM MCANDIDATE CD
LEFT JOIN MUSER CU ON CU.USERID = CD.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = CD.MODIFIEDBYID
WHERE CD.STATUS   = 1
AND   CD.TENANTID = @TenantId
AND   (@SearchKeyword IS NULL OR @SearchKeyword = ''
       OR CD.FIRSTNAME LIKE '%' + @SearchKeyword + '%'
       OR CD.LASTNAME  LIKE '%' + @SearchKeyword + '%'
       OR CD.EMAIL     LIKE '%' + @SearchKeyword + '%')
ORDER BY CD.FIRSTNAME, CD.LASTNAME
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;
";

        // Duplicate-applicant lookup — used by the public career-site apply flow
        // (RecruitmentBLL.Career.CareerBLL.SubmitApplication) to reuse an existing Candidate row
        // for a repeat applicant rather than creating a duplicate MCANDIDATE per submission. A
        // plain equality match on EMAIL, scoped to active rows for the tenant (same idiom as
        // GET_CURRENT_STAGE_BY_EMAIL below). TOP 1 is defensive only — EMAIL has no unique
        // constraint on MCANDIDATE, so this deliberately picks one deterministic row (most
        // recently modified) rather than erroring if more than one somehow exists.
        // Covering index: IX_MCANDIDATE_EMAIL + IX_MCANDIDATE_TENANTID (already ship with
        // 20260901_Recruitment_Phase1_CoreATS_Schema_SqlServer.sql).
        public const string GET_CANDIDATE_BY_EMAIL = @"
SELECT TOP 1
    CD.CANDIDATEID          AS CandidateId,
    CD.FIRSTNAME            AS FirstName,
    CD.LASTNAME             AS LastName,
    CD.EMAIL                AS Email,
    CD.PHONE                AS Phone,
    CD.RESUMEURL            AS ResumeUrl,
    CD.SOURCE               AS Source,
    CD.SOURCEDETAIL         AS SourceDetail,
    CD.SKILLTAGS            AS SkillTags,
    CD.EMPLOYEEID           AS EmployeeId,
    CD.AGENCYID             AS AgencyId,
    CD.CONSENTGIVEN         AS ConsentGiven,
    CD.CONSENTDATE          AS ConsentDate,
    CD.CANDIDATETYPE        AS CandidateType,
    CD.ENGAGEMENTTYPE       AS EngagementType,
    CD.CONTRACTDURATIONMONTHS AS ContractDurationMonths,

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

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

    CD.SORTORDER            AS SortOrder,
    CD.STATUS               AS Status,
    CD.VERSION              AS Version,
    CD.TENANTID             AS TenantId
FROM MCANDIDATE CD
LEFT JOIN MUSER CU ON CU.USERID = CD.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = CD.MODIFIEDBYID
WHERE CD.EMAIL    = @Email
AND   CD.STATUS   = 1
AND   CD.TENANTID = @TenantId
ORDER BY CD.MODIFIEDON DESC;
";

        // Internal-mobility dedup lookup — used by RecruitmentBLL.CandidateConversion.
        // EmployeeToInternalCandidateBLL to reuse an existing InternalEmployee-type Candidate row
        // for a repeat internal-mobility applicant (same employee applying to a second internal
        // opening) rather than seeding a duplicate MCANDIDATE per submission. Scoped to
        // CandidateType=2 (InternalEmployee) so a coincidental EmployeeId match against a stale/
        // reused placeholder value on an External/ContingentGig row (default -1, never a real
        // employee for those types) can never match. Covering index:
        // IX_MCANDIDATE_EMPLOYEEID (TenantId, EmployeeId) — see the Phase 6 migration.
        public const string GET_CANDIDATE_BY_EMPLOYEEID = @"
SELECT TOP 1
    CD.CANDIDATEID          AS CandidateId,
    CD.FIRSTNAME            AS FirstName,
    CD.LASTNAME             AS LastName,
    CD.EMAIL                AS Email,
    CD.PHONE                AS Phone,
    CD.RESUMEURL            AS ResumeUrl,
    CD.SOURCE               AS Source,
    CD.SOURCEDETAIL         AS SourceDetail,
    CD.SKILLTAGS            AS SkillTags,
    CD.EMPLOYEEID           AS EmployeeId,
    CD.CONSENTGIVEN         AS ConsentGiven,
    CD.CONSENTDATE          AS ConsentDate,
    CD.CANDIDATETYPE        AS CandidateType,
    CD.ENGAGEMENTTYPE       AS EngagementType,
    CD.CONTRACTDURATIONMONTHS AS ContractDurationMonths,

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

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

    CD.SORTORDER            AS SortOrder,
    CD.STATUS               AS Status,
    CD.VERSION              AS Version,
    CD.TENANTID             AS TenantId
FROM MCANDIDATE CD
LEFT JOIN MUSER CU ON CU.USERID = CD.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = CD.MODIFIEDBYID
WHERE CD.EMPLOYEEID    = @EmployeeId
AND   CD.CANDIDATETYPE = 2
AND   CD.STATUS        = 1
AND   CD.TENANTID      = @TenantId
ORDER BY CD.MODIFIEDON DESC;
";

        // Dedicated, narrow follow-up UPDATE — fires from CandidateConversionBLL once
        // PayRollBLL.Employee.IEmployeeBLL.SaveEmployee has committed the new Employee, to stamp
        // the resulting EmployeeId back onto the Candidate row (CandidateDTO.EmployeeId defaults
        // to -1 — "not yet converted", see that DTO's doc comment). Deliberately not part of
        // ExecuteSaveAsync — same non-transactional-follow-up pattern as
        // IApplicationDAL.UpdateApplicationCurrentStage / IInterviewDAL.UpdateInterviewEcpMeetingId /
        // IJobOfferDAL.UpdateJobOfferResponse: the Employee save is a separate module's own
        // ExecuteSaveAsync-less transaction (PayRoll's SaveEmployee uses a TransactionScope, not
        // this module's BaseEntityAppService), so there is no shared transaction to fold this
        // into. Worst case on failure here is a real new Employee whose source Candidate row
        // hasn't caught up yet — recoverable (retry the stamp), not data corruption.
        public const string UPDATE_CANDIDATE_EMPLOYEEID = @"
UPDATE MCANDIDATE
SET
    EMPLOYEEID = @EmployeeId
WHERE CANDIDATEID = @CandidateId
AND   TENANTID     = @TenantId;
";

        // Cross-module bridge for gb4.7mfe's legacy Applicant dashboard (projects/recruitment/
        // dashboard/applicantdashboard) — see PayRollDAL.DTO.Applicant.ApplicantReportDTO. That
        // dashboard's own data source (GET /prs/Applicant.svc/ApplicantReport) is served by an
        // external legacy GB4 WCF service (http://183.82.250.223:98/gb4/...), not by any query in
        // this repo, so the bridge cannot be built by extending that report's SQL directly. Instead
        // the FE sends the batch of ApplicantMailId values already present in that legacy response,
        // and this query resolves each one to a real MCANDIDATE + current pipeline stage — a plain
        // equality join on EMAIL, never a fuzzy/name-based match. An email with no active Candidate
        // row for the tenant is simply absent from the result set.
        //
        // A Candidate can in principle have more than one Application (e.g. re-applying to a
        // different requisition), so LatestApplication picks the most recent active one per
        // Candidate via ROW_NUMBER() partitioned by CANDIDATEID ordered by APPLIEDON DESC — same
        // "latest child row" idiom used elsewhere in this repo (e.g. CRMDAL.ActionQB,
        // PayRollDAL.EmployeeQB).
        //
        // Indexes required (already ship with 20260901_Recruitment_Phase1_CoreATS_Schema_SqlServer.sql):
        //   IX_MCANDIDATE_EMAIL, IX_MCANDIDATE_TENANTID, IX_TAPPLICATION_CANDIDATEID.
        public const string GET_CURRENT_STAGE_BY_EMAIL = @"
WITH LatestApplication AS
(
    SELECT
        TA.CANDIDATEID,
        TA.CURRENTSTAGEID,
        ROW_NUMBER() OVER (PARTITION BY TA.CANDIDATEID ORDER BY TA.APPLIEDON DESC) AS RN
    FROM TAPPLICATION TA
    WHERE TA.STATUS   = 1
    AND   TA.TENANTID = @TenantId
)
SELECT
    MC.EMAIL          AS Email,
    MC.CANDIDATEID    AS CandidateId,
    LA.CURRENTSTAGEID AS CurrentStageId,
    MPS.STAGENAME     AS CurrentStageName
FROM MCANDIDATE MC
LEFT JOIN LatestApplication LA
       ON LA.CANDIDATEID = MC.CANDIDATEID AND LA.RN = 1
LEFT JOIN MSELECTIONPROCESSSTAGE MPS
       ON MPS.SELECTIONPROCESSSTAGEID = LA.CURRENTSTAGEID
WHERE MC.STATUS   = 1
AND   MC.TENANTID = @TenantId
AND   MC.EMAIL IN @Emails;
";
    }
}