namespace AdminDAL.Query.BulkIceImport
{
    public static class BulkIceImportQB
    {
        // Returns header + detail rows for a given BulkIceId.
        // One row per MBULKICEDETAIL record (ordered by SLNO).
        // @TenantId is populated automatically by IQueryExecutor from LoginDTO.
        public const string GET_BULKICE_CONFIG = @"
            SELECT
                BI.BULKICEID              AS BulkIceId,
                BI.BULKICECODE            AS BulkIceCode,
                BI.BULKICENAME            AS BulkIceName,
                BI.NUMBEROFFILES          AS NumberOfFiles,
                BI.TOTYPE                 AS ToType,
                BI.CUSTOMPROCEDURENAME    AS CustomProcedure,
                BI.SKIPOVERWRITE          AS SkipOverWrite,
                BI.APPROVALDURINGIMPORT   AS ApprovalDuringImport,
                BI.POSTACTIONKEY          AS PostActionKey,
                BID.BULKICEDETAILID       AS BulkIceDetailId,
                BID.SLNO                  AS SlNo,
                BID.FILETEMPLATE          AS FileTemplate,
                BID.TEMPTABLENAME         AS TempTableName
            FROM  MBULKICE       BI
            INNER JOIN MBULKICEDETAIL BID ON BI.BULKICEID = BID.BULKICEID
            WHERE BI.BULKICEID = @BulkIceId
            AND   BI.TENANTID  = @TenantId
            ORDER BY BID.SLNO";

        // Validation batch — no ROWNUMBER column in the stored procedure output.
        // Used first; falls back to WITH_ROWNUM variant on column-count mismatch.
        public const string VALIDATION_BATCH_NO_ROWNUM = @"
            IF OBJECT_ID('tempdb..#BulkIceValidation') IS NOT NULL
                DROP TABLE #BulkIceValidation;

            CREATE TABLE #BulkIceValidation (
                VALIDATIONCODE  NVARCHAR(200),
                VALIDATIONNAME  NVARCHAR(500),
                VALIDATIONVALUE NVARCHAR(MAX)
            );

            INSERT INTO #BulkIceValidation
            EXEC PROC_BULK_ICE_DATA_VALIDATE @BULKICEIID = @BulkIceId, @USERID = @UserId;

            SELECT VALIDATIONCODE  AS ValidationCode,
                   VALIDATIONNAME  AS ValidationName,
                   VALIDATIONVALUE AS ValidationValue
            FROM   #BulkIceValidation;";

        // Validation batch — includes ROWNUMBER column.
        // Used when PROC_BULK_ICE_DATA_VALIDATE returns a row-number column.
        public const string VALIDATION_BATCH_WITH_ROWNUM = @"
            IF OBJECT_ID('tempdb..#BulkIceValidation') IS NOT NULL
                DROP TABLE #BulkIceValidation;

            CREATE TABLE #BulkIceValidation (
            
                VALIDATIONCODE  NVARCHAR(200),
                ROWNUMBER       INT,
                VALIDATIONNAME  NVARCHAR(500),
                VALIDATIONVALUE NVARCHAR(MAX)
            );

            INSERT INTO #BulkIceValidation
            EXEC PROC_BULK_ICE_DATA_VALIDATE @BULKICEIID= @BulkIceId, @USERID = @UserId;

            SELECT ROWNUMBER       AS RowNumber,
                   VALIDATIONCODE  AS ValidationCode,
                   VALIDATIONNAME  AS ValidationName,
                   VALIDATIONVALUE AS ValidationValue
            FROM   #BulkIceValidation;";

        // Output table name is a GUID-based dynamic identifier generated by BLL — safe to embed.
        public static string GetOutputTableSql(string guid)
            => $"SELECT * FROM [OUTPUT_{guid}]";
    }
}