using System;

namespace PayRollDAL.Query.GateEntry
{
    // ─────────────────────────────────────────────────────────────────────
    //  GateEntryQB  – SQL for all GateEntry operations (TGATEENTRY / TGATEENTRYDETAIL)
    //
    //  Rules:
    //    • Const strings only — no SQL concatenation
    //    • All params are named (@param) — no string interpolation
    //    • TGATEENTRYDETAIL.GATEENTRYDETAILID is IDENTITY — never inserted manually
    //    • Detail update strategy: DELETE existing rows then re-INSERT
    //
    //  Indexes required:
    //    TGATEENTRY:       (OUID, STATUS) INCLUDE (GATEENTRYNUMBER, GATEENTRYDATE)
    //                      UNIQUE (GATEENTRYNUMBER, BIZTRANSACTIONTYPEID, OUID, PERIODID)
    //    TGATEENTRYDETAIL: (GATEENTRYID, SLNO)
    // ─────────────────────────────────────────────────────────────────────

    public static class GateEntryQB
    {
        // ── Get single gate entry (header only) ─────────────────────────

        public const string GET_GATEENTRY = @"
SELECT
    GE.GATEENTRYID                AS GateEntryId,
    GE.GATEENTRYNUMBER            AS GateEntryNumber,
    GE.GATEENTRYDATE              AS GateEntryDate,
    GE.DATEIN                     AS GateEntryDateIn,
    GE.TIMEIN                     AS GateEntryTimeIn,
    GE.DATEOUT                    AS GateEntryDateOut,
    GE.TIMEOUT                    AS GateEntryTimeOut,
    GE.DURATION                   AS GateEntryDuration,
    GE.NATURE                     AS GateEntryNature,
    GE.REFERENCENUMBER            AS GateEntryReferenceNumber,
    GE.REFERENCEDATE              AS GateEntryReferenceDate,
    GE.MATERIALTYPE               AS GateEntryMaterialType,
    GE.DESCRIPTION                AS GateEntryDescription,
    GE.ENTRYSTATUS                AS GateEntryEntryStatus,
    GE.FIRSTWEIGHTMENT            AS GateEntryFirstWeightment,
    GE.SECONDWEIGHTMENT           AS GateEntrySecondWeightment,
    GE.STATUS                     AS GateEntryStatus,
    GE.VERSION                    AS GateEntryVersion,
    GE.LOADINGWEIGHT              AS GateEntryLoadingWeight,
    GE.GROSSWEIGHT                AS GateEntryGrossWeight,
    GE.RATEFINALIZE               AS GateEntryRateFinalize,
    GE.VALUE                      AS GateEntryValues,
    GE.UNLOADINGWEIGHT            AS GateEntryUnloadingWeight,
    GE.DOCUMENTIDS                AS GateEntryDocumentIds,
    GE.DOCUMENTNUMBERS            AS GateEntryDocumentNumbers,
    GE.LRNUMBER                   AS GateEntryLRNumber,
    GE.LRDATE                     AS GateEntryLRDate,
    GE.REMARKS                    AS GateEntryRemarks,
    GE.CREATEDBYID                AS GateEntryCreatedById,
    GE.CREATEDON                  AS GateEntryCreatedOn,
    CB.USERNAME                   AS GateEntryCreatedByName,
    GE.MODIFIEDBYID               AS GateEntryModifiedById,
    GE.MODIFIEDON                 AS GateEntryModifiedOn,
    MB.USERNAME                   AS GateEntryModifiedByName,

    GE.TRANSPORTERID              AS TransporterId,
    TP.PARTYCODE                  AS TransporterCode,
    TP.PARTYNAME                  AS TransporterName,

    GE.DRIVERID                   AS DriverId,
    DR.LICENCENUMBER              AS DriverCode,
    DR.DRIVERNAME                 AS DriverDriverName,

    GE.VEHICLETYPEID              AS VehicleTypeId,
    VT.GCMCODE                    AS VehicleTypeCode,
    VT.GCMNAME                    AS VehicleTypeName,

    GE.VEHICLEID                  AS VehicleId,
    VH.VEHICLENUMBER              AS VehicleNumber,

    GE.PARTYID                    AS PartyId,
    PT.PARTYCODE                  AS PartyCode,
    PT.PARTYNAME                  AS PartyName,
    PT.ISACCOUNT                  AS PartyIsAccount,

