namespace EntitlementDAL.QueryBuilders;

/// <summary>SQL constants for MAGREEMENTTYPE / MAGREEMENTVERSION / LAGREEMENTACCEPTANCE
/// (Legal/Contract Agreement Consent, Phase 0 — tracker §50/§51). Column list matches
/// 20260904_Entitlement_Agreement_Schema_{SqlServer,Postgres}.sql exactly.</summary>
public static class AgreementQB
{
    public const string GET_TYPE_BY_CODE = @"
        SELECT
            AGREEMENTTYPEID              AS AgreementTypeId,
            TYPECODE                     AS TypeCode,
            TYPENAME                     AS TypeName,
            REQUIRESINDIVIDUALACCEPTANCE AS RequiresIndividualAcceptance,
            VERSION                      AS Version,
            STATUS                       AS Status,
            SORTORDER                    AS SortOrder,
            CREATEDBYID                  AS CreatedById,
            CREATEDON                    AS CreatedOn,
            MODIFIEDBYID                 AS ModifiedById,
            MODIFIEDON                   AS ModifiedOn,
            SOURCETYPE                   AS SourceType
        FROM MAGREEMENTTYPE
        WHERE TYPECODE = @TypeCode AND STATUS = 1";

    // Admin authoring list (tracker §51.13) — every real agreement type, for a picker/list
    // screen. No pagination — this is a small, hand-curated catalog (types, not versions).
    public const string GET_TYPE_LIST = @"
        SELECT
            AGREEMENTTYPEID              AS AgreementTypeId,
            TYPECODE                     AS TypeCode,
            TYPENAME                     AS TypeName,
            REQUIRESINDIVIDUALACCEPTANCE AS RequiresIndividualAcceptance,
            VERSION                      AS Version,
            STATUS                       AS Status,
            SORTORDER                    AS SortOrder,
            CREATEDBYID                  AS CreatedById,
            CREATEDON                    AS CreatedOn,
            MODIFIEDBYID                 AS ModifiedById,
            MODIFIEDON                   AS ModifiedOn,
            SOURCETYPE                   AS SourceType
        FROM MAGREEMENTTYPE
        WHERE STATUS = 1
        ORDER BY TYPECODE";

    public const string INSERT_TYPE = @"
        INSERT INTO MAGREEMENTTYPE
            (AGREEMENTTYPEID, TYPECODE, TYPENAME, REQUIRESINDIVIDUALACCEPTANCE,
             VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE)
        VALUES
            (@AgreementTypeId, @TypeCode, @TypeName, @RequiresIndividualAcceptance,
             @Version, @Status, @SortOrder, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType)";

    // Resolves the Published bundle for a set of AgreementTypeCodes, preferring a
    // jurisdiction-specific override and falling back to the universal (JurisdictionCode IS
    // NULL) version — tracker §50.3's own jurisdiction-resolution rule. ROW_NUMBER picks exactly
    // one version per type: the jurisdiction match if one exists (Pref=0), else the universal
    // default (Pref=1); ties within the same preference broken by the newest EffectiveFrom.
    public const string GET_APPLICABLE_VERSIONS = @"
        WITH Ranked AS (
            SELECT
                v.AGREEMENTVERSIONID AS AgreementVersionId,
                v.AGREEMENTTYPEID    AS AgreementTypeId,
                v.VERSIONLABEL       AS VersionLabel,
                v.JURISDICTIONCODE   AS JurisdictionCode,
                v.LANGUAGEID         AS LanguageId,
                v.CONTENTREF         AS ContentRef,
                v.CONTENTHASH        AS ContentHash,
                v.EFFECTIVEFROM      AS EffectiveFrom,
                v.VERSIONSTATUS      AS VersionStatus,
                v.COUNTERPARTYTYPE   AS CounterpartyType,
                v.COUNTERPARTYREF    AS CounterpartyRef,
                v.VERSION            AS Version,
                v.STATUS             AS Status,
                v.SORTORDER          AS SortOrder,
                v.CREATEDBYID        AS CreatedById,
                v.CREATEDON          AS CreatedOn,
                v.MODIFIEDBYID       AS ModifiedById,
                v.MODIFIEDON         AS ModifiedOn,
                v.SOURCETYPE         AS SourceType,
                CASE WHEN v.JURISDICTIONCODE = @JurisdictionCode THEN 0 ELSE 1 END AS Pref,
                ROW_NUMBER() OVER (
                    PARTITION BY v.AGREEMENTTYPEID
                    ORDER BY CASE WHEN v.JURISDICTIONCODE = @JurisdictionCode THEN 0 ELSE 1 END,
                             v.EFFECTIVEFROM DESC
                ) AS RowNum
            FROM MAGREEMENTVERSION v
            INNER JOIN MAGREEMENTTYPE t ON t.AGREEMENTTYPEID = v.AGREEMENTTYPEID
            WHERE t.TYPECODE IN @TypeCodes
              AND v.VERSIONSTATUS = 1
              AND (v.JURISDICTIONCODE = @JurisdictionCode OR v.JURISDICTIONCODE IS NULL)
        )
        SELECT
            AgreementVersionId, AgreementTypeId, VersionLabel, JurisdictionCode, LanguageId,
            ContentRef, ContentHash, EffectiveFrom, VersionStatus, CounterpartyType, CounterpartyRef,
            Version, Status, SortOrder, CreatedById, CreatedOn, ModifiedById, ModifiedOn, SourceType
        FROM Ranked
        WHERE RowNum = 1";

