namespace QMSDAL.Query.PcsCardMaterial
{
    // INDEX RECOMMENDATION:
    //   IX_TPCSCARDMATERIAL_PCSCARDSTAEID  ON TPCSCARDMATERIAL (PCSCARDSTAEID, SLNO)
    //   IX_TPCSCARDMATERIAL_BATCHNO        ON TPCSCARDMATERIAL (BATCHNO)
    //     INCLUDE (PCSCARDSTAEID, MATERIALNAME, GRADECODE, DATEOFEXPIRY, ISEXPIRED)
    //     — backwards traceability: "which serial numbers used batch GM-450-B2025-1101?"
    //   IX_TPCSCARDMATERIAL_EXPIRED        ON TPCSCARDMATERIAL (ISEXPIRED, DATEOFEXPIRY)
    //     WHERE ISEXPIRED = 1  — filtered: expired material reporting
    public static class PcsCardMaterialQB
    {
        public const string GET_BY_STAGE = @"
            SELECT
                m.PCSCARDMATERIALID   AS PcsCardMaterialId,
                m.PCSCARDSTAEID       AS PcsCardStaeId,
                m.SLNO                AS SlNo,
                m.ITEMID              AS ItemId,
                m.MATERIALNAME        AS MaterialName,
                m.GRADECODE           AS GradeCode,
                m.BATCHNO             AS BatchNo,
                m.LOTID               AS LotId,
                m.QUANTITY            AS Quantity,
                m.UOMID               AS UomId,
                m.DATEOFMANUFACTURE   AS DateOfManufacture,
                m.DATEOPENED          AS DateOpened,
                m.DATEOFEXPIRY        AS DateOfExpiry,
                m.ISEXPIRED           AS IsExpired,
                m.REMARKS             AS Remarks,
                u.UOMNAME             AS UomName
            FROM   TPCSCARDMATERIAL m
            LEFT   JOIN MUOM u ON m.UOMID = u.UOMID
            WHERE  m.PCSCARDSTAEID = @PcsCardStaeId
            ORDER  BY m.SLNO";

        public const string GET_BY_CARD = @"
            SELECT
                m.PCSCARDMATERIALID   AS PcsCardMaterialId,
                m.PCSCARDSTAEID       AS PcsCardStaeId,
                m.SLNO                AS SlNo,
                m.ITEMID              AS ItemId,
                m.MATERIALNAME        AS MaterialName,
                m.GRADECODE           AS GradeCode,
                m.BATCHNO             AS BatchNo,
                m.LOTID               AS LotId,
                m.QUANTITY            AS Quantity,
                m.UOMID               AS UomId,
                m.DATEOFMANUFACTURE   AS DateOfManufacture,
                m.DATEOPENED          AS DateOpened,
                m.DATEOFEXPIRY        AS DateOfExpiry,
                m.ISEXPIRED           AS IsExpired,
                m.REMARKS             AS Remarks,
                u.UOMNAME             AS UomName
            FROM   TPCSCARDMATERIAL m
            INNER  JOIN TPCSCARDSTAGE s ON m.PCSCARDSTAEID = s.PCSCARDSTAEID
            LEFT   JOIN MUOM u          ON m.UOMID         = u.UOMID
            WHERE  s.PCSCARDID = @PcsCardId
            ORDER  BY s.STAGENO, m.SLNO";

        public const string HAS_MATERIALS_FOR_STAGE = @"
            SELECT COUNT(*)
            FROM   TPCSCARDMATERIAL
            WHERE  PCSCARDSTAEID = @PcsCardStaeId";

        public const string DELETE_BY_STAGE = @"
            DELETE FROM TPCSCARDMATERIAL
            WHERE  PCSCARDSTAEID = @PcsCardStaeId";

        public const string INSERT_MATERIAL = @"
            INSERT INTO TPCSCARDMATERIAL
            (
                PCSCARDMATERIALID, PCSCARDSTAEID, SLNO,
                ITEMID, MATERIALNAME, GRADECODE, BATCHNO, LOTID,
                QUANTITY, UOMID,
                DATEOFMANUFACTURE, DATEOPENED, DATEOFEXPIRY,
                ISEXPIRED, REMARKS
            )
            VALUES
            (
                @PcsCardMaterialId, @PcsCardStaeId, @SlNo,
                @ItemId, @MaterialName, @GradeCode, @BatchNo, @LotId,
                @Quantity, @UomId,
                @DateOfManufacture, @DateOpened, @DateOfExpiry,
                @IsExpired, @Remarks
            )";
    }
}