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

namespace AdminDAL.Query.IceDataQB
{
    public class IceDataQB
    {
        public const string GET_ICEDATA_Full = @"SELECT
                                            /* =========================
                                               Parent : TICEDATA
                                               ========================= */

                                            ID.ICEDATAID                AS IceDataId,
                                            ID.ICEDATANUMBER            AS IceDataNumber,
                                            ID.REFERENCENUMBER          AS ReferenceNumber,
                                            ID.ENTITYID                 AS EntityId,
                                            ID.MODELMAPID               AS ModelMapId,
                                            ID.ICETEMPLATEID            AS IceTemplateId,
                                            ID.ICEDATASTATUS            AS IceDataStatus,
                                            ID.CONFIDENCEPERCENT        AS ConfidencePercent,
                                            ID.DISPLAYDATA              AS DisplayData,
                                            ID.ORIGINALFILENAME         AS OriginalFileName,
                                            ID.FILEURL                  AS FileURL,
                                            ID.FILEBUFFER               AS FileBuffer,
                                            ID.EXTRACTEDTEXT            AS ExtractedText,
                                            ID.SOURCEDATA               AS SourceData,
                                            ID.VALIDATIONERRORS         AS ValidationErrors,
                                            ID.CREATEDBYID              AS CreatedById,
                                            ID.CREATEDON                AS CreatedOn,
                                            ID.MODIFIEDBYID             AS ModifiedById,
                                            ID.MODIFIEDON               AS ModifiedOn,
                                            ID.TENANTID                 AS TenantId,

                                            /* =========================
                                               Child : TICEDATADETAIL
                                               ========================= */

                                            IDD.ICEDATADETAILID             AS IceDataDetailId,
                                            IDD.ICEDATAID                   AS IceDataId,
                                            IDD.SLNO                        AS SLNO,
                                            IDD.DETAILREFERENCENUMBER       AS DetailReferenceNumber,
                                            IDD.ICEDETAILSTATUS             AS IceDetailStatus,
                                            IDD.DISPLAYDETAIL               AS DisplayDetail,
                                            IDD.DETAILSOURCEDATA            AS DetailSourceData,
                                            IDD.CONVERTEDDATA               AS ConvertedData,
                                            IDD.DETAILVALIDATIONERRORS      AS DetailValidationErrors,
                                            IDD.TARGETRESPONSE              AS TargetResponse,
                                            IDD.TARGETID                    AS TargetId,
                                            IDD.TARGETREFERENCE             AS TargetReference,
                                            IDD.CURRENTBIZPROCESSSTAGEID    AS CurrentBizProcessStageId,
                                            IDD.REVIEWNOTES                 AS ReviewNotes,
                                            IDD.REVIEWEDBYID                AS ReviewedById,
                                            IDD.ORIGINALICEDATADETAILID     AS OriginalIceDataDetailId,
                                            IDD.ORIGINALREFERENCENUMBER     AS OriginalReferenceNumber,
                                            IDD.AMENDMENTSLNO               AS AmendmentSLNO,
                                            IDD.AMENDMENTREASON             AS AmendmentReason,
                                            IDD.AMENDMENTDATE               AS AmendmentDate,
                                            IDD.CREATEDBYID                 AS CreatedById,
                                            IDD.CREATEDON                   AS CreatedOn,
                                            IDD.MODIFIEDBYID                AS ModifiedById,
                                            IDD.MODIFIEDON                  AS ModifiedOn,
                                            IDD.TENANTID                    AS TenantId

                                        FROM TICEDATA ID
                                        LEFT JOIN TICEDATADETAIL IDD ON ID.ICEDATAID = IDD.ICEDATAID

                                        WHERE ID.ICEDATAID = @icedataid;";

        public const string GET_ICEDATA = @"
                                            SELECT
                                                ICEDATAID         AS IceDataId,
                                                ICEDATANUMBER     AS IceDataNumber,
                                                REFERENCENUMBER   AS ReferenceNumber,
                                                ENTITYID          AS EntityId,
                                                MODELMAPID        AS ModelMapId,
                                                ICETEMPLATEID     AS IceTemplateId,
                                                ICEDATASTATUS     AS IceDataStatus,
                                                CONFIDENCEPERCENT AS ConfidencePercent,
                                                DISPLAYDATA       AS DisplayData,
                                                ORIGINALFILENAME  AS OriginalFileName,
                                                FILEURL           AS FileURL,
                                                FILEBUFFER        AS FileBuffer,
                                                EXTRACTEDTEXT     AS ExtractedText,
                                                SOURCEDATA        AS SourceData,
                                                VALIDATIONERRORS  AS ValidationErrors,
                                                CREATEDBYID       AS CreatedById,
                                                CREATEDON         AS CreatedOn,
                                                MODIFIEDBYID      AS ModifiedById,
                                                MODIFIEDON        AS ModifiedOn,
                                                TENANTID         AS TenantId
                                            FROM TICEDATA
                                            WHERE ICEDATAID = @icedataid;
                                            ";