    GE.VILLAGEID                  AS VillageId,
    VL.VILLAGECODE                AS VillageCode,
    VL.VILLAGENAME                AS VillageName,

    GE.CITYID                     AS CityId,
    CT.CITYCODE                   AS CityCode,
    CT.CITYNAME                   AS CityName,

    GE.STATEID                    AS StateId,
    ST.REGIONCODE                 AS StateCode,
    ST.REGIONNAME                 AS StateName,

    GE.ITEMID                     AS ItemId,
    IT.ITEMCODE                   AS ItemCode,
    IT.ITEMNAME                   AS ItemName,

    GE.ITEMCATEGORYID             AS GateEntryItemCategoryId,
    IC.ITEMCATEGORYCODE           AS GateEntryItemCategoryCode,
    IC.ITEMCATEGORYNAME           AS GateEntryItemCategoryName,

    GE.ITEMSUBCATEGORYID          AS GateEntryItemSubCategoryId,
    ISC.ITEMSUBCATEGORYCODE       AS GateEntryItemSubCategoryCode,
    ISC.ITEMSUBCATEGORYNAME       AS GateEntryItemSubCategoryName,

    GE.BIZTRANSACTIONTYPEID       AS BIZTransactionTypeId,
    BTT.BIZTRANSACTIONTYPECODE    AS BIZTransactionTypeCode,
    BTT.BIZTRANSACTIONTYPENAME    AS BIZTransactionTypeName,

    GE.ASSIGNEDSTOREID            AS AssignedStoreId,
    MS.STORECODE                  AS AssignedStoreCode,
    MS.STORENAME                  AS AssignedStoreName,

    GE.OUID                       AS OUId,
    OU.ORGANIZATIONUNITCODE       AS OUCode,
    OU.ORGANIZATIONUNITNAME       AS OUName,

    GE.PERIODID                   AS PeriodId,
    PE.PERIODCODE                 AS PeriodCode,
    PE.PERIODNAME                 AS PeriodName

FROM TGATEENTRY GE
LEFT JOIN MUSER             CB  ON CB.USERID              = GE.CREATEDBYID
LEFT JOIN MUSER             MB  ON MB.USERID              = GE.MODIFIEDBYID
LEFT JOIN MPARTY            TP  ON TP.PARTYID             = GE.TRANSPORTERID
LEFT JOIN MDRIVER           DR  ON DR.DRIVERID            = GE.DRIVERID
LEFT JOIN MGCM              VT  ON VT.GCMID               = GE.VEHICLETYPEID
LEFT JOIN MVEHICLE          VH  ON VH.VEHICLEID           = GE.VEHICLEID
LEFT JOIN MPARTY            PT  ON PT.PARTYID             = GE.PARTYID
LEFT JOIN MVILLAGE          VL  ON VL.VILLAGEID           = GE.VILLAGEID
LEFT JOIN MCITY             CT  ON CT.CITYID              = GE.CITYID
LEFT JOIN MREGION           ST  ON ST.REGIONID            = GE.STATEID
LEFT JOIN MITEM             IT  ON IT.ITEMID              = GE.ITEMID
LEFT JOIN MITEMCATEGORY     IC  ON IC.ITEMCATEGORYID      = GE.ITEMCATEGORYID
LEFT JOIN MITEMSUBCATEGORY  ISC ON ISC.ITEMSUBCATEGORYID  = GE.ITEMSUBCATEGORYID
LEFT JOIN MBIZTRANSACTIONTYPE BTT ON BTT.BIZTRANSACTIONTYPEID = GE.BIZTRANSACTIONTYPEID
LEFT JOIN MSTORE            MS  ON MS.STOREID             = GE.ASSIGNEDSTOREID
LEFT JOIN MORGANIZATIONUNIT OU  ON OU.OUID                = GE.OUID
LEFT JOIN MPERIOD           PE  ON PE.PERIODID            = GE.PERIODID
WHERE GE.GATEENTRYID = @gateentryid;";

        // ── Get detail rows for a gate entry ────────────────────────────

