using Dapper;
using GB5Shared.ListQuery;
using MMDAL.DTO.SupplyGroup;

namespace MMDAL.Query.SupplyGroup
{
    // ─────────────────────────────────────────────────────────────────────
    //  SupplyGroupQB  – SQL for all SupplyGroup operations
    //
    //  Rules:
    //    • Const strings for single-record Get / Save / Update / Delete
    //    • BuildSupplyGroupList returns (sql, params) for paged list
    //    • QueryContext + SqlClauses + ISqlDialect for multi-DB paged query
    //    • No user-supplied values concatenated into SQL strings
    //
    //  Indexes required:
    //    MSUPPLYGROUP:       (TENANTID, STATUS) INCLUDE (SUPPLYGROUPCODE, SUPPLYGROUPNAME, SORTORDER)
    //    MSUPPLYGROUPDETAIL: (SUPPLYGROUPID, SLNO)
    // ─────────────────────────────────────────────────────────────────────
    public static class SupplyGroupQB
    {
        // ── Get single supply group (header only — detail loaded separately) ──

        public const string GET_SUPPLYGROUP = @"
SELECT
    SG.SUPPLYGROUPID     AS SupplyGroupId,
    SG.SUPPLYGROUPCODE   AS SupplyGroupCode,
    SG.SUPPLYGROUPNAME   AS SupplyGroupName,
    SG.REMARKS           AS SupplyGroupRemarks,
    SG.SORTORDER         AS SupplyGroupSortOrder,
    SG.STATUS            AS SupplyGroupStatus,
    SG.VERSION           AS SupplyGroupVersion,
    SG.SOURCETYPE        AS SupplyGroupSourceType,
    SG.TENANTID          AS TenantId,
    SG.CREATEDBYID       AS SupplyGroupCreatedById,
    SG.CREATEDON         AS SupplyGroupCreatedOn,
    CB.USERNAME          AS SupplyGroupCreatedByName,
    SG.MODIFIEDBYID      AS SupplyGroupModifiedById,
    SG.MODIFIEDON        AS SupplyGroupModifiedOn,
    MB.USERNAME          AS SupplyGroupModifiedByName
FROM  MSUPPLYGROUP SG
LEFT JOIN MUSER CB ON CB.USERID = SG.CREATEDBYID
LEFT JOIN MUSER MB ON MB.USERID = SG.MODIFIEDBYID
WHERE SG.SUPPLYGROUPID = @supplygroupid
AND   SG.TENANTID      = @TenantId;";

        // ── Get detail rows for a supply group ──

        public const string GET_SUPPLYGROUPDETAIL = @"
SELECT
    SGD.SUPPLYGROUPDETAILID  AS SupplyGroupDetailId,
    SGD.SUPPLYGROUPID        AS SupplyGroupId,
    SGD.SLNO                 AS SupplyGroupDetailSlNo,
    SGD.OUID                 AS OuId,
    OU.OUCODE                AS OuCode,
    OU.OUNAME                AS OuName,
    SGD.PARTYBRANCHID        AS PartyBranchId,
    PB.PARTYBRANCHCODE       AS PartyBranchCode,
    PB.PARTYBRANCHNAME       AS PartyBranchName,
    SGD.PARTICULARS          AS SupplyGroupDetailParticulars,
    SGD.SHAREPERCENT         AS SupplyGroupDetailSharePercent
FROM  MSUPPLYGROUPDETAIL SGD
LEFT JOIN MOU          OU ON OU.OUID           = SGD.OUID
LEFT JOIN MPARTYBRANCH PB ON PB.PARTYBRANCHID  = SGD.PARTYBRANCHID
INNER JOIN MSUPPLYGROUP SG ON SG.SUPPLYGROUPID = SGD.SUPPLYGROUPID
WHERE SGD.SUPPLYGROUPID = @supplygroupid
AND   SG.TENANTID       = @TenantId
ORDER BY SGD.SLNO;";

        // ── Insert header ──

        public const string SAVE_SUPPLYGROUP = @"
INSERT INTO MSUPPLYGROUP
(
    SUPPLYGROUPID,
    SUPPLYGROUPCODE,
    SUPPLYGROUPNAME,
    REMARKS,
    SORTORDER,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SOURCETYPE,
    TENANTID
)
VALUES
(
    @SupplyGroupId,
    @SupplyGroupCode,
    @SupplyGroupName,
    @SupplyGroupRemarks,
    @SupplyGroupSortOrder,
    @SupplyGroupStatus,
    @SupplyGroupVersion,
    @SupplyGroupCreatedById,
    @SupplyGroupCreatedOn,
    @SupplyGroupModifiedById,
    @SupplyGroupModifiedOn,
    @SupplyGroupSourceType,
    @TenantId
);";