        public const string GET_ICEDATA_DETAIL = @"
                                                SELECT
                                                    /* Parent fields you may need */
                                                    ID.ICEDATAID       AS IceDataId,
                                                    ID.ICEDATANUMBER   AS IceDataNumber,
                                                    ID.REFERENCENUMBER AS ReferenceNumber,

                                                    /* Detail fields */
                                                    IDD.ICEDATADETAILID        AS IceDataDetailId,
                                                    IDD.ICEDATAID              AS IceDataId,
                                                    IDD.SLNO                   AS SLNO,
                                                    IDD.DETAILREFERENCENUMBER  AS DetailReferenceNumber,
                                                    IDD.ICEDETAILSTATUS        AS IceDetailStatus,
                                                    IDD.DISPLAYDETAIL          AS DisplayDetail,
                                                    IDD.DETAILSOURCEDATA       AS DetailSourceData,
                                                    IDD.CONVERTEDDATA          AS ConvertedData,
                                                    IDD.DETAILVALIDATIONERRORS AS DetailValidationErrors,
                                                    IDD.TARGETRESPONSE         AS TargetResponse,
                                                    IDD.TARGETID               AS TargetId,
                                                    IDD.TARGETREFERENCE        AS TargetReference,
                                                    IDD.CURRENTBIZPROCESSSTAGEID AS CurrentBizProcessStageId,
                                                    IDD.REVIEWNOTES            AS ReviewNotes,
                                                    IDD.REVIEWEDBYID           AS ReviewedById,
                                                    IDD.ORIGINALICEDATADETAILID AS OriginalIceDataDetailId,
                                                    IDD.ORIGINALREFERENCENUMBER AS OriginalReferenceNumber,
                                                    IDD.AMENDMENTSLNO          AS AmendmentSLNO,
                                                    IDD.AMENDMENTREASON        AS AmendmentReason,
                                                    IDD.AMENDMENTDATE          AS AmendmentDate,
                                                    IDD.CREATEDBYID            AS CreatedById,
                                                    IDD.CREATEDON              AS CreatedOn,
                                                    IDD.MODIFIEDBYID           AS ModifiedById,
                                                    IDD.MODIFIEDON             AS ModifiedOn,
                                                    IDD.TENANTID               AS TenantId
                                                FROM TICEDATADETAIL IDD
                                                INNER JOIN TICEDATA ID ON ID.ICEDATAID = IDD.ICEDATAID
                                                WHERE IDD.ICEDATADETAILID = @icedatadetailid;
                                                ";

        public const string SAVE_ICEDATA = @"INSERT INTO TICEDATA
                                   (
                                       ICEDATANUMBER,
                                       REFERENCENUMBER,
                                       ENTITYID,
                                       MODELMAPID,
                                       ICETEMPLATEID,
                                       ICEDATASTATUS,
                                       CONFIDENCEPERCENT,
                                       DISPLAYDATA,
                                       ORIGINALFILENAME,
                                       FILEURL,
                                       FILEBUFFER,
                                       EXTRACTEDTEXT,
                                       SOURCEDATA,
                                       VALIDATIONERRORS,
                                       CREATEDBYID,
                                       CREATEDON,
                                       MODIFIEDBYID,
                                       MODIFIEDON,
                                       TENANTID
                                   )
                                   VALUES
                                   (
                                       @IceDataNumber,
                                       @ReferenceNumber,
                                       @EntityId,
                                       @ModelMapId,
                                       @IceTemplateId,
                                       @IceDataStatus,
                                       @ConfidencePercent,
                                       @DisplayData,
                                       @OriginalFileName,
                                       @FileURL,
                                       @FileBuffer,
                                       @ExtractedText,
                                       @SourceData,
                                       @ValidationErrors,
                                       @CreatedById,
                                       @CreatedOn,
                                       @ModifiedById,
                                       @ModifiedOn,
                                       @TenantId
                                   );
                                   SELECT CAST(SCOPE_IDENTITY() AS INT);";

