namespace ComplianceDAL.Query.GSTEWayBill;

public static class GSTEWayBillQB
{
    public const string GET_EWAYBILL = @"
        SELECT  e.EWAYBILLID            EWayBillId,
                e.ARTIFACTID            ArtifactId,
                e.DOCUMENTID            DocumentId,
                e.DOCUMENTTYPE          DocumentType,
                e.DOCUMENTNUMBER        DocumentNumber,
                e.DOCUMENTDATE          DocumentDate,
                e.EWBNUMBER             EWBNumber,
                e.GSTIN                 GSTIN,
                e.FROMGSTIN             FromGSTIN,
                e.FROMNAME              FromName,
                e.FROMADDRESS           FromAddress,
                e.FROMPINCODE           FromPincode,
                e.FROMSTATECODE         FromStateCode,
                e.TOGSTIN               ToGSTIN,
                e.TONAME                ToName,
                e.TOADDRESS             ToAddress,
                e.TOPINCODE             ToPincode,
                e.TOSTATECODE           ToStateCode,
                e.TRANSPORTERGSTIN      TransporterGSTIN,
                e.TRANSPORTERNAME       TransporterName,
                e.TRANSPORTDOCNUMBER    TransportDocNumber,
                e.TRANSPORTDOCDATE      TransportDocDate,
                e.VEHICLENUMBER         VehicleNumber,
                e.VEHICLETYPE           VehicleType,
                e.TRANSPORTMODE         TransportMode,
                e.DISTANCE              Distance,
                e.TOTALVALUE            TotalValue,
                e.TAXABLEVALUE          TaxableValue,
                e.IGSTAMOUNT            IGSTAmount,
                e.CGSTAMOUNT            CGSTAmount,
                e.SGSTAMOUNT            SGSTAmount,
                e.CESSAMOUNT            CessAmount,
                e.STATUS                Status,
                e.VALIDUPTO             ValidUpto,
                e.CANCELLEDON           CancelledOn,
                e.CANCELLATIONREASON    CancellationReason,
                e.SUBMISSIONMODE        SubmissionMode,
                e.CREATEDBYID           CreatedById,
                e.CREATEDON             CreatedOn
        FROM    TGST_EWAYBILL e
        WHERE   e.EWAYBILLID   = @EWayBillId
        AND     e.CLIENTID     = @ClientId
        AND     e.DATABASENAME = @DatabaseName";

    public const string GET_EWAYBILL_BY_DOCUMENT = @"
        SELECT  TOP 1
                e.EWAYBILLID            EWayBillId,
                e.ARTIFACTID            ArtifactId,
                e.DOCUMENTID            DocumentId,
                e.DOCUMENTTYPE          DocumentType,
                e.DOCUMENTNUMBER        DocumentNumber,
                e.EWBNUMBER             EWBNumber,
                e.GSTIN                 GSTIN,
                e.STATUS                Status,
                e.VALIDUPTO             ValidUpto,
                e.VEHICLENUMBER         VehicleNumber,
                e.TRANSPORTERGSTIN      TransporterGSTIN,
                e.CREATEDBYID           CreatedById,
                e.CREATEDON             CreatedOn
        FROM    TGST_EWAYBILL e
        WHERE   e.DOCUMENTID   = @DocumentId
        AND     e.DOCUMENTTYPE = @DocumentType
        AND     e.CLIENTID     = @ClientId
        AND     e.DATABASENAME = @DatabaseName
        ORDER BY e.CREATEDON DESC";

