namespace RecruitmentDAL.Query.CandidateDemographic
{
    // MCANDIDATEDEMOGRAPHIC — see migration 20260904_Recruitment_Phase5_Analytics_Schema_
    // SqlServer.sql's header comment for the full isolation/consent design rationale, and
    // CandidateDemographicDTO's doc comment for the sanctioned-read-paths list.
    //
    // Indexes required (already ship with the schema migration):
    //   PK_MCANDIDATEDEMOGRAPHIC (CANDIDATEDEMOGRAPHICID),
    //   UQ_MCANDIDATEDEMOGRAPHIC_CANDIDATEID (CANDIDATEID) — drives GET_BY_CANDIDATEID and the
    //     upsert-by-CandidateId lookup in CandidateDemographicBLL.SaveCandidateDemographic,
    //   IX_MCANDIDATEDEMOGRAPHIC_TENANTID (TENANTID).
    public static class CandidateDemographicQB
    {
        // Internal upsert-lookup only — used by CandidateDemographicBLL.SaveCandidateDemographic
        // to decide insert vs update. Never exposed as a public Get-by-CandidateId endpoint (see
        // ICandidateDemographicBLL doc comment) — this returns individual field values and must
        // stay internal to the upsert path.
        public const string GET_BY_CANDIDATEID = @"
SELECT
    CDG.CANDIDATEDEMOGRAPHICID  AS CandidateDemographicId,
    CDG.CANDIDATEID              AS CandidateId,
    CDG.GENDERIDENTITY           AS GenderIdentity,
    CDG.ETHNICITYRACE            AS EthnicityRace,
    CDG.DISABILITYSTATUS         AS DisabilityStatus,
    CDG.VETERANSTATUS            AS VeteranStatus,
    CDG.AGEBAND                  AS AgeBand,
    CDG.CONSENTTOCOLLECT         AS ConsentToCollect,
    CDG.COLLECTEDON              AS CollectedOn,
    CDG.CREATEDBYID              AS CreatedById,
    CDG.CREATEDON                AS CreatedOn,
    CDG.MODIFIEDBYID             AS ModifiedById,
    CDG.MODIFIEDON               AS ModifiedOn,
    CDG.STATUS                   AS Status,
    CDG.VERSION                  AS Version,
    CDG.TENANTID                 AS TenantId
FROM MCANDIDATEDEMOGRAPHIC CDG
WHERE CDG.CANDIDATEID = @CandidateId
AND   CDG.TENANTID    = @TenantId
AND   CDG.STATUS      != 2;
";

        public const string SAVE_CANDIDATEDEMOGRAPHIC = @"
INSERT INTO MCANDIDATEDEMOGRAPHIC (
    CANDIDATEDEMOGRAPHICID,
    CANDIDATEID,
    GENDERIDENTITY,
    ETHNICITYRACE,
    DISABILITYSTATUS,
    VETERANSTATUS,
    AGEBAND,
    CONSENTTOCOLLECT,
    COLLECTEDON,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    STATUS,
    VERSION,
    TENANTID
)
VALUES (
    @CandidateDemographicId,
    @CandidateId,
    @GenderIdentity,
    @EthnicityRace,
    @DisabilityStatus,
    @VeteranStatus,
    @AgeBand,
    @ConsentToCollect,
    @CollectedOn,
    @CreatedById,
    @CreatedOn,
    @ModifiedById,
    @ModifiedOn,
    @Status,
    @Version,
    @TenantId
);
";

        // Consent/data-field columns are always overwritten in full on update — this entity has
        // no "patch individual field" path, matching the upsert-by-CandidateId semantics: every
        // save (from the FLS VoluntarySelfId submit handler) supplies the complete current state.
        public const string UPDATE_CANDIDATEDEMOGRAPHIC = @"
UPDATE MCANDIDATEDEMOGRAPHIC
SET
    GENDERIDENTITY    = @GenderIdentity,
    ETHNICITYRACE     = @EthnicityRace,
    DISABILITYSTATUS  = @DisabilityStatus,
    VETERANSTATUS     = @VeteranStatus,
    AGEBAND           = @AgeBand,
    CONSENTTOCOLLECT  = @ConsentToCollect,
    COLLECTEDON       = @CollectedOn,
    MODIFIEDBYID      = @ModifiedById,
    MODIFIEDON        = @ModifiedOn,
    STATUS            = @Status,
    VERSION           = @Version
WHERE CANDIDATEDEMOGRAPHICID = @CandidateDemographicId
AND   TENANTID                = @TenantId;
";

        // Soft-delete via STATUS=2, same convention as every other Phase-1/3 Recruitment entity
        // (see CandidateQB.DELETE_CANDIDATE's doc comment) — not currently called from the FLS
        // bridge, provided for CLAUDE.md CRUD-completeness / a future admin/compliance withdrawal
        // flow (e.g. a candidate later asking to have their self-ID data removed).
        public const string DELETE_CANDIDATEDEMOGRAPHIC = @"
UPDATE MCANDIDATEDEMOGRAPHIC SET
    STATUS       = 2,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE CANDIDATEDEMOGRAPHICID = @CandidateDemographicId
AND   TENANTID                = @TenantId;
";
    }
}