public static class AutoShipStreamShipping
{
    // -------------------------------------------------------------------------
    // Starting
    // -------------------------------------------------------------------------

    /// <summary>
    /// Starts the carrier deadline clock for a shipment about to be sent.
    /// Idempotent: only the first call stamps it, so a retry cannot extend a
    /// shipment's deadline indefinitely.
    /// </summary>
    public static void MarkShipStarted(long shipmentDataId)
    {
        AutoShipDb.Execute(
            @"UPDATE dbo.ShipmentData
                 SET ShipStartedAt = SYSUTCDATETIME(),
                     AttemptCount  = AttemptCount + 1
               WHERE Id = @Id
                 AND ShipStatus = @Processing
                 AND ShipStartedAt IS NULL;",
            AutoShipDb.P("@Id", shipmentDataId),
            AutoShipDb.P("@Processing", ShipStatus.Processing));
    }

    // -------------------------------------------------------------------------
    // One label, one parcel
    // -------------------------------------------------------------------------

    /// <summary>
    /// Stores one carton's label.
    ///
    /// Guarded on the SHIPMENT still being in Processing, not just on the parcel
    /// existing: a late carrier response must not attach a label to a shipment
    /// the deadline sweep already failed, whose "no label" slip may have printed.
    /// The parcel must also belong to that shipment - passing a parcel id from
    /// another shipment is a bug, and silently writing it would put a label in
    /// the wrong place in the print order.
    /// </summary>
    /// <returns>False if nothing was written. Do not ignore this.</returns>
    public static bool SetParcelLabel(
        long shipmentDataId, long parcelId, string zpl, string trackingNumber, string carrierCode)
    {
        int affected = AutoShipDb.Execute(
            @"UPDATE p
                 SET p.ZPL            = @Zpl,
                     p.TrackingNumber = @Tracking,
                     p.CarrierCode    = @Carrier
                FROM dbo.ShipmentParcel p
                JOIN dbo.ShipmentData d ON d.Id = p.ShipmentData_Id
               WHERE p.Id = @ParcelId
                 AND p.ShipmentData_Id = @ShipmentId
                 AND d.ShipStatus = @Processing
                 AND p.PrintStatus = @Pending;",
            AutoShipDb.P("@ParcelId", parcelId),
            AutoShipDb.P("@ShipmentId", shipmentDataId),
            AutoShipDb.P("@Processing", ShipStatus.Processing),
            AutoShipDb.P("@Pending", PrintStatus.Pending),
            AutoShipDb.P("@Zpl", zpl),
            AutoShipDb.P("@Tracking", AutoShipDb.Truncate(trackingNumber, 50)),
            AutoShipDb.P("@Carrier", AutoShipDb.Truncate(carrierCode, 20)));

        if (affected != 1)
        {
            ShipLink.Helpers.LogHelper.LogMessage(
                "AutoShipStream: label for parcel " + parcelId + " of shipment " + shipmentDataId +
                " was NOT stored and will not print. Either the shipment is no longer in Processing " +
                "(most likely failed by the deadline sweep), the parcel has already been handed to the " +
                "printer, or the parcel belongs to a different shipment. Check whether the shipment " +
                "exists at the carrier.");
        }

        return affected == 1;
    }

    /// <summary>
    /// The same, addressed by the sender's CARTON number rather than our parcel
    /// id - which is usually all the carrier response carries.
    ///
    /// CARTON is not guaranteed unique across the whole table, so this is scoped
    /// to one shipment. Within a shipment a repeated CARTON is a sender fault:
    /// it is reported and nothing is written, because there is no way to tell
    /// which of the two cartons the label belongs on.
    /// </summary>
    public static bool SetParcelLabelByCarton(
        long shipmentDataId, string cartonId, string zpl, string trackingNumber, string carrierCode)
    {
        long parcelId = ResolveParcelIdByCarton(shipmentDataId, cartonId);
        if (parcelId == 0)
        {
            return false;
        }

        return SetParcelLabel(shipmentDataId, parcelId, zpl, trackingNumber, carrierCode);
    }