        // ── Update header ──

        public const string UPDATE_SUPPLYGROUP = @"
UPDATE MSUPPLYGROUP
SET
    SUPPLYGROUPCODE = @SupplyGroupCode,
    SUPPLYGROUPNAME = @SupplyGroupName,
    REMARKS         = @SupplyGroupRemarks,
    SORTORDER       = @SupplyGroupSortOrder,
    STATUS          = @SupplyGroupStatus,
    VERSION         = @SupplyGroupVersion,
    MODIFIEDBYID    = @SupplyGroupModifiedById,
    MODIFIEDON      = @SupplyGroupModifiedOn,
    SOURCETYPE      = @SupplyGroupSourceType
WHERE SUPPLYGROUPID = @SupplyGroupId;";

        // ── Delete all detail rows for a supply group (used before reinsert on update) ──

        public const string DELETE_SUPPLYGROUPDETAIL = @"
DELETE FROM MSUPPLYGROUPDETAIL
WHERE SUPPLYGROUPID = @supplygroupid;";

        // ── Insert one detail row ──

        public const string SAVE_SUPPLYGROUPDETAIL = @"
INSERT INTO MSUPPLYGROUPDETAIL
(
    SUPPLYGROUPDETAILID,
    SUPPLYGROUPID,
    SLNO,
    OUID,
    PARTYBRANCHID,
    PARTICULARS,
    SHAREPERCENT
)
VALUES
(
    @SupplyGroupDetailId,
    @SupplyGroupId,
    @SupplyGroupDetailSlNo,
    @OuId,
    @PartyBranchId,
    @SupplyGroupDetailParticulars,
    @SupplyGroupDetailSharePercent
);";

        // ── Soft delete header (STATUS = 2 = Deleted) ──

        public const string DELETE_SUPPLYGROUP = @"
UPDATE MSUPPLYGROUP
SET    STATUS = 2
WHERE  SUPPLYGROUPID = @supplygroupid;";

        // ── Picklist (select list) ──

        public const string GET_SELECTLIST_SUPPLYGROUP = @"
WITH PagedSupplyGroup AS (
    SELECT
        SG.SUPPLYGROUPID   AS Id,
        SG.SUPPLYGROUPCODE AS Code,
        SG.SUPPLYGROUPNAME AS Name,
        ROW_NUMBER() OVER (ORDER BY SG.SUPPLYGROUPNAME) AS RowNum
    FROM  MSUPPLYGROUP SG
    WHERE SG.STATUS = 1
)
SELECT Id, Code, Name
FROM   PagedSupplyGroup
WHERE  (@firstnumber = -1 AND @maxresult = -1)
    OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        // ── Paged list builder (Pattern A / SqlListHandler) ─────────────────

        /// <summary>
        /// Builds the paged supply group list query.
        /// Filters active records; optionally filters by SearchText on code or name.
        /// Indexes required: MSUPPLYGROUP (TENANTID, STATUS) INCLUDE (SUPPLYGROUPCODE, SUPPLYGROUPNAME, SORTORDER)
        /// </summary>
        public static (string Sql, DynamicParameters Params) BuildSupplyGroupList(
            SupplyGroupListCriteria c, ISqlDialect d)
        {
            var ctx = new QueryContext(d)
                .Register("supplygroup", "sg");

            var (whereSql, whereParams) = SqlClause.AsWhere(new[]
            {
                // Only active supply groups
                new SqlClause(
                    $"{ctx.Col("supplygroup", "STATUS")} = 1",
                    new DynamicParameters()),

                // Optional search on code or name
                string.IsNullOrWhiteSpace(c.SearchText)
                    ? SqlClause.Empty
                    : new SqlClause(
                        $"({ctx.Col("supplygroup", "SUPPLYGROUPCODE")} {d.LikeCaseOp} {d.Param("searchText")}" +
                        $" OR {ctx.Col("supplygroup", "SUPPLYGROUPNAME")} {d.LikeCaseOp} {d.Param("searchText")})",
                        new DynamicParameters(new { searchText = $"%{c.SearchText}%" })),
            });

            var (pagingSql, pagingParams) = SqlClauses.Paging(ctx, c, orderBy: "sg.SUPPLYGROUPID DESC");

            var sql = $@"
SELECT {d.TotalCountExpr()},
       sg.SUPPLYGROUPID   AS SupplyGroupId,
       sg.SUPPLYGROUPCODE AS SupplyGroupCode,
       sg.SUPPLYGROUPNAME AS SupplyGroupName,
       sg.REMARKS         AS Remarks,
       sg.STATUS          AS Status,
       sg.SORTORDER       AS SortOrder
FROM   MSUPPLYGROUP sg
{whereSql}
{pagingSql}";

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