        public const string GET_GATEENTRYDETAIL = @"
SELECT
    GED.GATEENTRYDETAILID   AS GateEntryDetailId,
    GED.GATEENTRYID         AS GateEntryId,
    GED.SLNO                AS SlNo,
    GED.DOCUMENTID          AS DocumentId,
    GED.DOCUMENTNUMBER      AS DocumentNumber,
    GED.ITEMID              AS ItemId,
    GED.ITEMCODE            AS ItemCode,
    GED.ITEMNAME            AS ItemName,
    GED.UOMID               AS UOMId,
    GED.UOMNAME             AS UOMName,
    GED.LOTID               AS LotId,
    GED.LOTNUMBER           AS LotNumber,
    GED.LOTQUANTITY         AS LotQuantity,
    GED.PACKNUMBERID        AS PackNumberId,
    GED.PACKNUMBER          AS PackNumber,
    GED.PACKQUANTITY        AS PackQuantity,
    GED.PACKTYPE            AS PackType,
    GED.PACKID              AS PackId,
    GED.PACKCODE            AS PackCode,
    GED.PACKNAME            AS PackName,
    GED.MANUFACTURINGDATE   AS ManufacturingDate,
    GED.EXPIRYDATE          AS ExpiryDate,
    GED.ALLOCATIONID        AS AllocationId,
    GED.DOCUMENTVALUE       AS DocumentValue,
    GED.REMARKS             AS GateEntryDetailRemarks
FROM TGATEENTRYDETAIL GED
INNER JOIN TGATEENTRY GE ON GE.GATEENTRYID = GED.GATEENTRYID
WHERE GED.GATEENTRYID = @gateentryid
ORDER BY GED.SLNO;";

        // ── Insert header ────────────────────────────────────────────────

        public const string SAVE_GATEENTRY = @"
INSERT INTO TGATEENTRY
(
    GATEENTRYID,
    GATEENTRYNUMBER,
    GATEENTRYDATE,
    DATEIN,
    TIMEIN,
    DATEOUT,
    TIMEOUT,
    DURATION,
    NATURE,
    TRANSPORTERID,
    DRIVERID,
    VEHICLETYPEID,
    VEHICLEID,
    PARTYID,
    VILLAGEID,
    CITYID,
    STATEID,
    REFERENCENUMBER,
    REFERENCEDATE,
    MATERIALTYPE,
    ITEMID,
    ITEMCATEGORYID,
    ITEMSUBCATEGORYID,
    DESCRIPTION,
    ENTRYSTATUS,
    FIRSTWEIGHTMENT,
    SECONDWEIGHTMENT,
    VERSION,
    STATUS,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    BIZTRANSACTIONTYPEID,
    ASSIGNEDSTOREID,
    LOADINGWEIGHT,
    GROSSWEIGHT,
    RATEFINALIZE,
    VALUE,
    UNLOADINGWEIGHT,
    DOCUMENTIDS,
    DOCUMENTNUMBERS,
    OUID,
    PERIODID,
    LRNUMBER,
    LRDATE,
    REMARKS
)
VALUES
(
    @GateEntryId,
    @GateEntryNumber,
    @GateEntryDate,
    @GateEntryDateIn,
    @GateEntryTimeIn,
    @GateEntryDateOut,
    @GateEntryTimeOut,
    @GateEntryDuration,
    @GateEntryNature,
    @TransporterId,
    @DriverId,
    @VehicleTypeId,
    @VehicleId,
    @PartyId,
    @VillageId,
    @CityId,
    @StateId,
    @GateEntryReferenceNumber,
    @GateEntryReferenceDate,
    @GateEntryMaterialType,
    @ItemId,
    @GateEntryItemCategoryId,
    @GateEntryItemSubCategoryId,
    @GateEntryDescription,
    @GateEntryEntryStatus,
    @GateEntryFirstWeightment,
    @GateEntrySecondWeightment,
    @GateEntryVersion,
    @GateEntryStatus,
    @GateEntryCreatedById,
    @GateEntryCreatedOn,
    @GateEntryModifiedById,
    @GateEntryModifiedOn,
    @BIZTransactionTypeId,
    @AssignedStoreId,
    @GateEntryLoadingWeight,
    @GateEntryGrossWeight,
    @GateEntryRateFinalize,
    @GateEntryValues,
    @GateEntryUnloadingWeight,
    @GateEntryDocumentIds,
    @GateEntryDocumentNumbers,
    @OUId,
    @PeriodId,
    @GateEntryLRNumber,
    @GateEntryLRDate,
    @GateEntryRemarks
);";