    /// <summary>
    /// Finds a parcel by its CARTON number within one shipment.
    /// </summary>
    /// <returns>0 when there is no single unambiguous match.</returns>
    public static long ResolveParcelIdByCarton(long shipmentDataId, string cartonId)
    {
        DataTable matches = AutoShipDb.Query(
            @"SELECT Id
                FROM dbo.ShipmentParcel
               WHERE ShipmentData_Id = @ShipmentId
                 AND CartonId = @CartonId
               ORDER BY Id;",
            AutoShipDb.P("@ShipmentId", shipmentDataId),
            AutoShipDb.P("@CartonId", cartonId));

        if (matches.Rows.Count == 1)
        {
            return Convert.ToInt64(matches.Rows[0]["Id"]);
        }

        if (matches.Rows.Count == 0)
        {
            ShipLink.Helpers.LogHelper.LogMessage(
                "AutoShipStream: shipment " + shipmentDataId + " has no carton '" + cartonId +
                "'. The label was not stored.");
        }
        else
        {
            ShipLink.Helpers.LogHelper.LogMessage(
                "AutoShipStream: shipment " + shipmentDataId + " has " + matches.Rows.Count +
                " parcels carrying carton '" + cartonId + "'. The sender reused a carton number, so " +
                "there is no way to tell which one this label belongs on. Nothing was stored.");
        }

        return 0;
    }

    /// <summary>
    /// Records that a carton will legitimately never have a label - consolidated
    /// onto another parcel, say.
    ///
    /// It keeps its place in the print order; without this the printing loop
    /// would block on it until the deadline sweep failed the whole shipment.
    /// </summary>
    public static bool MarkParcelNoLabel(long shipmentDataId, long parcelId, string reason)
    {
        int affected = AutoShipDb.Execute(
            @"UPDATE dbo.ShipmentParcel
                 SET PrintStatus  = @NoLabel,
                     PrintedAt    = SYSUTCDATETIME(),
                     ErrorMessage = @Reason
               WHERE Id = @ParcelId
                 AND ShipmentData_Id = @ShipmentId
                 AND PrintStatus = @Pending;",
            AutoShipDb.P("@ParcelId", parcelId),
            AutoShipDb.P("@ShipmentId", shipmentDataId),
            AutoShipDb.P("@NoLabel", PrintStatus.NoLabel),
            AutoShipDb.P("@Pending", PrintStatus.Pending),
            AutoShipDb.P("@Reason", AutoShipDb.Truncate(reason, 1000)));

        return affected == 1;
    }

    // -------------------------------------------------------------------------
    // Closing the shipment
    // -------------------------------------------------------------------------

    /// <summary>
    /// Records a successful shipment, after its parcels' labels have been stored.
    ///
    /// Does NOT require every parcel to have a label: a shipment that came back
    /// short is still a shipment, and refusing to close it here would just stall
    /// the batch until the deadline sweep failed it. Parcels without a label are
    /// counted and logged, and the printing loop records each as NoLabel.
    /// </summary>
    /// <returns>False if the shipment had already moved on. Do not ignore this.</returns>
    public static bool MarkShipped(long shipmentDataId)
    {
        int missing = CountParcelsWithoutLabel(shipmentDataId);

        int affected = AutoShipDb.Execute(
            @"UPDATE dbo.ShipmentData
                 SET ShipStatus = @Shipped,
                     ShippedAt  = SYSUTCDATETIME()
               WHERE Id = @Id
                 AND ShipStatus = @Processing;",
            AutoShipDb.P("@Id", shipmentDataId),
            AutoShipDb.P("@Shipped", ShipStatus.Shipped),
            AutoShipDb.P("@Processing", ShipStatus.Processing));

        if (affected != 1)
        {
            ShipLink.Helpers.LogHelper.LogMessage(
                "AutoShipStream: shipment " + shipmentDataId + " was no longer in Processing when the " +
                "carrier responded - it had already been failed, most likely by the deadline sweep. Its " +
                "labels will NOT print. Check whether the shipment exists at the carrier.");
            return false;
        }

        if (missing > 0)
        {
            ShipLink.Helpers.LogHelper.LogMessage(
                "AutoShipStream: shipment " + shipmentDataId + " was marked Shipped with " + missing +
                " parcel(s) carrying no label. Those cartons will go out unlabelled.");
        }

        return true;
    }