        public const string UPDATE_ICEDATA_Full = @"UPDATE TICEDATA
                                   SET
                                       ICEDATANUMBER       = @IceDataNumber,
                                       REFERENCENUMBER     = @ReferenceNumber,
                                       ENTITYID            = @EntityId,
                                       MODELMAPID          = @ModelMapId,
                                       ICETEMPLATEID       = @IceTemplateId,
                                       ICEDATASTATUS       = @IceDataStatus,
                                       CONFIDENCEPERCENT   = @ConfidencePercent,
                                       DISPLAYDATA         = @DisplayData,
                                       ORIGINALFILENAME    = @OriginalFileName,
                                       FILEURL             = @FileURL,
                                       FILEBUFFER          = @FileBuffer,
                                       EXTRACTEDTEXT       = @ExtractedText,
                                       SOURCEDATA          = @SourceData,
                                       VALIDATIONERRORS    = @ValidationErrors,
                                       MODIFIEDBYID        = @ModifiedById,
                                       MODIFIEDON          = @ModifiedOn,
                                       TENANTID            = @TenantId
                                   WHERE
                                       ICEDATAID = @IceDataId;";

        public const string SAVE_ICE_DATA_DETAIL = @"INSERT INTO TICEDATADETAIL
(
    ICEDATAID,
    SLNO,
    DETAILREFERENCENUMBER,
    ICEDETAILSTATUS,
    DISPLAYDETAIL,
    DETAILSOURCEDATA,
    CONVERTEDDATA,
    DETAILVALIDATIONERRORS,
    TARGETRESPONSE,
    TARGETID,
    TARGETREFERENCE,
    CURRENTBIZPROCESSSTAGEID,
    REVIEWNOTES,
    REVIEWEDBYID,
    ORIGINALICEDATADETAILID,
    ORIGINALREFERENCENUMBER,
    AMENDMENTSLNO,
    AMENDMENTREASON,
    AMENDMENTDATE,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    TENANTID
)
VALUES
(
    @IceDataId,
    @SLNO,
    @DetailReferenceNumber,
    @IceDetailStatus,
    @DisplayDetail,
    @DetailSourceData,
    @ConvertedData,
    @DetailValidationErrors,
    @TargetResponse,
    @TargetId,
    @TargetReference,
    @CurrentBizProcessStageId,
    @ReviewNotes,
    @ReviewedById,
    @OriginalIceDataDetailId,
    @OriginalReferenceNumber,
    @AmendmentSLNO,
    @AmendmentReason,
    @AmendmentDate,
    @CreatedById,
    @CreatedOn,
    @ModifiedById,
    @ModifiedOn,
    @TenantId
);";

        public const string UPDATE_ICE_DATA_DETAIL = @"UPDATE TICEDATADETAIL
                                           SET
                                               SLNO                        = @SLNO,
                                               DETAILREFERENCENUMBER       = @DetailReferenceNumber,
                                               ICEDETAILSTATUS             = @IceDetailStatus,
                                               DISPLAYDETAIL               = @DisplayDetail,
                                               DETAILSOURCEDATA            = @DetailSourceData,
                                               CONVERTEDDATA               = @ConvertedData,
                                               DETAILVALIDATIONERRORS      = @DetailValidationErrors,
                                               TARGETRESPONSE              = @TargetResponse,
                                               TARGETID                    = @TargetId,
                                               TARGETREFERENCE             = @TargetReference,
                                               CURRENTBIZPROCESSSTAGEID    = @CurrentBizProcessStageId,
                                               REVIEWNOTES                 = @ReviewNotes,
                                               REVIEWEDBYID                = @ReviewedById,
                                               ORIGINALICEDATADETAILID     = @OriginalIceDataDetailId,
                                               ORIGINALREFERENCENUMBER     = @OriginalReferenceNumber,
                                               AMENDMENTSLNO               = @AmendmentSLNO,
                                               AMENDMENTREASON             = @AmendmentReason,
                                               AMENDMENTDATE               = @AmendmentDate,
                                               MODIFIEDBYID                = @ModifiedById,
                                               MODIFIEDON                  = @ModifiedOn,
                                               TENANTID                    = @TenantId
                                           WHERE
                                               ICEDATADETAILID = @IceDataDetailId
                                               AND ICEDATAID = @IceDataId;";

        public const string GET_ICEDATA_COUNT = @"SELECT COUNT(1) 
                                       FROM TICEDATA 
                                       WHERE ENTITYID = @EntityId 
                                       AND MODELMAPID = @ModelMapId 
                                       AND ICETEMPLATEID = @IceTemplateId 
                                       AND ICEDATAID <> @IceDataId
                                       AND ENTITYID > 0
                                       AND MODELMAPID > 0
                                       AND ICETEMPLATEID > 0;";

