// =============================================================================
// FORMULA BODY : auto-shipping batching / order list qualification
//
// Paste into the FormulaText of the formula named in
//   Config > ERP > Order Options > Special Functions > Automated Shipping
//         > Order List Qualification
//
// This is a formula BODY, not a ShipLink Class - it goes in the box where your
// SorMaster sample went, and the compiler wraps it in DoExecute() for you.
//
// Self-contained on purpose: it needs none of the AutoShipStream classes, so it
// can be dropped in and run on its own.
//
// WHAT IT DOES
//   1. Fails any shipment that has outrun its carrier deadline, so nothing can
//      sit in Processing forever and stall the labels queued behind it.
//   2. Creates a ShipmentBatch and claims the oldest Ready shipments into it.
//   3. Returns those shipments as Dictionary<string, List<string>> so
//      AutoShippingManager.ShipOrders ships them.
//
// -----------------------------------------------------------------------------
// ONE ENTRY = ONE SHIPMENT = ONE CARRIER CALL
// -----------------------------------------------------------------------------
// Key   : the ShipmentData.Id. The manager treats the key as diagnostics only,
//         and using the row id makes every log line traceable to a row.
// Value : EVERY order number in that shipment, in arrival order.
//
// That value being a list is the whole multi-order story. A row of ShipmentData
// is one eWarehouse transaction - the run of carton records the sender closed
// with LASTTRAN = 'Y' - and a transaction may carry several ORDERs. They were
// picked together, they go in the same cartons, and they must be ONE shipment.
// Handing them over as one entry with several order numbers is what makes
// ShipRequest.IsMultiOrder true downstream.
//
// The previous version always emitted a single-element list, because a row was
// then a single carton record. Anything that assumed one order per entry needs
// looking at.
//
// AutoShippingManager ships the entries CONCURRENTLY via Parallel.ForEach, so
// they will not complete in order. That is fine - printing order is enforced
// separately, by ShipmentParcel.Id, in the printing loop.
// =============================================================================

Dictionary<string, List<string>> dictionaryOrders = new Dictionary<string, List<string>>();

// How many SHIPMENTS one pass claims. Note this is no longer a count of cartons:
// a batch of 250 multi-carton shipments is a great deal more printing than 250
// used to be.
int batchSize = 250;

// How long a carrier call may take before the shipment is failed with IsTimeOut
// set. This deadline is what guarantees every shipment reaches a terminal state
// in bounded time, so a batch always drains and printing never has to skip one.
//
// It is one deadline for the whole shipment, not one per carton: a twelve-carton
// multi-order shipment is a single carrier call and gets the same 90 seconds as
// a one-carton one. Raise it if large shipments start timing out.
int carrierDeadlineSeconds = 90;

// Backstop for a shipment that was claimed but that nothing ever picked up. It
// has no ShipStartedAt for the deadline above to measure against, so without
// this it would stall its batch forever.
int unstartedDeadlineSeconds = 600;

