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

namespace DXPDAL.VendorPo;

// Required indexes (tenant DB — advisory only, DXP does not own this database):
//   TMMHEAD: (PARTYID, BIZTRANSACTIONTYPEID, OUID) INCLUDE (DOCUMENTDATE, STATUS)
//
// Reads TMMHEAD/TMMDETAIL directly — GB5 modules share one physical per-tenant database, so
// this is the same kind of direct cross-module read DXP already does against
// MPARTY/MPARTYBRANCH/MPARTYACCOUNTOU (see the Phase 1.3 migration header for the full
// read-vs-write rationale — Stock Reservation, a write with real business logic, goes over
// HTTP to MM's own endpoint instead).
//
// NOTE: IsAcknowledged/AcknowledgedOn are NOT populated by this query — they live in DXPDb
// (TDXPPOACKNOWLEDGEMENT), a different physical database from the tenant's TMMHEAD. VendorPoBLL
// runs a second query against DXPDb for the returned MMHeadIds and merges the two in memory.
public sealed class VendorPoQB : IQueryBuilder<VendorPoListQuery, VendorPoDTO>
{
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["date"]   = "H.DOCUMENTDATE",
            ["number"] = "H.DOCUMENTNUMBER",
            ["value"]  = "H.TOTALNETVALUE"
        };

    public (string Sql, DynamicParameters Params) Build(VendorPoListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var c = query.Criteria;
        var orderBy = SqlClauses.OrderBy(c.SortBy, c.SortDesc, _sort, "H.DOCUMENTDATE DESC");

        var ctx = new QueryContext(dialect).Register("document", "H");

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            new SqlClause("H.PARTYID = @PartyId", new DynamicParameters(new { PartyId = c.PartyId })),
            new SqlClause("H.BIZTRANSACTIONTYPEID = @BizTransactionTypeId",
                new DynamicParameters(new { BizTransactionTypeId = c.BizTransactionTypeId })),
            SqlClauses.OUFilterSingle(ctx, c.OUId),
            SqlClauses.DocumentSearch(ctx, c.SearchText)
        });

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

        var sql = $@"
SELECT H.DOCUMENTID           AS MMHeadId,
       H.BIZTRANSACTIONTYPEID  AS BizTransactionTypeId,
       H.OUID                  AS OUId,
       H.DOCUMENTDATE          AS MMHeadDate,
       H.DOCUMENTNUMBER        AS MMHeadNumber,
       H.PARTYID               AS PartyId,
       H.PARTYBRANCHID         AS PartyBranchId,
       H.TOTALNETVALUE         AS MMHeadTotalNetValue,
       H.STATUS                AS MMHeadStatus,
       H.VERSION               AS MMHeadVersion,
       {dialect.TotalCountExpr()}
FROM   TMMHEAD H
{whereSql}
{pagingSql}";

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

    // ── Single PO detail (header + lines) — plain, non-paged, ANSI-safe SQL ──

    public const string GET_PO_HEADER = @"
SELECT H.DOCUMENTID           AS MMHeadId,
       H.BIZTRANSACTIONTYPEID  AS BizTransactionTypeId,
       H.OUID                  AS OUId,
       H.DOCUMENTDATE          AS MMHeadDate,
       H.DOCUMENTNUMBER        AS MMHeadNumber,
       H.PARTYID               AS PartyId,
       H.PARTYBRANCHID         AS PartyBranchId,
       H.TOTALNETVALUE         AS MMHeadTotalNetValue,
       H.STATUS                AS MMHeadStatus,
       H.VERSION               AS MMHeadVersion
FROM   TMMHEAD H
WHERE  H.DOCUMENTID = @MMHeadId
  AND  H.PARTYID    = @PartyId";
        // PartyId filter here is a defence-in-depth check — never trust that a caller-supplied
        // MMHeadId actually belongs to them without re-checking PartyId server-side.

    public const string GET_PO_DETAILS = @"
SELECT D.DOCUMENTDETAILID              AS MMDetailId,
       D.DOCUMENTID                     AS DocumentId,
       D.SLNO                           AS MMDetailSlNo,
       D.ITEMID                         AS ItemId,
       D.TRANSACTIONQUANTITY            AS MMDetailTransactionQuantity,
       D.RATE                           AS MMDetailRate,
       D.TRANSACTIONUOMID               AS TransactionUOMId
FROM   TMMDETAIL D
WHERE  D.DOCUMENTID = @MMHeadId
ORDER BY D.SLNO";

    // ── OU-scoping check (MPARTYACCOUNTOU) ──────────────────────────────────

    public const string GET_PARTY_OU_LINKS = @"
SELECT PO.PARTYACCOUNTOUID AS PartyOUId,
       PO.PARTYID           AS PartyId,
       PO.OUID              AS OUId,
       PO.PARTYACCOUNTOUTYPE AS PartyOUType
FROM   MPARTYACCOUNTOU PO
WHERE  PO.PARTYID = @PartyId
  AND  PO.STATUS  = 1";

    // ── Post-creation lookup (ASN, Phase 1.4) ───────────────────────────────
    // MM's SaveMMHead response is a plain success message string, not the new DocumentId (see
    // MmDocumentSaveOutcome's doc comment) — DXP looks the new row up itself immediately after,
    // by the reference/type/party combo it just set. Defensible since DXP controls what it wrote
    // as MMHeadReferenceNumber; ordered DESC + TOP 1 as a tie-breaker if this is ever ambiguous.
    public const string FIND_NEWLY_CREATED_DOCUMENT = @"
SELECT H.DOCUMENTID           AS MMHeadId,
       H.BIZTRANSACTIONTYPEID  AS BizTransactionTypeId,
       H.OUID                  AS OUId,
       H.DOCUMENTDATE          AS MMHeadDate,
       H.DOCUMENTNUMBER        AS MMHeadNumber,
       H.PARTYID               AS PartyId,
       H.PARTYBRANCHID         AS PartyBranchId,
       H.TOTALNETVALUE         AS MMHeadTotalNetValue,
       H.STATUS                AS MMHeadStatus,
       H.VERSION               AS MMHeadVersion
FROM   TMMHEAD H
WHERE  H.REFERENCENUMBER      = @ReferenceNumber
  AND  H.BIZTRANSACTIONTYPEID = @BizTransactionTypeId
  AND  H.PARTYID               = @PartyId
ORDER BY H.DOCUMENTID DESC";
}