        public const string UPDATE_ICEDATA = @"UPDATE TICEDATA
                                   SET
                                       ICEDATANUMBER       = @IceDataNumber,
                                       REFERENCENUMBER     = @ReferenceNumber,
                                       ENTITYID            = @EntityId,
                                       MODELMAPID          = @ModelMapId,
                                       ICETEMPLATEID       = @IceTemplateId,
                                       ICEDATASTATUS       = @IceDataStatus,
                                       CONFIDENCEPERCENT   = @ConfidencePercent,
                                       DISPLAYDATA         = @DisplayData,
                                       EXTRACTEDTEXT       = @ExtractedText,
                                       SOURCEDATA          = @SourceData,
                                       VALIDATIONERRORS    = @ValidationErrors,
                                       MODIFIEDBYID        = @ModifiedById,
                                       MODIFIEDON          = @ModifiedOn,
                                       TENANTID            = @TenantId
                                   WHERE
                                       ICEDATAID = @IceDataId;";

        public const string GET_SELECTLIST_ICEDATA_Full = @"SELECT
                                                    -- Parent : TICEDATA
                                                    ID.ICEDATAID                AS IceDataId,
                                                    ID.ICEDATANUMBER            AS IceDataNumber,
                                                    ID.REFERENCENUMBER          AS ReferenceNumber,
                                                    ID.ENTITYID                 AS EntityId,
                                                    ID.MODELMAPID               AS ModelMapId,
                                                    ID.ICETEMPLATEID            AS IceTemplateId,
                                                    ID.ICEDATASTATUS            AS IceDataStatus,
                                                    ID.CONFIDENCEPERCENT        AS ConfidencePercent,
                                                    ID.DISPLAYDATA              AS DisplayData,
                                                    ID.ORIGINALFILENAME         AS OriginalFileName,
                                                    ID.FILEURL                  AS FileURL,
                                                    ID.FILEBUFFER               AS FileBuffer,
                                                    ID.EXTRACTEDTEXT            AS ExtractedText,
                                                    ID.SOURCEDATA               AS SourceData,
                                                    ID.VALIDATIONERRORS         AS ValidationErrors,
                                                    ID.CREATEDBYID              AS CreatedById,
                                                    ID.CREATEDON                AS CreatedOn,
                                                    ID.MODIFIEDBYID             AS ModifiedById,
                                                    ID.MODIFIEDON               AS ModifiedOn,
                                                    ID.TENANTID                 AS TenantId,
                                                    -- Child : TICEDATADETAIL
                                                    IDD.ICEDATADETAILID             AS IceDataDetailId,
                                                    IDD.ICEDATAID                   AS IceDataId,
                                                    IDD.SLNO                        AS SLNO,
                                                    IDD.DETAILREFERENCENUMBER       AS DetailReferenceNumber,
                                                    IDD.ICEDETAILSTATUS             AS IceDetailStatus,
                                                    IDD.DISPLAYDETAIL               AS DisplayDetail,
                                                    IDD.DETAILSOURCEDATA            AS DetailSourceData,
                                                    IDD.CONVERTEDDATA               AS ConvertedData,
                                                    IDD.DETAILVALIDATIONERRORS      AS DetailValidationErrors,
                                                    IDD.TARGETRESPONSE              AS TargetResponse,
                                                    IDD.TARGETID                    AS TargetId,
                                                    IDD.TARGETREFERENCE             AS TargetReference,
                                                    IDD.CURRENTBIZPROCESSSTAGEID    AS CurrentBizProcessStageId,
                                                    IDD.REVIEWNOTES                 AS ReviewNotes,
                                                    IDD.REVIEWEDBYID                AS ReviewedById,
                                                    IDD.ORIGINALICEDATADETAILID     AS OriginalIceDataDetailId,
                                                    IDD.ORIGINALREFERENCENUMBER     AS OriginalReferenceNumber,
                                                    IDD.AMENDMENTSLNO               AS AmendmentSLNO,
                                                    IDD.AMENDMENTREASON             AS AmendmentReason,
                                                    IDD.AMENDMENTDATE               AS AmendmentDate,
                                                    IDD.CREATEDBYID                 AS CreatedById,
                                                    IDD.CREATEDON                   AS CreatedOn,
                                                    IDD.MODIFIEDBYID                AS ModifiedById,
                                                    IDD.MODIFIEDON                  AS ModifiedOn,
                                                    IDD.TENANTID                    AS TenantId