try
{
    using (SqlConnection connection = new SqlConnection(ConnectionString.ShipLinkDB))
    {
        connection.Open();

        // ------------------------------------------------------------------
        // 1. Deadline sweep.
        // ------------------------------------------------------------------
        string sweepSql =
            @"UPDATE dbo.ShipmentData
                 SET ShipStatus   = 'Failed',
                     IsTimeOut    = 1,
                     ErrorMessage = 'Carrier did not respond within the configured deadline.',
                     ShippedAt    = SYSUTCDATETIME()
               WHERE ShipStatus = 'Processing'
                 AND (
                       (ShipStartedAt IS NOT NULL
                        AND ShipStartedAt < DATEADD(SECOND, -@CarrierSeconds, SYSUTCDATETIME()))
                    OR (ShipStartedAt IS NULL
                        AND BatchedAt IS NOT NULL
                        AND BatchedAt < DATEADD(SECOND, -@UnstartedSeconds, SYSUTCDATETIME()))
                     );";

        using (SqlCommand command = new SqlCommand(sweepSql, connection))
        {
            command.Parameters.AddWithValue("@CarrierSeconds", carrierDeadlineSeconds);
            command.Parameters.AddWithValue("@UnstartedSeconds", unstartedDeadlineSeconds);

            int expired = command.ExecuteNonQuery();
            if (expired > 0)
            {
                // A steady trickle here means the carrier is degraded and parcels
                // are going out with error slips instead of labels - not something
                // to find out from the printer.
                ShipLink.Helpers.LogHelper.LogMessage(
                    $"AutoShipStream: {expired} shipment(s) exceeded the {carrierDeadlineSeconds}s carrier deadline and were failed as timed out.");
            }
        }

        // ------------------------------------------------------------------
        // 2. Create a batch and claim the oldest Ready shipments into it.
        //
        //    ORDER BY Id is the print order - Id is the arrival order.
        //    No READPAST: skipping a locked Ready row would push it into a later
        //    batch and print it late, which is the one thing this design exists
        //    to prevent. Block and wait instead.
        // ------------------------------------------------------------------
        long batchId = 0;
        int claimed = 0;

        using (SqlTransaction transaction = connection.BeginTransaction())
        {
            try
            {
                string insertBatchSql =
                    @"INSERT INTO dbo.ShipmentBatch (Status, RecordCount, PCId, CreatedDate)
                      VALUES ('Processing', 0, @PCId, GETUTCDATE());
                      SELECT CAST(SCOPE_IDENTITY() AS BIGINT);";

                using (SqlCommand command = new SqlCommand(insertBatchSql, connection, transaction))
                {
                    object pcId = DBNull.Value;
                    if (Config.License != null && Config.License.CustomerPC != null)
                    {
                        pcId = Config.License.CustomerPC.Id;
                    }

                    command.Parameters.AddWithValue("@PCId", pcId);
                    batchId = Convert.ToInt64(command.ExecuteScalar());
                }

                string claimSql =
                    @";WITH ready AS (
                          SELECT TOP (@BatchSize) Id, ShipStatus, ShipmentBatch_Id, BatchedAt
                            FROM dbo.ShipmentData WITH (UPDLOCK, ROWLOCK)
                           WHERE ShipStatus = 'Ready'
                           ORDER BY Id
                      )
                      UPDATE ready
                         SET ShipStatus       = 'Processing',
                             ShipmentBatch_Id = @BatchId,
                             BatchedAt        = SYSUTCDATETIME();";

                using (SqlCommand command = new SqlCommand(claimSql, connection, transaction))
                {
                    command.Parameters.AddWithValue("@BatchSize", batchSize);
                    command.Parameters.AddWithValue("@BatchId", batchId);

                    claimed = command.ExecuteNonQuery();
                }

                if (claimed == 0)
                {
                    // Nothing was waiting. Don't leave an empty batch behind for the
                    // printing loop to pick up and immediately complete.
                    using (SqlCommand command = new SqlCommand(
                        "DELETE FROM dbo.ShipmentBatch WHERE Id = @Id;", connection, transaction))
                    {
                        command.Parameters.AddWithValue("@Id", batchId);
                        command.ExecuteNonQuery();
                    }
                }
                else
                {
                    // A shipment with no order at all can never ship, so fail it here
                    // rather than let it sit in Processing until the deadline sweep
                    // catches it - it would block every label behind it until then.
                    //
                    // OrderCount is the test, not OrderNumber: OrderNumber is only
                    // the first order of the transaction, and a shipment can have
                    // parcels whose records never parsed and therefore no orders at
                    // all. Its cartons still print as "no label" and keep their place
                    // in the sequence.
                    using (SqlCommand command = new SqlCommand(
                        @"UPDATE dbo.ShipmentData
                             SET ShipStatus   = 'Failed',
                                 ErrorMessage = COALESCE(ErrorMessage, 'No order could be parsed from any record in this transaction.'),
                                 ShippedAt    = SYSUTCDATETIME()
                           WHERE ShipmentBatch_Id = @BatchId
                             AND (OrderCount = 0
                                  OR OrderNumber IS NULL
                                  OR LTRIM(RTRIM(OrderNumber)) = '');",
                        connection, transaction))
                    {
                        command.Parameters.AddWithValue("@BatchId", batchId);

                        int unusable = command.ExecuteNonQuery();
                        if (unusable > 0)
                        {
                            ShipLink.Helpers.LogHelper.LogMessage(
                                $"AutoShipStream: batch {batchId} contains {unusable} shipment(s) with no usable order; failed immediately.");
                        }
                    }

                    using (SqlCommand command = new SqlCommand(
                        "UPDATE dbo.ShipmentBatch SET RecordCount = @Count WHERE Id = @Id;",
                        connection, transaction))
                    {
                        command.Parameters.AddWithValue("@Count", claimed);
                        command.Parameters.AddWithValue("@Id", batchId);
                        command.ExecuteNonQuery();
                    }
                }

                transaction.Commit();
            }
            catch (Exception)
            {
                transaction.Rollback();
                throw;
            }
        }

        if (claimed == 0)
        {
            ShipLink.Helpers.LogHelper.LogMessage("AutoShipStream: no Ready shipments to batch.");
            result = dictionaryOrders;
            return;
        }

        // ------------------------------------------------------------------
        // 3. Hand the claimed shipments to auto-shipping.
        //
        //    Only shipments still in Processing - the step above already failed
        //    the unusable ones, and there is no point asking the carrier about
        //    them.
        //
        //    One row per ORDER, so a multi-order shipment arrives as several
        //    consecutive rows sharing a ShipmentData.Id and is folded back into
        //    one dictionary entry below. Ordered by (d.Id, o.SeqNo) so the first
        //    order number in each list is the parent order.
        // ------------------------------------------------------------------
        string readSql =
            @"SELECT d.Id, o.OrderNumber
                FROM dbo.ShipmentData d
                JOIN dbo.ShipmentOrder o ON o.ShipmentData_Id = d.Id
               WHERE d.ShipmentBatch_Id = @BatchId
                 AND d.ShipStatus = 'Processing'
               ORDER BY d.Id, o.SeqNo;";

        int multiOrderShipments = 0;

        using (SqlCommand command = new SqlCommand(readSql, connection))
        {
            command.Parameters.AddWithValue("@BatchId", batchId);

            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    string shipmentDataId = Convert.ToString(reader["Id"]);
                    string orderNumber = Convert.ToString(reader["OrderNumber"]);
                    

                    List<string> orderNumbers;
                    if (dictionaryOrders.TryGetValue(shipmentDataId, out orderNumbers))
                    {
                        if (orderNumbers.Count == 1)
                        {
                            multiOrderShipments++;
                        }

                        orderNumbers.Add(orderNumber);
                    }
                    else
                    {
                        dictionaryOrders.Add(shipmentDataId, new List<string> { orderNumber });
                        Config.ShipLinkCache.SetKey(orderNumber, shipmentDataId);

                    }
                }
            }
        }

        ShipLink.Helpers.LogHelper.LogMessage(
            $"AutoShipStream: batch {batchId} claimed {claimed} shipment(s), {dictionaryOrders.Count} handed to auto-shipping ({multiOrderShipments} multi-order).");
    }
}
catch (Exception ex)
{
    ShipLink.Helpers.LogHelper.LogMessage(ex.ToString());
    return;
}

result = dictionaryOrders;