    /// <summary>
    /// Convenience for a single-parcel shipment: stores the one label and closes
    /// the shipment in one call. This is the old signature, and it still means
    /// what it used to.
    ///
    /// Refuses on a multi-parcel shipment rather than guessing which carton the
    /// label belongs to. Use SetParcelLabelByCarton per carton, then MarkShipped.
    /// </summary>
    public static bool MarkShipped(long shipmentDataId, string zpl, string trackingNumber, string carrierCode)
    {
        DataTable parcels = AutoShipDb.Query(
            "SELECT Id FROM dbo.ShipmentParcel WHERE ShipmentData_Id = @Id ORDER BY Id;",
            AutoShipDb.P("@Id", shipmentDataId));

        if (parcels.Rows.Count != 1)
        {
            ShipLink.Helpers.LogHelper.LogMessage(
                "AutoShipStream: MarkShipped(id, zpl, ...) was called for shipment " + shipmentDataId +
                ", which has " + parcels.Rows.Count + " parcels. That overload only works for a " +
                "single-parcel shipment - one ZPL cannot label " + parcels.Rows.Count + " cartons. " +
                "Use SetParcelLabelByCarton per carton, then MarkShipped(id). Nothing was stored.");
            return false;
        }

        if (!SetParcelLabel(shipmentDataId, Convert.ToInt64(parcels.Rows[0]["Id"]), zpl, trackingNumber, carrierCode))
        {
            return false;
        }

        return MarkShipped(shipmentDataId);
    }

    /// <summary>Records a carrier refusal or error. Same guard as MarkShipped.</summary>
    public static bool MarkShipFailed(long shipmentDataId, string errorMessage, bool isTimeOut)
    {
        int affected = AutoShipDb.Execute(
            @"UPDATE dbo.ShipmentData
                 SET ShipStatus   = @Failed,
                     IsTimeOut    = @IsTimeOut,
                     ErrorMessage = @Error,
                     ShippedAt    = SYSUTCDATETIME()
               WHERE Id = @Id
                 AND ShipStatus = @Processing;",
            AutoShipDb.P("@Id", shipmentDataId),
            AutoShipDb.P("@Failed", ShipStatus.Failed),
            AutoShipDb.P("@Processing", ShipStatus.Processing),
            AutoShipDb.P("@IsTimeOut", isTimeOut),
            AutoShipDb.P("@Error", AutoShipDb.Truncate(errorMessage, 4000)));

        return affected == 1;
    }

    // -------------------------------------------------------------------------
    // Finding a shipment
    // -------------------------------------------------------------------------

    /// <summary>
    /// Finds the in-flight shipment for an order number.
    ///
    /// Searches ShipmentOrder, not just ShipmentData.OrderNumber: that column
    /// only holds the FIRST order of a transaction, so on a multi-order shipment
    /// every order after the first would otherwise be unfindable.
    ///
    /// CAVEAT: an order number is not unique. If the sender retransmits a file,
    /// or the same order legitimately ships twice, more than one shipment can
    /// carry it. This returns the oldest still in Processing, which is the right
    /// answer for a straight retransmit and the wrong one if two shipments for
    /// that order are genuinely in flight at once. Where you can, carry
    /// ShipmentData.Id through your shipping code instead of calling this.
    /// </summary>
    /// <returns>0 when nothing matches.</returns>
    public static long ResolveIdByOrderNumber(string orderNumber)
    {
        object value = AutoShipDb.Scalar(
            @"SELECT MIN(d.Id)
                FROM dbo.ShipmentData d
               WHERE d.ShipStatus = @Processing
                 AND EXISTS (
                       SELECT 1 FROM dbo.ShipmentOrder o
                        WHERE o.ShipmentData_Id = d.Id
                          AND o.OrderNumber = @OrderNumber
                     );",
            AutoShipDb.P("@OrderNumber", orderNumber),
            AutoShipDb.P("@Processing", ShipStatus.Processing));

        if (value == null)
        {
            ShipLink.Helpers.LogHelper.LogMessage(
                "AutoShipStream: no in-flight shipment found for order '" + orderNumber + "'.");
            return 0;
        }

        return Convert.ToInt64(value);
    }

    // -------------------------------------------------------------------------
    // Reading a shipment
    //
    // Four queries rather than one join, because the shapes are genuinely
    // different: one shipment, N orders, N details, N parcels. A single flattened
    // result would repeat the order across every one of its cartons and leave the
    // caller to un-repeat it - which is the bug the listener already avoided once
    // by grouping at the source.
    //
    // Together these are the Order / OrderDetail / Parcel graph: build a
    // ShipRequest from GetShipment, one Order per row of GetOrders (the first is
    // the parent), its OrderDetails from GetOrderDetails filtered by
    // ShipmentOrder_Id, and ShipRequest.Parcels from GetParcels. Set
    // ShipRequest.IsMultiOrder when GetOrders returns more than one row.
    //
    // Note ShipLink's Parcel entity has no Order_Id. On a multi-order shipment
    // the carton-to-order link exists only in ShipmentParcel.ShipmentOrder_Id -
    // carry it across yourself if the carrier call needs it.
    // -------------------------------------------------------------------------