                                                FROM TICEDATA ID
                                                LEFT JOIN TICEDATADETAIL IDD ON ID.ICEDATAID = IDD.ICEDATAID
                                                ORDER BY ID.ICEDATAID, IDD.ICEDATADETAILID;";

        public const string GET_SELECTLIST_ICEDATA = @"
                                                        SELECT
                                                            ID.ICEDATAID         AS IceDataId,
                                                            ID.ICEDATANUMBER     AS IceDataNumber,
                                                            ID.REFERENCENUMBER   AS ReferenceNumber,
                                                            ID.ENTITYID          AS EntityId,
                                                            ID.MODELMAPID        AS ModelMapId,
                                                            ID.ICETEMPLATEID     AS IceTemplateId,
                                                            ID.ICEDATASTATUS     AS IceDataStatus,
                                                            ID.CONFIDENCEPERCENT AS ConfidencePercent,
                                                            ID.DISPLAYDATA       AS DisplayData,
                                                            ID.ORIGINALFILENAME  AS OriginalFileName,
                                                            ID.FILEURL           AS FileURL,
                                                            ID.FILEBUFFER        AS FileBuffer,
                                                            ID.EXTRACTEDTEXT     AS ExtractedText,
                                                            ID.SOURCEDATA        AS SourceData,
                                                            ID.VALIDATIONERRORS  AS ValidationErrors,
                                                            ID.CREATEDBYID       AS CreatedById,
                                                            ID.CREATEDON         AS CreatedOn,
                                                            ID.MODIFIEDBYID      AS ModifiedById,
                                                            ID.MODIFIEDON        AS ModifiedOn,
                                                            ID.TENANTID          AS TenantId
                                                        FROM TICEDATA ID
                                                        ORDER BY ID.ICEDATAID;
                                                        ";

        public const string GET_SELECTLIST_ICEDATA_DETAIL = @"
                                                        SELECT
                                                            IDD.ICEDATADETAILID         AS IceDataDetailId,
                                                            IDD.ICEDATAID               AS IceDataId,
                                                            IDD.SLNO                    AS SLNO,
                                                            IDD.DETAILREFERENCENUMBER   AS DetailReferenceNumber,
                                                            IDD.ICEDETAILSTATUS         AS IceDetailStatus,
                                                            IDD.DISPLAYDETAIL           AS DisplayDetail,
                                                            IDD.DETAILSOURCEDATA        AS DetailSourceData,
                                                            IDD.CONVERTEDDATA           AS ConvertedData,
                                                            IDD.DETAILVALIDATIONERRORS  AS DetailValidationErrors,
                                                            IDD.TARGETRESPONSE          AS TargetResponse,
                                                            IDD.TARGETID                AS TargetId,
                                                            IDD.TARGETREFERENCE         AS TargetReference,
                                                            IDD.CURRENTBIZPROCESSSTAGEID AS CurrentBizProcessStageId,
                                                            IDD.REVIEWNOTES             AS ReviewNotes,
                                                            IDD.REVIEWEDBYID            AS ReviewedById,
                                                            IDD.ORIGINALICEDATADETAILID AS OriginalIceDataDetailId,
                                                            IDD.ORIGINALREFERENCENUMBER AS OriginalReferenceNumber,
                                                            IDD.AMENDMENTSLNO           AS AmendmentSLNO,
                                                            IDD.AMENDMENTREASON         AS AmendmentReason,
                                                            IDD.AMENDMENTDATE           AS AmendmentDate,
                                                            IDD.CREATEDBYID             AS CreatedById,
                                                            IDD.CREATEDON               AS CreatedOn,
                                                            IDD.MODIFIEDBYID            AS ModifiedById,
                                                            IDD.MODIFIEDON              AS ModifiedOn,
                                                            IDD.TENANTID                AS TenantId
                                                        FROM TICEDATADETAIL IDD
                                                        ORDER BY IDD.ICEDATADETAILID;
                                                        ";

        public const string DELETE_ICEDATA = @"DELETE FROM TICEDATA 
                                            WHERE ICEDATAID = @icedataid;";

        public const string DELETE_ICE_DATA_DETAIL = @"DELETE FROM TICEDATADETAIL 
                                                    WHERE ICEDATAID = @icedataid;";

        public const string DELETE_ICE_DATA_DETAIL_BY_ID = @"DELETE FROM TICEDATADETAIL 
                                                          WHERE ICEDATADETAILID = @icedatadetailid;";
    }
}