using Dapper;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.ListQuery;
using IDMSDAL.DTOs;

namespace IDMSDAL.QueryBuilders;

// Required indexes:
//   LSIGNOFFREGISTER: (ENGAGEMENTID, TENANTID) INCLUDE (SIGNEDON, DOMAINCODE)
//   LSIGNOFFREGISTER: (ENGAGEMENTID, TENANTID, DOMAINCODE)
//
// Note: Table is INSERT-only at DB level (trg_LSIGNOFFREGISTER_Immutable prevents UPDATE/DELETE).
// SIGNOFFID is IDENTITY — use ExecuteInsertReturnIdentityAsync on INSERT.

// ── LSIGNOFFREGISTER list query builder ──────────────────────────────────────

public sealed class SignOffQB : IQueryBuilder<SignOffListQuery, SignOffListDTO>
{
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["signedon"]     = "SO.SIGNEDON",
            ["domaincode"]   = "SO.DOMAINCODE",
            ["signofftype"]  = "SO.SIGNOFFTYPE"
        };

    public (string Sql, DynamicParameters Params) Build(SignOffListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc,
            _sort, "SO.SIGNEDON DESC");
        return BuildSignOffList(query.Criteria, login, dialect, orderBy);
    }

    public static (string Sql, DynamicParameters Params) BuildSignOffList(
        SignOffListCriteria c, LoginDTO login, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d).Register("signoff", "SO");

        var tenantParams = new DynamicParameters();
        tenantParams.Add("TenantId", login.ClientId);

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            new SqlClause("SO.TENANTID = @TenantId", tenantParams),
            SqlClauses.ExactInt(ctx,   c.EngagementId, "signoff", "ENGAGEMENTID"),
            SqlClauses.ExactString(ctx, c.DomainCode,   "signoff", "DOMAINCODE"),
        });

        var (pagingSql, pagingParams) = SqlClauses.Paging(ctx, c, orderBy);

        string sql = $@"
SELECT {d.TotalCountExpr()},
       SO.SIGNOFFID         AS SignOffId,
       SO.DOMAINCODE        AS DomainCode,
       SO.SIGNOFFTYPE       AS SignOffType,
       SO.SIGNOFFNAME       AS SignOffName,
       SO.SIGNEDBYNAME      AS SignedByName,
       SO.SIGNEDON          AS SignedOn,
       SO.ISDIGITALLYSIGNED AS IsDigitalSignature
FROM   LSIGNOFFREGISTER SO
{whereSql}
{pagingSql}";

        return (sql, SqlClause.Merge(whereParams, pagingParams));
    }

    // ── SQL constants ─────────────────────────────────────────────────────────
    // INSERT-only table — no UPDATE or DELETE ever.

    public const string GET_BY_ID = @"
SELECT SO.SIGNOFFID          AS SignOffId,
       SO.ENGAGEMENTID       AS EngagementId,
       SO.TENANTID           AS TenantId,
       SO.DOMAINCODE         AS DomainCode,
       SO.SIGNOFFTYPE        AS SignOffType,
       SO.SIGNOFFNAME        AS SignOffName,
       SO.SIGNEDITEMS        AS SignedItems,
       SO.CONTENTSNAPSHOT    AS ContentSnapshot,
       SO.CONTENTHASH        AS ContentHash,
       SO.SIGNEDBYID         AS SignedById,
       SO.SIGNEDBYNAME       AS SignedByName,
       SO.SIGNEDON           AS SignedOn,
       SO.ISDIGITALLYSIGNED  AS IsDigitallySigned,
       SO.PORTALSIGNATUREID  AS PortalSignatureId,
       SO.DELIVERYMANAGERID  AS DeliveryManagerId,
       SO.CREATEDBYID        AS CreatedById,
       SO.CREATEDON          AS CreatedOn
FROM   LSIGNOFFREGISTER SO
WHERE  SO.SIGNOFFID  = @SignOffId
  AND  SO.TENANTID   = @TenantId";

    public const string GET_BY_ENGAGEMENT = @"
SELECT SO.SIGNOFFID          AS SignOffId,
       SO.ENGAGEMENTID       AS EngagementId,
       SO.TENANTID           AS TenantId,
       SO.DOMAINCODE         AS DomainCode,
       SO.SIGNOFFTYPE        AS SignOffType,
       SO.SIGNOFFNAME        AS SignOffName,
       SO.SIGNEDITEMS        AS SignedItems,
       SO.CONTENTSNAPSHOT    AS ContentSnapshot,
       SO.CONTENTHASH        AS ContentHash,
       SO.SIGNEDBYID         AS SignedById,
       SO.SIGNEDBYNAME       AS SignedByName,
       SO.SIGNEDON           AS SignedOn,
       SO.ISDIGITALLYSIGNED  AS IsDigitallySigned,
       SO.PORTALSIGNATUREID  AS PortalSignatureId,
       SO.DELIVERYMANAGERID  AS DeliveryManagerId,
       SO.CREATEDBYID        AS CreatedById,
       SO.CREATEDON          AS CreatedOn
FROM   LSIGNOFFREGISTER SO
WHERE  SO.ENGAGEMENTID = @EngagementId
  AND  SO.TENANTID     = @TenantId
ORDER BY SO.SIGNEDON DESC";

    // SIGNOFFID is IDENTITY — excluded from INSERT column list.
    // Use ExecuteInsertReturnIdentityAsync to retrieve the generated ID.
    public const string INSERT = @"
INSERT INTO LSIGNOFFREGISTER
    (ENGAGEMENTID, TENANTID, DOMAINCODE, SIGNOFFTYPE, SIGNOFFNAME,
     SIGNEDITEMS, CONTENTSNAPSHOT, CONTENTHASH,
     SIGNEDBYID, SIGNEDBYNAME, SIGNEDON,
     ISDIGITALLYSIGNED, PORTALSIGNATUREID, DELIVERYMANAGERID,
     CREATEDBYID, CREATEDON)
VALUES
    (@EngagementId, @TenantId, @DomainCode, @SignOffType, @SignOffName,
     @SignedItems, @ContentSnapshot, @ContentHash,
     @SignedById, @SignedByName, @SignedOn,
     @IsDigitallySigned, @PortalSignatureId, @DeliveryManagerId,
     @CreatedById, @CreatedOn)";
}