    // Every currently-Published version of a RequiresIndividualAcceptance=1 type (e.g. an
    // Enterprise Clickwrap EULA) — one per type, same jurisdiction-preferring ROW_NUMBER shape
    // as GET_APPLICABLE_VERSIONS. Used to compute an individual user's pending-acceptance set at
    // login (tracker §50.3's own "individual-user first-login gate" — the BLL subtracts whatever
    // this subject has already accepted, via GET_ACCEPTANCES_BY_SUBJECT).
    public const string GET_REQUIRED_INDIVIDUAL_VERSIONS = @"
        WITH Ranked AS (
            SELECT
                v.AGREEMENTVERSIONID AS AgreementVersionId,
                v.AGREEMENTTYPEID    AS AgreementTypeId,
                v.VERSIONLABEL       AS VersionLabel,
                v.JURISDICTIONCODE   AS JurisdictionCode,
                v.LANGUAGEID         AS LanguageId,
                v.CONTENTREF         AS ContentRef,
                v.CONTENTHASH        AS ContentHash,
                v.EFFECTIVEFROM      AS EffectiveFrom,
                v.VERSIONSTATUS      AS VersionStatus,
                v.COUNTERPARTYTYPE   AS CounterpartyType,
                v.COUNTERPARTYREF    AS CounterpartyRef,
                v.VERSION            AS Version,
                v.STATUS             AS Status,
                v.SORTORDER          AS SortOrder,
                v.CREATEDBYID        AS CreatedById,
                v.CREATEDON          AS CreatedOn,
                v.MODIFIEDBYID       AS ModifiedById,
                v.MODIFIEDON         AS ModifiedOn,
                v.SOURCETYPE         AS SourceType,
                ROW_NUMBER() OVER (
                    PARTITION BY v.AGREEMENTTYPEID
                    ORDER BY CASE WHEN v.JURISDICTIONCODE = @JurisdictionCode THEN 0 ELSE 1 END,
                             v.EFFECTIVEFROM DESC
                ) AS RowNum
            FROM MAGREEMENTVERSION v
            INNER JOIN MAGREEMENTTYPE t ON t.AGREEMENTTYPEID = v.AGREEMENTTYPEID
            WHERE t.REQUIRESINDIVIDUALACCEPTANCE = 1
              AND v.VERSIONSTATUS = 1
              AND (v.JURISDICTIONCODE = @JurisdictionCode OR v.JURISDICTIONCODE IS NULL)
        )
        SELECT
            AgreementVersionId, AgreementTypeId, VersionLabel, JurisdictionCode, LanguageId,
            ContentRef, ContentHash, EffectiveFrom, VersionStatus, CounterpartyType, CounterpartyRef,
            Version, Status, SortOrder, CreatedById, CreatedOn, ModifiedById, ModifiedOn, SourceType
        FROM Ranked
        WHERE RowNum = 1";

    public const string GET_VERSION_BY_ID = @"
        SELECT
            AGREEMENTVERSIONID AS AgreementVersionId,
            AGREEMENTTYPEID    AS AgreementTypeId,
            VERSIONLABEL       AS VersionLabel,
            JURISDICTIONCODE   AS JurisdictionCode,
            LANGUAGEID         AS LanguageId,
            CONTENTREF         AS ContentRef,
            CONTENTHASH        AS ContentHash,
            EFFECTIVEFROM      AS EffectiveFrom,
            VERSIONSTATUS      AS VersionStatus,
            COUNTERPARTYTYPE   AS CounterpartyType,
            COUNTERPARTYREF    AS CounterpartyRef,
            VERSION            AS Version,
            STATUS             AS Status,
            SORTORDER          AS SortOrder,
            CREATEDBYID        AS CreatedById,
            CREATEDON          AS CreatedOn,
            MODIFIEDBYID       AS ModifiedById,
            MODIFIEDON         AS ModifiedOn,
            SOURCETYPE         AS SourceType
        FROM MAGREEMENTVERSION
        WHERE AGREEMENTVERSIONID = @AgreementVersionId";