    public const string SAVE_EWAYBILL = @"
        INSERT INTO TGST_EWAYBILL (
            ARTIFACTID, DOCUMENTID, DOCUMENTTYPE, DOCUMENTNUMBER, DOCUMENTDATE,
            EWBNUMBER, GSTIN,
            FROMGSTIN, FROMNAME, FROMADDRESS, FROMPINCODE, FROMSTATECODE,
            TOGSTIN, TONAME, TOADDRESS, TOPINCODE, TOSTATECODE,
            TRANSPORTERGSTIN, TRANSPORTERNAME, TRANSPORTDOCNUMBER, TRANSPORTDOCDATE,
            VEHICLENUMBER, VEHICLETYPE, TRANSPORTMODE, DISTANCE,
            TOTALVALUE, TAXABLEVALUE, IGSTAMOUNT, CGSTAMOUNT, SGSTAMOUNT, CESSAMOUNT,
            STATUS, VALIDUPTO, SUBMISSIONMODE, BATCHID,
            CLIENTID, DATABASENAME, CREATEDBYID, CREATEDON
        )
        OUTPUT INSERTED.EWAYBILLID
        VALUES (
            @ArtifactId, @DocumentId, @DocumentType, @DocumentNumber, @DocumentDate,
            @EWBNumber, @GSTIN,
            @FromGSTIN, @FromName, @FromAddress, @FromPincode, @FromStateCode,
            @ToGSTIN, @ToName, @ToAddress, @ToPincode, @ToStateCode,
            @TransporterGSTIN, @TransporterName, @TransportDocNumber, @TransportDocDate,
            @VehicleNumber, @VehicleType, @TransportMode, @Distance,
            @TotalValue, @TaxableValue, @IGSTAmount, @CGSTAmount, @SGSTAmount, @CessAmount,
            'Active', @ValidUpto, @SubmissionMode, @BatchId,
            @ClientId, @DatabaseName, @CreatedById, GETUTCDATE()
        )";

    public const string CANCEL_EWAYBILL = @"
        UPDATE  TGST_EWAYBILL
        SET     STATUS             = 'Cancelled',
                CANCELLEDON        = GETUTCDATE(),
                CANCELLATIONREASON = @CancellationReason,
                MODIFIEDBYID       = @ModifiedById,
                MODIFIEDON         = GETUTCDATE()
        WHERE   EWAYBILLID   = @EWayBillId
        AND     CLIENTID     = @ClientId
        AND     DATABASENAME = @DatabaseName
        AND     STATUS       = 'Active'";

    // Matches GB4's inline "select IRN from Tmmhead where documentid=..." in EwayBillFromIRN.
    public const string GET_TMMHEAD_IRN_BY_DOCUMENTID = @"
        SELECT IRN FROM TMMHEAD WHERE DOCUMENTID = @DocumentId";

    // Matches GB4's EWayBillQueryBuilder.GET_EWAY_GSTIN_BASED_ON_OU — resolves the GSTIN currently
    // registered against the e-way bill's OU (via TMMHEAD.OUID → MADDRESS, OBJECTTYPEID=-1899999977
    // is the OU's own registered address, same constant AdapterConfigQB uses for this join).
    public const string GET_EWAY_GSTIN_BASED_ON_OU = @"
        SELECT  MA.GSTIN
        FROM    TMMHEAD MM
        INNER JOIN MADDRESS MA ON MA.OBJECTID = MM.OUID
        WHERE   MM.EBNUMBER = @EBNumber
        AND     MA.OBJECTTYPEID = -1899999977";

    // Matches GB4's EWayBillQueryBuilder.GET_EWAY_GSTIN_BASED_ON_STORE — same lookup but keyed off
    // the e-way bill's STOREID instead of OUID (OBJECTTYPEID=-1899997963 is the store's own
    // registered address). GB4 gates OU-vs-Store on two hardcoded client IDs (WB/Sunbeam); ported
    // as-is for now per explicit instruction — the discriminator itself is expected to change soon,
    // see GSTEWayBillBLL.CancelEWayBillAsync.
    public const string GET_EWAY_GSTIN_BASED_ON_STORE = @"
        SELECT  MA.GSTIN
        FROM    TMMHEAD MM
        INNER JOIN MADDRESS MA ON MA.OBJECTID = MM.STOREID
        WHERE   MM.EBNUMBER = @EBNumber
        AND     MA.OBJECTTYPEID = -1899997963";

    // Matches GB4's EWayDAL.CancelEway — "UPDATE TMMHEAD SET EBNUMBER='' WHERE EBNUMBER=@EBNumber".
    // GB4 clears TMMHEAD's EBNUMBER by matching on the EBNUMBER value itself (not DocumentId), so
    // this is kept literal.
    public const string CLEAR_TMMHEAD_EBNUMBER_BY_EBNUMBER = @"
        UPDATE TMMHEAD SET EBNUMBER = '' WHERE EBNUMBER = @EBNumber";

