using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LogisticsDAL.Query.Booking
{
    public static class BookingPicklistQB
    {
        public const string GET_BOOKING_SELECTLIST_SQL = @"
            WITH BookingSelectList AS (
                SELECT
                    a.BOOKINGID          AS BookingId,
                    a.BOOKINGDATE        AS BookingDate,
                    a.BOOKINGNUMBER      AS BookingNumber,
                    a.REFERENCEDATE      AS BookingReferenceDate,
                    a.REFERENCENUMBER    AS BookingReferenceNumber,
                    a.FROMCITYID         AS FromCityId,
                    fromcity.CITYNAME    AS FromCityName,
                    a.TOCITYID           AS ToCityId,
                    tocity.CITYNAME      AS ToCityName,
                    a.PAX                AS BookingPax,
                    a.BILLABLEWEIGHT     AS BookingBillableWeight,
                    a.ISCOD              AS BookingIsCOD,
                    a.CODAMOUNT          AS BookingCODAmount,
                    ROW_NUMBER() OVER (ORDER BY a.BOOKINGID DESC) AS RowNum
                FROM TBOOKING a, MCITY fromcity, MCITY tocity
                WHERE a.FROMCITYID = fromcity.CITYID
                  AND a.TOCITYID = tocity.CITYID
                  AND a.BOOKINGSTATUSID IN (-1199999988, -1199999985, -1199999982)
            )
            SELECT
                BookingId,
                BookingDate,
                BookingNumber,
                BookingReferenceDate,
                BookingReferenceNumber,
                FromCityId,
                FromCityName,
                ToCityId,
                ToCityName,
                BookingPax,
                BookingBillableWeight,
                BookingIsCOD,
                BookingCODAmount
            FROM BookingSelectList
            WHERE
                  (@firstnumber = -1 AND @maxresult = -1)
               OR (RowNum BETWEEN @firstnumber AND @maxresult);
            ";

        public const string GET_BOOKING_SELECTLIST_PG = @"
            WITH ""BookingSelectList"" AS (
                SELECT
                    a.BOOKINGID          AS ""BookingId"",
                    a.BOOKINGDATE        AS ""BookingDate"",
                    a.BOOKINGNUMBER      AS ""BookingNumber"",
                    a.REFERENCEDATE      AS ""BookingReferenceDate"",
                    a.REFERENCENUMBER    AS ""BookingReferenceNumber"",
                    a.FROMCITYID         AS ""FromCityId"",
                    fromcity.CITYNAME    AS ""FromCityName"",
                    a.TOCITYID           AS ""ToCityId"",
                    tocity.CITYNAME      AS ""ToCityName"",
                    a.PAX                AS ""BookingPax"",
                    a.BILLABLEWEIGHT     AS ""BookingBillableWeight"",
                    a.ISCOD              AS ""BookingIsCOD"",
                    a.CODAMOUNT          AS ""BookingCODAmount"",
                    ROW_NUMBER() OVER (ORDER BY a.BOOKINGID DESC) AS ""RowNum""
                FROM TBOOKING a, MCITY fromcity, MCITY tocity
                WHERE a.FROMCITYID = fromcity.CITYID
                  AND a.TOCITYID = tocity.CITYID
                  AND a.BOOKINGSTATUSID IN (-1199999988, -1199999985, -1199999982)
            )
            SELECT
                ""BookingId"",
                ""BookingDate"",
                ""BookingNumber"",
                ""BookingReferenceDate"",
                ""BookingReferenceNumber"",
                ""FromCityId"",
                ""FromCityName"",
                ""ToCityId"",
                ""ToCityName"",
                ""BookingPax"",
                ""BookingBillableWeight"",
                ""BookingIsCOD"",
                ""BookingCODAmount""
            FROM ""BookingSelectList""
            WHERE
                  (:firstnumber = -1 AND :maxresult = -1)
               OR (""RowNum"" BETWEEN :firstnumber AND :maxresult);
            ";

        public const string GET_BOOKING_SELECTLIST_ORACLE = @";";
        public const string GET_BOOKING_SELECTLIST_MYSQL = @";";

        public const string GET_DELIVERY_RUNNUMBER_PICKLIST_SQL = @"
            WITH DeliveryRunNumberPicklist AS (
                SELECT
                    a.BOOKINGID          AS Id,
                    a.BOOKINGNUMBER      AS Number,
                    a.DELIVERYRUNNUMBER  AS DeliveryRunNumber,
                    ROW_NUMBER() OVER (ORDER BY a.BOOKINGID) AS RowNum
                FROM TBOOKING a
                WHERE a.DELIVERYRUNNUMBER <> ''
            )
            SELECT
                Id,
                Number,
                DeliveryRunNumber
            FROM DeliveryRunNumberPicklist
            WHERE
                  (@firstnumber = -1 AND @maxresult = -1)
               OR (RowNum BETWEEN @firstnumber AND @maxresult);
            ";

        public const string GET_DELIVERY_RUNNUMBER_PICKLIST_PG = @"
            WITH ""DeliveryRunNumberPicklist"" AS (
                SELECT
                    a.BOOKINGID          AS ""Id"",
                    a.BOOKINGNUMBER      AS ""Number"",
                    a.DELIVERYRUNNUMBER  AS ""DeliveryRunNumber"",
                    ROW_NUMBER() OVER (ORDER BY a.BOOKINGID) AS ""RowNum""
                FROM TBOOKING a
                WHERE a.DELIVERYRUNNUMBER <> ''
            )
            SELECT
                ""Id"",
                ""Number"",
                ""DeliveryRunNumber""
            FROM ""DeliveryRunNumberPicklist""
            WHERE
                  (:firstnumber = -1 AND :maxresult = -1)
               OR (""RowNum"" BETWEEN :firstnumber AND :maxresult);
            ";

        public const string GET_DELIVERY_RUNNUMBER_PICKLIST_ORACLE = @";";
        public const string GET_DELIVERY_RUNNUMBER_PICKLIST_MYSQL = @";";

        // GB4 parity: Booking.svc/DRSSelectList — ParcelBookingBLL.DRSPicklist →
        // DSRQueryBuilder.GET_DRS_PICKLIST (TBOOKING, distinct DELIVERYRUNNUMBER).
        public const string GET_DRS_SELECTLIST_SQL = @"
            WITH DRSSelectList AS (
                SELECT DISTINCT
                    a.DELIVERYRUNNUMBER      AS DeliveryNumber,
                    a.BIZTRANSACTIONTYPEID   AS DeliveryBizTypeId,
                    a.DELIVERYRUNDATE        AS DeliveryDate,
                    a.BOOKINGID              AS BookingId,
                    ROW_NUMBER() OVER (ORDER BY a.BOOKINGID) AS RowNum
                FROM TBOOKING a
                WHERE a.DELIVERYRUNNUMBER IS NOT NULL AND a.DELIVERYRUNNUMBER <> ''
                AND   (@dsrnumber IS NULL OR a.DELIVERYRUNNUMBER LIKE @dsrnumber)
                AND   (@number IS NULL OR a.BOOKINGNUMBER LIKE @number)
                AND   (@biztransactiontypeid IS NULL OR CAST(a.BIZTRANSACTIONTYPEID AS VARCHAR(50)) LIKE @biztransactiontypeid)
                AND   (@id IS NULL OR CAST(a.BOOKINGID AS VARCHAR(50)) LIKE @id)
                AND   (@bookingstatus IS NULL OR CAST(a.BOOKINGSTATUSID AS VARCHAR(50)) LIKE @bookingstatus)
            )
            SELECT
                DeliveryNumber,
                DeliveryBizTypeId,
                DeliveryDate,
                BookingId
            FROM DRSSelectList
            WHERE
                  (@firstnumber = -1 AND @maxresult = -1)
               OR (RowNum BETWEEN @firstnumber AND @maxresult);
            ";

        public const string GET_DRS_SELECTLIST_PG = @"
            WITH ""DRSSelectList"" AS (
                SELECT DISTINCT
                    a.DELIVERYRUNNUMBER      AS ""DeliveryNumber"",
                    a.BIZTRANSACTIONTYPEID   AS ""DeliveryBizTypeId"",
                    a.DELIVERYRUNDATE        AS ""DeliveryDate"",
                    a.BOOKINGID              AS ""BookingId"",
                    ROW_NUMBER() OVER (ORDER BY a.BOOKINGID) AS ""RowNum""
                FROM TBOOKING a
                WHERE a.DELIVERYRUNNUMBER IS NOT NULL AND a.DELIVERYRUNNUMBER <> ''
                AND   (:dsrnumber IS NULL OR a.DELIVERYRUNNUMBER LIKE :dsrnumber)
                AND   (:number IS NULL OR a.BOOKINGNUMBER LIKE :number)
                AND   (:biztransactiontypeid IS NULL OR CAST(a.BIZTRANSACTIONTYPEID AS VARCHAR(50)) LIKE :biztransactiontypeid)
                AND   (:id IS NULL OR CAST(a.BOOKINGID AS VARCHAR(50)) LIKE :id)
                AND   (:bookingstatus IS NULL OR CAST(a.BOOKINGSTATUSID AS VARCHAR(50)) LIKE :bookingstatus)
            )
            SELECT
                ""DeliveryNumber"",
                ""DeliveryBizTypeId"",
                ""DeliveryDate"",
                ""BookingId""
            FROM ""DRSSelectList""
            WHERE
                  (:firstnumber = -1 AND :maxresult = -1)
               OR (""RowNum"" BETWEEN :firstnumber AND :maxresult);
            ";

        public const string GET_DRS_SELECTLIST_ORACLE = @";";
        public const string GET_DRS_SELECTLIST_MYSQL = @";";
    }
}