    /// <summary>The shipments in a batch still waiting on the carrier, in arrival order.</summary>
    public static DataTable GetPendingCarrierWork(long batchId)
    {
        return AutoShipDb.Query(
            @"SELECT Id, OrderNumber, OrderCount, ParcelCount, CloseReason, SourceEndpoint, ErrorMessage
                FROM dbo.ShipmentData
               WHERE ShipmentBatch_Id = @BatchId
                 AND ShipStatus = @Processing
               ORDER BY Id;",
            AutoShipDb.P("@BatchId", batchId),
            AutoShipDb.P("@Processing", ShipStatus.Processing));
    }

    /// <summary>One row: the shipment header.</summary>
    public static DataTable GetShipment(long shipmentDataId)
    {
        return AutoShipDb.Query(
            @"SELECT Id, OrderNumber, OrderCount, ParcelCount, RecordCount, CloseReason,
                     ShipStatus, PrintStatus, IsTimeOut, ShipmentBatch_Id, PCId, CreatedDate,
                     BatchedAt, ShipStartedAt, ShippedAt, AttemptCount, ErrorMessage, SourceEndpoint
                FROM dbo.ShipmentData
               WHERE Id = @Id;",
            AutoShipDb.P("@Id", shipmentDataId));
    }

    /// <summary>
    /// The shipment's orders, in arrival order. More than one row IS the
    /// multi-order case; the first row is the parent order.
    /// </summary>
    public static DataTable GetOrders(long shipmentDataId)
    {
        return AutoShipDb.Query(
            @"SELECT *
                FROM dbo.ShipmentOrder
               WHERE ShipmentData_Id = @Id
               ORDER BY SeqNo;",
            AutoShipDb.P("@Id", shipmentDataId));
    }

    /// <summary>
    /// Every part line of the shipment. Group by ShipmentOrder_Id to attach them
    /// to their orders; ShipmentParcel_Id says which carton each was reported on.
    /// </summary>
    public static DataTable GetOrderDetails(long shipmentDataId)
    {
        return AutoShipDb.Query(
            @"SELECT *
                FROM dbo.ShipmentOrderDetail
               WHERE ShipmentData_Id = @Id
               ORDER BY ShipmentOrder_Id, SeqNo;",
            AutoShipDb.P("@Id", shipmentDataId));
    }

    /// <summary>
    /// The shipment's cartons, in print order.
    ///
    /// Excludes ZPL on purpose - it is written here, not read, and a large
    /// shipment would otherwise pull every label into memory. A parcel with a
    /// null ShipmentOrder_Id is one whose record did not parse: it has no order,
    /// and its ErrorMessage says why.
    /// </summary>
    public static DataTable GetParcels(long shipmentDataId)
    {
        return AutoShipDb.Query(
            @"SELECT Id, ShipmentOrder_Id, SeqNo, CartonId, CartonSeq, CartonTotal,
                     Weight, WeightRaw, Length, Width, Height, DimUnit, Cost, TotVal,
                     IsLastCarton, IsLastTran, PrintStatus, TrackingNumber, CarrierCode,
                     Data, ErrorMessage
                FROM dbo.ShipmentParcel
               WHERE ShipmentData_Id = @Id
               ORDER BY Id;",
            AutoShipDb.P("@Id", shipmentDataId));
    }

    /// <summary>
    /// How many of a shipment's cartons still have no label. Zero is what you
    /// want before calling MarkShipped.
    /// </summary>
    public static int CountParcelsWithoutLabel(long shipmentDataId)
    {
        object value = AutoShipDb.Scalar(
            @"SELECT COUNT(*)
                FROM dbo.ShipmentParcel
               WHERE ShipmentData_Id = @Id
                 AND PrintStatus = @Pending
                 AND (ZPL IS NULL OR DATALENGTH(ZPL) = 0);",
            AutoShipDb.P("@Id", shipmentDataId),
            AutoShipDb.P("@Pending", PrintStatus.Pending));

        return value == null ? 0 : Convert.ToInt32(value);
    }
}