    public const string UPDATE_VEHICLE = @"
        UPDATE  TGST_EWAYBILL
        SET     VEHICLENUMBER      = @VehicleNumber,
                VEHICLETYPE        = @VehicleType,
                TRANSPORTMODE      = @TransportMode,
                TRANSPORTDOCNUMBER = @TransportDocNumber,
                TRANSPORTDOCDATE   = @TransportDocDate,
                MODIFIEDBYID       = @ModifiedById,
                MODIFIEDON         = GETUTCDATE()
        WHERE   EWAYBILLID   = @EWayBillId
        AND     CLIENTID     = @ClientId
        AND     DATABASENAME = @DatabaseName
        AND     STATUS       = 'Active'";

    // Matches GB4's EWayDAL.ProcessEway TMMHEAD update — same columns (EBNUMBER/EWAYDATE repurpose
    // EXCISEREGISTERLASTNO for distance and FORMSISSUEDDATE for validUpto, exactly as GB4 does).
    // TMMHEAD lives in the ERP/MM (SQL Server) schema, unlike TGST_EWAYBILL above.
    public const string UPDATE_TMMHEAD_EWAY_BY_DOCUMENTID = @"
        UPDATE TMMHEAD SET
            EBNUMBER              = @EBNumber,
            EWAYDATE               = @EwayDate,
            EXCISEREGISTERLASTNO  = @Distance,
            FORMSISSUEDDATE        = @ValidUpto
        WHERE DOCUMENTID = @DocumentId";

    // Port note: the Postgres original was `BETWEEN NOW() AND NOW() + INTERVAL '@HoursAhead hours'`
    // — a Postgres INTERVAL literal can't interpolate a bound parameter that way, so @HoursAhead was
    // never actually applied. Fixed here via DATEADD, which does parameterize correctly.
    public const string GET_EXPIRING_EWBS = @"
        SELECT  e.EWAYBILLID   EWayBillId,
                e.EWBNUMBER    EWBNumber,
                e.GSTIN        GSTIN,
                e.DOCUMENTID   DocumentId,
                e.VALIDUPTO    ValidUpto
        FROM    TGST_EWAYBILL e
        WHERE   e.STATUS       = 'Active'
        AND     e.VALIDUPTO    BETWEEN GETUTCDATE() AND DATEADD(hour, @HoursAhead, GETUTCDATE())
        AND     e.CLIENTID     = @ClientId
        AND     e.DATABASENAME = @DatabaseName
        ORDER BY e.VALIDUPTO";

    public const string GET_EWAYBILL_COVERAGE_LIST = @"
        SELECT  e.GSTIN                                     GSTIN,
                FORMAT(e.DOCUMENTDATE, 'MMyyyy')            ReturnPeriod,
                COUNT(*)                                     TotalDocuments,
                SUM(CASE WHEN e.STATUS = 'Active' THEN 1 ELSE 0 END) WithEWB,
                SUM(CASE WHEN e.EWBNUMBER = '' OR e.EWBNUMBER IS NULL THEN 1 ELSE 0 END) WithoutEWB,
                SUM(CASE WHEN e.STATUS = 'Cancelled' THEN 1 ELSE 0 END) Cancelled,
                SUM(CASE WHEN e.STATUS = 'Expired'   THEN 1 ELSE 0 END) Expired,
                ROUND(
                    100.0 * SUM(CASE WHEN e.STATUS = 'Active' THEN 1 ELSE 0 END)
                    / NULLIF(COUNT(*), 0), 2
                ) CoveragePercent
        FROM    TGST_EWAYBILL e
        WHERE   e.CLIENTID     = @ClientId
        AND     e.DATABASENAME = @DatabaseName
        AND     (@GSTIN = '' OR e.GSTIN = @GSTIN)
        AND     (@ReturnPeriod = '' OR FORMAT(e.DOCUMENTDATE, 'MMyyyy') = @ReturnPeriod)
        GROUP BY e.GSTIN, FORMAT(e.DOCUMENTDATE, 'MMyyyy')
        ORDER BY ReturnPeriod DESC, e.GSTIN";
}