        // ── Update header ────────────────────────────────────────────────

        public const string UPDATE_GATEENTRY = @"
UPDATE TGATEENTRY
SET
    DATEIN              = @GateEntryDateIn,
    TIMEIN              = @GateEntryTimeIn,
    DATEOUT             = @GateEntryDateOut,
    TIMEOUT             = @GateEntryTimeOut,
    DURATION            = @GateEntryDuration,
    NATURE              = @GateEntryNature,
    TRANSPORTERID       = @TransporterId,
    DRIVERID            = @DriverId,
    VEHICLETYPEID       = @VehicleTypeId,
    VEHICLEID           = @VehicleId,
    PARTYID             = @PartyId,
    VILLAGEID           = @VillageId,
    CITYID              = @CityId,
    STATEID             = @StateId,
    REFERENCENUMBER     = @GateEntryReferenceNumber,
    REFERENCEDATE       = @GateEntryReferenceDate,
    MATERIALTYPE        = @GateEntryMaterialType,
    ITEMID              = @ItemId,
    ITEMCATEGORYID      = @GateEntryItemCategoryId,
    ITEMSUBCATEGORYID   = @GateEntryItemSubCategoryId,
    DESCRIPTION         = @GateEntryDescription,
    ENTRYSTATUS         = @GateEntryEntryStatus,
    FIRSTWEIGHTMENT     = @GateEntryFirstWeightment,
    SECONDWEIGHTMENT    = @GateEntrySecondWeightment,
    VERSION             = @GateEntryVersion,
    STATUS              = @GateEntryStatus,
    MODIFIEDBYID        = @GateEntryModifiedById,
    MODIFIEDON          = @GateEntryModifiedOn,
    BIZTRANSACTIONTYPEID = @BIZTransactionTypeId,
    ASSIGNEDSTOREID     = @AssignedStoreId,
    LOADINGWEIGHT       = @GateEntryLoadingWeight,
    GROSSWEIGHT         = @GateEntryGrossWeight,
    RATEFINALIZE        = @GateEntryRateFinalize,
    VALUE               = @GateEntryValues,
    UNLOADINGWEIGHT     = @GateEntryUnloadingWeight,
    DOCUMENTIDS         = @GateEntryDocumentIds,
    DOCUMENTNUMBERS     = @GateEntryDocumentNumbers,
    LRNUMBER            = @GateEntryLRNumber,
    LRDATE              = @GateEntryLRDate,
    REMARKS             = @GateEntryRemarks
WHERE GATEENTRYID = @GateEntryId;";

        // ── Delete all detail rows for a gate entry (used before re-insert on update) ──

        public const string DELETE_GATEENTRYDETAIL = @"
DELETE FROM TGATEENTRYDETAIL
WHERE GATEENTRYID = @gateentryid;";

        // ── Insert one detail row (GATEENTRYDETAILID is IDENTITY — omitted) ──

        public const string SAVE_GATEENTRYDETAIL = @"
INSERT INTO TGATEENTRYDETAIL
(
    GATEENTRYID,
    SLNO,
    DOCUMENTID,
    DOCUMENTNUMBER,
    ITEMID,
    ITEMCODE,
    ITEMNAME,
    UOMID,
    UOMNAME,
    LOTID,
    LOTNUMBER,
    LOTQUANTITY,
    PACKNUMBERID,
    PACKNUMBER,
    PACKQUANTITY,
    PACKTYPE,
    PACKID,
    PACKCODE,
    PACKNAME,
    MANUFACTURINGDATE,
    EXPIRYDATE,
    ALLOCATIONID,
    DOCUMENTVALUE,
    REMARKS
)
VALUES
(
    @GateEntryId,
    @SlNo,
    @DocumentId,
    @DocumentNumber,
    @ItemId,
    @ItemCode,
    @ItemName,
    @UOMId,
    @UOMName,
    @LotId,
    @LotNumber,
    @LotQuantity,
    @PackNumberId,
    @PackNumber,
    @PackQuantity,
    @PackType,
    @PackId,
    @PackCode,
    @PackName,
    @ManufacturingDate,
    @ExpiryDate,
    @AllocationId,
    @DocumentValue,
    @GateEntryDetailRemarks
);";

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

        public const string DELETE_GATEENTRY = @"
UPDATE TGATEENTRY
SET    STATUS = 2
WHERE  GATEENTRYID = @gateentryid;";