    // Admin authoring list (tracker §51.13) — every version ever created for one type, newest
    // EffectiveFrom first, so Draft/Published/Superseded/Retired history is all visible at once
    // (unlike GET_APPLICABLE_VERSIONS/GET_REQUIRED_INDIVIDUAL_VERSIONS, which only surface the
    // one currently-Published row per jurisdiction).
    public const string GET_VERSION_LIST_BY_TYPE = @"
        SELECT
            AGREEMENTVERSIONID AS AgreementVersionId,
            AGREEMENTTYPEID    AS AgreementTypeId,
            VERSIONLABEL       AS VersionLabel,
            JURISDICTIONCODE   AS JurisdictionCode,
            LANGUAGEID         AS LanguageId,
            CONTENTREF         AS ContentRef,
            CONTENTHASH        AS ContentHash,
            EFFECTIVEFROM      AS EffectiveFrom,
            VERSIONSTATUS      AS VersionStatus,
            COUNTERPARTYTYPE   AS CounterpartyType,
            COUNTERPARTYREF    AS CounterpartyRef,
            VERSION            AS Version,
            STATUS             AS Status,
            SORTORDER          AS SortOrder,
            CREATEDBYID        AS CreatedById,
            CREATEDON          AS CreatedOn,
            MODIFIEDBYID       AS ModifiedById,
            MODIFIEDON         AS ModifiedOn,
            SOURCETYPE         AS SourceType
        FROM MAGREEMENTVERSION
        WHERE AGREEMENTTYPEID = @AgreementTypeId
        ORDER BY EFFECTIVEFROM DESC";

    public const string INSERT_VERSION = @"
        INSERT INTO MAGREEMENTVERSION
            (AGREEMENTVERSIONID, AGREEMENTTYPEID, VERSIONLABEL, JURISDICTIONCODE, LANGUAGEID,
             CONTENTREF, CONTENTHASH, EFFECTIVEFROM, VERSIONSTATUS, COUNTERPARTYTYPE, COUNTERPARTYREF,
             VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE)
        VALUES
            (@AgreementVersionId, @AgreementTypeId, @VersionLabel, @JurisdictionCode, @LanguageId,
             @ContentRef, @ContentHash, @EffectiveFrom, @VersionStatus, @CounterpartyType, @CounterpartyRef,
             @Version, @Status, @SortOrder, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType)";

    // Immutable-once-Published guard is enforced in the BLL (mirrors UpgradePackageBLL.Save's
    // own convention) — this UPDATE only ever fires from a Draft row, never from Published.
    public const string UPDATE_VERSION_STATUS = @"
        UPDATE MAGREEMENTVERSION
        SET VERSIONSTATUS = @VersionStatus, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn
        WHERE AGREEMENTVERSIONID = @AgreementVersionId";

    // Draft → Published specifically — sets VERSIONSTATUS and stamps CONTENTHASH (computed at
    // publish time, not draft time, since ContentRef may still change while Draft) together in
    // one write, so the hash is never left uncommitted after a successful publish call.
    public const string PUBLISH_VERSION = @"
        UPDATE MAGREEMENTVERSION
        SET VERSIONSTATUS = @VersionStatus, CONTENTHASH = @ContentHash,
            MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn
        WHERE AGREEMENTVERSIONID = @AgreementVersionId";

    public const string INSERT_ACCEPTANCE = @"
        INSERT INTO LAGREEMENTACCEPTANCE
            (AGREEMENTACCEPTANCEID, SUBJECTTYPE, SUBJECTID, AGREEMENTVERSIONID, ACCEPTEDON,
             IPADDRESS, USERAGENT, ACCEPTANCEMETHOD, CORRELATIONKEY, COUNTERSIGNEDBYUSERID,
             CREATEDBYID, CREATEDON, TENANTID)
        VALUES
            (@AgreementAcceptanceId, @SubjectType, @SubjectId, @AgreementVersionId, @AcceptedOn,
             @IpAddress, @UserAgent, @AcceptanceMethod, @CorrelationKey, @CounterSignedByUserId,
             @CreatedById, @CreatedOn, @TenantId)";

    public const string GET_ACCEPTANCES_BY_SUBJECT = @"
        SELECT
            AGREEMENTACCEPTANCEID AS AgreementAcceptanceId,
            SUBJECTTYPE           AS SubjectType,
            SUBJECTID             AS SubjectId,
            AGREEMENTVERSIONID    AS AgreementVersionId,
            ACCEPTEDON            AS AcceptedOn,
            IPADDRESS             AS IpAddress,
            USERAGENT             AS UserAgent,
            ACCEPTANCEMETHOD      AS AcceptanceMethod,
            CORRELATIONKEY        AS CorrelationKey,
            COUNTERSIGNEDBYUSERID AS CounterSignedByUserId,
            CREATEDBYID           AS CreatedById,
            CREATEDON             AS CreatedOn,
            TENANTID              AS TenantId
        FROM LAGREEMENTACCEPTANCE
        WHERE SUBJECTTYPE = @SubjectType AND SUBJECTID = @SubjectId
        ORDER BY ACCEPTEDON DESC";
}