        // ── Name / code-based lookup queries for detail resolution ───────
        // Used by ResolveGateEntryDetail endpoint to turn raw scanner text
        // into database IDs before the caller submits SaveGateEntry.
        //
        // Required indexes (should already exist on master tables):
        //   MPARTY:      (PARTYNAME, STATUS, TENANTID)
        //   MITEM:       (ITEMCODE,  STATUS, TENANTID)
        //   TLOT:        (LOTNUMBER, STATUS, TENANTID)
        //   TMMHEAD:     (DOCUMENTNUMBER, STATUS, TENANTID)
        //   MALLOCATION: (ALLOCATIONNAME, STATUS, TENANTID)
        //   MUOM:        (UOMNAME, STATUS)

        public const string GET_PARTY_BY_NAME = @"
SELECT TOP 1
    P.PARTYID   AS PartyId,
    P.PARTYCODE AS PartyCode,
    P.PARTYNAME AS PartyName
FROM MPARTY P
WHERE P.PARTYNAME = @PartyName
  AND P.STATUS    = 1;";

        public const string GET_PARTY_BY_NAME_LIKE = @"
SELECT TOP 1
    P.PARTYID   AS PartyId,
    P.PARTYCODE AS PartyCode,
    P.PARTYNAME AS PartyName
FROM MPARTY P
WHERE P.PARTYNAME LIKE @PartyPattern
  AND P.STATUS    = 1;";

        public const string GET_ITEM_BY_CODE = @"
SELECT TOP 1
    I.ITEMID   AS ItemId,
    I.ITEMCODE AS ItemCode,
    I.ITEMNAME AS ItemName
FROM MITEM I
WHERE I.ITEMCODE = @ItemCode ";

        public const string GET_ITEM_BY_NAME = @"
SELECT TOP 1
    I.ITEMID   AS ItemId,
    I.ITEMCODE AS ItemCode,
    I.ITEMNAME AS ItemName
FROM MITEM I
WHERE I.ITEMNAME = @ItemName ";

        public const string GET_LOT_BY_NUMBER = @"
SELECT TOP 1
    L.LOTID     AS LotId,
    L.LOTNUMBER AS LotNumber
FROM TLOT L
WHERE L.LOTNUMBER = @LotNumber ";

        public const string GET_DOCUMENT_BY_NUMBER = @"
SELECT TOP 1
    H.DOCUMENTID     AS DocumentId,
    H.DOCUMENTNUMBER AS DocumentNumber
FROM TMMHEAD H
WHERE H.DOCUMENTNUMBER = @DocumentNumber
  AND H.STATUS         = 1;";

        // Fallback for PO lookup when exact match fails (e.g. QR sends 2-digit year '26-27', DB stores '2026-27')
        public const string GET_DOCUMENT_BY_NUMBER_LIKE = @"
SELECT TOP 1
    H.DOCUMENTID     AS DocumentId,
    H.DOCUMENTNUMBER AS DocumentNumber
FROM TMMHEAD H
WHERE H.DOCUMENTNUMBER LIKE @DocumentPattern
  AND H.STATUS         = 1;";

        public const string GET_ALLOCATION_BY_NAME = @"
SELECT  
    A.ALLOCATIONID AS AllocationId
FROM MALLOCATION A
WHERE A.ALLOCATIONNAME = @AllocationName ";

        public const string GET_UOM_BY_NAME = @"
SELECT TOP 1
    U.UOMID   AS UOMId,
    U.UOMNAME AS UOMName
FROM MUOM U
WHERE U.UOMNAME = @UOMName ";

        // ── Picklist (select list for dropdowns) ────────────────────────

        public const string GET_SELECTLIST_GATEENTRY = @"
WITH PagedGateEntry AS (
    SELECT
        GE.GATEENTRYID     AS Id,
        GE.GATEENTRYNUMBER AS Number,
        ROW_NUMBER() OVER (ORDER BY GE.GATEENTRYID DESC) AS RowNum
    FROM TGATEENTRY GE
    WHERE GE.STATUS  = 1
      AND GE.OUID    = @ouid
)
SELECT Id, Number
FROM   PagedGateEntry
WHERE  (@firstnumber = -1 AND @maxresult = -1)
    OR (RowNum BETWEEN @firstnumber AND @maxresult);";
    }
}