AI Insights [Pending Indent, IIRS Consumption, BOM, Cash Bank Balances]

dash.DashboardIirs(id INT IDENTITY(1,1) PRIMARY KEY, ItemId NVARCHAR(6), ItemShortName NVARCHAR(100), ItemName NVARCHAR(150), ItemGroupCode NVARCHAR(3), ItemGroupName NVARCHAR(50), InputStageCode NVARCHAR(2), InputStageName NVARCHAR(25), qty NUMERIC(12,3), rate NUMERIC(12,4), amount NUMERIC(12,2), LeadTime NUMERIC(12,2), ItemSegmentCode NVARCHAR(3), ItemSegmentName NVARCHAR(75), YearLabel NVARCHAR(7)), dash.DashboardInputWise(id INT IDENTITY(1,1) PRIMARY KEY, ItemId NVARCHAR(6), ItemShortName NVARCHAR(100), ItemName NVARCHAR(150), ItemGroupCode NVARCHAR(3), ItemGroupName NVARCHAR(50), InputStageCode NVARCHAR(2), InputStageName NVARCHAR(25), iirsconsumptionqty NUMERIC(12,3), consumptionqty NUMERIC(12,3), rate NUMERIC(12,4), iirsamount NUMERIC(12,2), bomamount NUMERIC(12,2), ItemSegmentCode NVARCHAR(3), ItemSegmentName NVARCHAR(75), YearLabel NVARCHAR(7)), dash.DashboardBom(id INT IDENTITY(1,1) PRIMARY KEY, BomNo NVARCHAR(6), BomDate DATETIME, productid NVARCHAR(6), ProductShortName NVARCHAR(100), ProductGroupCode NVARCHAR(3), ProductGroupName NVARCHAR(50), ProcessName NVARCHAR(30), ItemGroupCode NVARCHAR(3), ItemGroupName NVARCHAR(50), itemid NVARCHAR(6), ItemShortName NVARCHAR(100), UomCode NVARCHAR(2), UomName NVARCHAR(5), InputStageCode NVARCHAR(2), InputStageName NVARCHAR(25), qty NUMERIC(12,3), rate NUMERIC(12,4), value NUMERIC(12,2), ismanualratename NVARCHAR(3), inputtypename NVARCHAR(2), level NVARCHAR(1), refbomno NVARCHAR(6), identification NVARCHAR(150), RackInfo NVARCHAR(200), IndentDate DATETIME, LinkedCustomerCode NVARCHAR(40)), dash.DashboardCashBankBook(id INT IDENTITY(1,1) PRIMARY KEY, voucherno NVARCHAR(6), voucherdate DATETIME, bankcode NVARCHAR(6), bankname NVARCHAR(150), vouchersubno NVARCHAR(6), cheqno NVARCHAR(30), cheqdate DATETIME, drawnon NVARCHAR(30), ind NVARCHAR(1), receipt NUMERIC(11,2), payment NUMERIC(11,2), amount NUMERIC(11,2), balance NUMERIC(11,2), refdoc NVARCHAR(2), refno NVARCHAR(6), refdate DATETIME, refacname NVARCHAR(100), valuedate DATETIME)

CREATE TABLE dash.DashboardIirs
(
    id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    ItemId NVARCHAR(6),
    ItemShortName NVARCHAR(100),
    ItemName NVARCHAR(150),
    ItemGroupCode NVARCHAR(3),
    ItemGroupName NVARCHAR(50),
    InputStageCode NVARCHAR(2),
    InputStageName NVARCHAR(25),
    qty NUMERIC(12,3),
    rate NUMERIC(12,4),
    amount NUMERIC(12,2),
    LeadTime NUMERIC(12,2),
    ItemSegmentCode NVARCHAR(3),
    ItemSegmentName NVARCHAR(75),
    YearLabel NVARCHAR(7)
)

CREATE TABLE dash.DashboardInputWise
(
    id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    ItemId NVARCHAR(6),
    ItemShortName NVARCHAR(100),
    ItemName NVARCHAR(150),
    ItemGroupCode NVARCHAR(3),
    ItemGroupName NVARCHAR(50),
    InputStageCode NVARCHAR(2),
    InputStageName NVARCHAR(25),
    iirsconsumptionqty NUMERIC(12,3),
    consumptionqty NUMERIC(12,3),
    rate NUMERIC(12,4),
    iirsamount NUMERIC(12,2),
    bomamount NUMERIC(12,2),
    ItemSegmentCode NVARCHAR(3),
    ItemSegmentName NVARCHAR(75),
    YearLabel NVARCHAR(7)
)


CREATE TABLE dash.DashboardBom
(
    id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    BomNo NVARCHAR(6),
    BomDate DATETIME,
    productid NVARCHAR(6),
    ProductShortName NVARCHAR(100),
    ProductGroupCode NVARCHAR(3),
    ProductGroupName NVARCHAR(50),
    ProcessName NVARCHAR(30),
    ItemGroupCode NVARCHAR(3),
    ItemGroupName NVARCHAR(50),
    itemid NVARCHAR(6),
    ItemShortName NVARCHAR(100),
    UomCode NVARCHAR(2),
    UomName NVARCHAR(5),
    InputStageCode NVARCHAR(2),
    InputStageName NVARCHAR(25),
    qty NUMERIC(12,3),
    rate NUMERIC(12,4),
    value NUMERIC(12,2),
    ismanualratename NVARCHAR(3),
    inputtypename NVARCHAR(2),
    level NVARCHAR(1),
    refbomno NVARCHAR(6),
    identification NVARCHAR(150),
    RackInfo NVARCHAR(200),
    IndentDate DATETIME,
    LinkedCustomerCode NVARCHAR(40)
)


CREATE TABLE dash.DashboardCashBankBook
(
    id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    voucherno NVARCHAR(6),
    voucherdate DATETIME,
    bankcode NVARCHAR(6),
    bankname NVARCHAR(150),
    vouchersubno NVARCHAR(6), 
    cheqno NVARCHAR(30),
    cheqdate DATETIME,
    drawnon NVARCHAR(30), 
    ind NVARCHAR(1),
    receipt NUMERIC(11,2),
    payment NUMERIC(11,2),
    amount NUMERIC(11,2),
    balance NUMERIC(11,2),
    refdoc NVARCHAR(2),
    refno NVARCHAR(6), 
    refdate DATETIME,
    refacname NVARCHAR(100), 
    valuedate DATETIME
)

using System.Text.Json;
using ErpCrystal_MFG.Models;
using Microsoft.AspNetCore.Components;
using Microsoft.JSInterop;
namespace ErpCrystal_MFG.Web.Services
{
    public class AIInsightsService : IAIInsightsService
    {
        private readonly IHttpClientFactory _IHttpClientFactory;
        private readonly HttpClient _HttpClient;
        private readonly IJSRuntime _JS;
        private readonly HttpClient _OpenAIHttpClient;
        private readonly string _model;
        private readonly string _schema;

        public AIInsightsService(IHttpClientFactory httpClientfactory, IJSRuntime JS, IConfiguration config)
        {
            _IHttpClientFactory = httpClientfactory;
            _HttpClient = _IHttpClientFactory.CreateClient("mfgapi");
            _JS = JS;
            _OpenAIHttpClient = _IHttpClientFactory.CreateClient("openai");
            _model = config["OpenAI:Model"] ?? "gpt-4o";
            _schema = config["OpenAI:Schema"] ?? "";
        }

        // public async Task<Dashboard?> SalesData(string prompt, string dbname)
        // {
        //     var response = await _HttpClient.PostAsJsonAsync($"/api/aiinsights/salesdata/{dbname}", new { prompt });

        //     if (!response.IsSuccessStatusCode)
        //     {
        //         return new Dashboard { Text = $"Error: {response.StatusCode}" };
        //     }

        //     var result = await response.Content.ReadFromJsonAsync<Dashboard>(
        //         new JsonSerializerOptions { PropertyNameCaseInsensitive = true });

        //     if (result != null)
        //     {
        //         if (!string.IsNullOrEmpty(result.ChartJson))
        //         {
        //             // Call single chart
        //             await _JS.InvokeVoidAsync("renderSingleChart", result.ChartJson);
        //         }
        //         else if (result.ChartJsons != null && result.ChartJsons.Count > 0)
        //         {
        //             // Call multiple charts
        //             await _JS.InvokeVoidAsync("renderCharts", result.ChartJsons);
        //         }
        //     }

        //     return result;
        // }

        public async Task<string> GenerateSqlQueryFromPrompt(string userPrompt,string? dbname)
        {
            var purchaseSystemPrompt = @"Table 4: DashboardPurchase
            Use for ALL purchase-related queries:
            - Purchases, procurement, buying
            - Goods Received Notes (GRN) if the intent is purchase quantity, rate, amount, or trends
            - Item-wise / supplier-wise purchase analysis
            - Purchase quantities for any item or itemid
            - Yearly, monthly, quarterly purchase comparisons
            - GST, invoice-level purchase insights
            - Rate deviation, ceiling price checks

            This is the PRIMARY table for all purchase quantity or purchase trend questions.
            Never use DashboardGrn for purchase analytics.";

            var orderSystemPrompt = @"Table 5: DashboardOrder 
            Use for queries about:
            Sales orders and line-level order details
            Customer- and product-wise order analysis
            Order quantity, rate, and total value trends
            Delivery planning and order type monitoring
            Sales representative and customer performance tracking
            Key columns include:
            OrderNo, OrderDate, PoNo, ItemName, ItemGroupName, BrandName, OrderQty, 
            OrderRate, OrderAmount, RequiredByDate, OrderStatusName, OrderTypeName, 
            PartyName, SalesmenName, GstRateName, UnitName
            Filtering Rules:
            Consider only records with Ind = 'O' for Order Register
            Consider only records with Ind = 'P' for Pending Orders
            Use Order Register (Ind = 'O') for all total calculations unless user explicitly mentions pending indents.";

            var indentSystemPrompt = @"Table 6: DashboardIndent
            Use this table for:
            - Purchase indents and material requisitions
            - Indent approval and pending status analysis
            - Item-, department-, and unit-wise indent tracking
            - Authorized vs non-authorized indent insights

            Key Columns:
            IndentNo, IndentDate, UnitCodeName, PartyName, ItemName,Qty,Currency,PendingQty,Rate

            Filtering Rules:
            - Ind = 'I' → Indent Register
            - Ind = 'P' → Pending Indents
            Use Indent Register (Ind = 'I') for all total calculations unless user explicitly mentions pending indents.";
            
            var iirsSystemPrompt = @"Table 7: DashboardIirs
            Use for queries about:
            - Material consumption, IIRS usage, costing, and item-wise analysis
            - Qty, Rate, Value, Lead Time, and item group/segment behavior

            Key columns:
            ItemSegmentName, ItemGroupName, ItemId, ItemShortName, InputStageName, 
            Rate, LeadTime, Qty, Amount, Yearlabel

            Business Rules:
            - Use when the user asks about Qty, Rate, Value totals, Lead Time,
            or item segment/group analysis.
            - For queries asking for **IIRS** 
            - If user asks for data based on **year** or **years** then utilize the YearLabel column

            Year Filter Rule:
            - Always normalize any user-provided financial year (e.g., 'FY 2024-25', 'FY24-25', '2024-25', '2024')
            to the format used in the Yearlabel column (i.e. '2024-25','2025-26') etc.";

            var bomSystemPrompt = @"Table 8: DashboardBom
            Use for queries about:
            - **Bill of Materials (BOM) structure and hierarchy** (Levels, Item Grouping, Process Flow).
            - **Finished Goods (FG) and their component items** (Product details, Item details).
            - **Component costing and usage metrics** (Qty, Rate, Value).
            - **BOM Process/operation details** (Process Name, Input Stage, Input Type).
            - **Reference and identification markers** (Ref-Bom, Identification, Rack Info, Linked Customer Code).
            
            Key columns include:
            BomNo, BomDate, ProductId, ProductShortName, ProductGroupName, 
            ProcessName, ItemGroupName, ItemShortName, InputTypeName, Level, 
            qty, rate, value, refbomno, Identification, RackInfo, LinkedCustomerCode,
            IsManualRateName

            Business Rules:
            - For queries asking for **total cost, item cost, or unit consumption** 
            (e.g., total Value, Qty per Product, Item Rate), always use the metrics columns: **qty, rate, value**.
            - For queries asking for **structural details, grouping, or reference information** 
            (e.g., BOM hierarchy, items at a specific Level, Process Name, Linked Customer Code), 
            primarily use the descriptive columns: **Process Name, Item Group Name, InputType, 
            Level, RefBom, Identification, RackInfo**.
            - **Prioritize Product context**: All analysis on 'Qty', 'Rate', and 'Value' 
            must be linked back to the parent Product Id/Name for correct interpretation.
            - For queries asking for **manual** check ismanualratename = 'Yes'

            Purpose:
            Ensure accurate retrieval of BOM component structure, process details, and calculation of product cost based on the Bill of Materials";

            var cashBankBookSystemPrompt = @"Table 9: DashboardCashBankBook
            Use for queries about:
            Cash and bank ledger transactions
            Daily receipts, payments, and running balance
            Voucher-wise cash/bank activity
            Cheque/UTR-based transactions
            Tracking deposits, withdrawals, and fund movement
            Document references (Ref Doc, Ref No., Ref Date)
            Party/account details involved in transactions
            Identifying high-value transactions
            Verifying clearing dates through Value Date
            Analysing account-wise cash/bank flow over a date range
            Key columns include:
            BankCode,BankName,VoucherSubNo, VoucherNo,
            VoucherDate, RefAcName, CheqNo,
            CheqDate, RefDoc, RefNo, RefDate, DrawnOn,
            Amount, Receipt, Payment, Balance, ValueDate
            Filtering Rules:
            - Opening Balance: Filter with ind = 'O'
            - Payment : Filter with ind = 'P'
            - Receipt : Filter with ind = 'R'
            Ref Doc (refdoc):
            OA = On Account
            AD = Advance Payment
            SI = Sales Invoice
            PB = Purchase Bill
            Business Rules:
            - For queries asking regarding **bank**
            - Consider Opening Balance (Ind = 'O') for calculations only and not while returning transactions 
            unless in queries **Opening Balance** or **Opg Bal** is mentioned.
            - Always show year month in 'MMM-yy' format.";

            var inputWiseSystemPrompt = @"Table 10: DashboardInputWise
            Use for queries about:
            - BOM vs IIRS comparison, variance, and deviation detection
            - Qty, Rate, Value, and item group/segment behavior

            Key columns:
            ItemSegmentName, ItemGroupName, ItemId, ItemShortName, InputStageName, 
            Rate, ConsumptionQty, IirsConsumptionQty, 
            BomAmount, IirsAmount, Yearlabel

            Business Rules:
            - Use when the user asks about Consumption, IIRS Consumption, 
            BOM Value, IIRS Value, or any variance/mismatch/deviation.
            - If the question involves comparison or variance use it.
            - For queries asking for **IIRS Consumption**
            - If user asks for data based on **year** or **years** then utilize the YearLabel column

            Year Filter Rule:
            - Always normalize any user-provided financial year (e.g., 'FY 2024-25', 'FY24-25', '2024-25', '2024')
            to the format used in the Yearlabel column (i.e. '2024-25','2025-26') etc.";

            var systemPrompt = $@"Optimized System Prompts for SQL Query Generation
            Core Identity and Scope
            You are an expert Microsoft SQL Server query generator. Your sole purpose is to convert natural language questions into single, read-only SELECT queries for the 'dash' schema.

            Critical Rules:

            Generate ONLY valid T-SQL SELECT statements
            Access ONLY the 'dash' schema
            If a query is ambiguous, unclear, or impossible with available schema, respond with: ""INVALID""
            Never generate DML (INSERT, UPDATE, DELETE), DDL (CREATE, ALTER, DROP), or stored procedures
            Use valid T-SQL syntax compatible with Microsoft SQL Server.
            Aliased columns must be referenced in ORDER BY by alias, not by reusing aggregate expressions.

            SQL Syntax and Structure Rules
            1. Query Complexity
            Use simple SELECT queries when sufficient (no unnecessary CTEs)
            Use CTEs (Common Table Expressions) only when:
            Multiple aggregation steps are needed
            Intermediate calculations improve readability
            Complex joins or subqueries benefit from named references
            When referencing item name, party name strip off leading/trailing spaces, for example, LTRIM(RTRIM(itemname))

            2. Column Naming and Aliases
            Replace spaces in column aliases with underscores: 'Total Amount' → Total_Amount
            Use descriptive, business-friendly aliases
            Avoid SQL reserved keywords in aliases (e.g., don't use ORDER, DATE, GROUP as aliases)

            3. Data Types and Handling
            Treat document numbers as strings: Doc No, Ref No, Invoice No, GRN No, etc.
            Always check for NULL or zero values before division or average calculations
            Use ISNULL() or COALESCE() for NULL handling
            Format monetary values to 2 decimal places: CAST(amount AS DECIMAL(18,2))
            Use standard date literals: 'dd-MMM-yy' format

            4. Result Limitations
            Default row limit: If no TOP/LIMIT specified by user, return TOP 20 rows
            Apply TOP clause after all calculations and aggregations are complete
            Only include essential columns - avoid SELECT *

            5. Date Filtering
            Accept flexible date inputs (e.g., ""last month"", ""this year"", ""Q1 2024"")
            Convert to SQL Server date functions: DATEADD(), DATEDIFF(), YEAR(), MONTH()
            Always use proper date comparisons with time stripped if needed
            Financial Year (FY) Rules — STRICT
            - All year-based queries must use Financial Year (FY), not calendar year.
            - FY always starts on April 1 and ends on March 31.
            - This applies whenever the user mentions a year, a range of years, or comparisons such as 2024 vs 2025.
            - Use calendar year only if the user explicitly says calendar year.

            FY Examples
            - 2024 means FY 2024 which is 01-Apr-2024 to 31-Mar-2025
            - 2024 vs 2025 means
                FY 2024: 01-Apr-2024 to 31-Mar-2025
                FY 2025: 01-Apr-2025 to 31-Mar-2026

            Date Filtering Rules
            - Always use BETWEEN for date ranges
            - Never use >=, >, <=, < for date ranges unless the user explicitly writes them
            - BETWEEN must always include both start and end dates
            - Always wrap date fields using CONVERT(DATE, column) to remove time component


            Business Terminology Mapping
            Common Synonyms (treat as equivalent):
            Purchases/Procurement: GRN, Goods Received Note, Goods Receipt, indents, challans
            Suppliers: Vendors, sellers, procurement parties
            Sales: Revenue, invoices, orders, customer transactions
            Customers: Buyers, clients
            Outstanding: Due, pending, unpaid, balance
            Overdue: Past due, late, aged
            Payments: Settlements, receipts (for receivables), disbursements (for payables)

            Table Selection Logic
            Table 1: DashboardGrn
            Use for queries about:
            Use ONLY for:
                - GRN-level document details
                - Pending quantities (received vs pending)
                - GRN tracking, GRN performance
                - Supplier receipt delays
                - Receipt discrepancies (Qty Received vs Qty Ordered)
            Key columns include: GRN No, Supplier Name, Item, Quantity, Pending Qty, Receipt Date
            DO NOT use DashboardGrn for:
                - Purchase totals
                - Purchase quantity analysis
                - Year-wise or month-wise purchase comparison
                - Purchase rate or amount calculations

            Table 2: DashboardSales
            Use for queries about:
            Sales, revenue, customer orders
            Sales invoices, sales transactions
            Customers, buyers
            Sales quantities, sales amounts
            Customer performance, revenue analysis
            Sales trends, top products
            Key columns include: Invoice No, Customer Name, Item, Sales Qty, Sales Amount, Invoice Date

            Table 3: DashboardArap (Accounts Receivable and Payable)
            Use for queries about:
            - Outstanding balances (receivable or payable)
            - Payment tracking, receipts, settlements
            - Aging analysis, overdue amounts
            - Credit/debit notes, adjustments
            - Advances, on-account payments
            
            Filtering Rules:
            - Receivables (AR): Filter with araptype = 'R'
            - Payables (AP): Filter with araptype = 'P'
            - Overdue/Aging Analysis: Use DATEDIFF(DAY, Refdate, GETDATE()) for age calculations
            
            Filters for Outstanding/Payable Queries:
            - For AR outstanding: WHERE araptype = 'R' AND netamount > 0
            - For AP payable: WHERE araptype = 'P' AND netamount > 0
            Example:
            Q: Show outstanding receivables aged 0-30 days
            SQL: SELECT CAST(SUM(netamount) AS DECIMAL(18,2)) 
            FROM dash.DashboardArap 
            WHERE araptype = 'R' AND netamount > 0 
            AND DATEDIFF(DAY, Refdate, GETDATE()) <= 30

            Field Definitions:
            Doc Type (doctype):
            I = Invoice
            B = Bill
            J = Journal Voucher
            R = Receipt
            P = Payment
            D = Debit Note
            C = Credit Note
            V = Voucher

            Ref Doc (refdoc):
            OA = On Account
            AD = Advance
            SI = Sales Invoice
            PB = Purchase Bill

            Key Analysis Patterns:
            For Receivables Analysis:
            Outstanding = SUM(NetAmount) where araptype = 'R'
            Include advances (AD) and on-account (OA) when reconciling
            Consider credit notes (C) and debit notes (D) for adjustments
            Group by: partyname, doctype, refdoc
            Sort by: highest outstanding or earliest duedate
            Top customers = highest outstanding balances

            For Payables Analysis:
            Outstanding = SUM(NetAmount) where araptype = 'P'
            Include advances (AD) and on-account (OA) for settlements
            Consider debit notes (D) and credit notes (C) for adjustments
            Identify upcoming payments by duedate
            Top vendors = highest outstanding balances

            Key Dashboard Indicators:
            Total Receivable = SUM(NetAmount) WHERE araptype = 'R'
            Total Payable = SUM(NetAmount) WHERE araptype = 'P'
            Net Amount = Total Receivable - Total Payable

            Segmentation Fields:
            partygroupname (party groups)
            partyregionname (regions)
            SalesmenName (salesperson)
            unitname (business units)
            
            {purchaseSystemPrompt}
            {orderSystemPrompt}
            {indentSystemPrompt}
            {iirsSystemPrompt}
            {bomSystemPrompt }
            {cashBankBookSystemPrompt}
            {inputWiseSystemPrompt}

            Query Optimization Guidelines
            Filter Early: Apply WHERE conditions (especially araptype, doctype, refdoc) before aggregations
            Limit Joins: Only join tables when absolutely necessary
            Index-Friendly: Use indexed columns in WHERE clauses when possible
            Avoid Functions on Indexed Columns: Instead of YEAR(date_column) = 2024, use date_column >= '2024-01-01' AND date_column < '2025-01-01'

            Error Handling and Edge Cases
            When to Return 'INVALID':
            Query references tables outside 'dash' schema
            Query requires data not available in schema
            Request involves data modification (INSERT, UPDATE, DELETE)
            Question is too ambiguous to map to specific tables/columns
            Query would require information not present in the database

            Ambiguity Resolution:
            If user asks about ""invoices"" without context:
            Check if they mean sales invoices (DashboardSales) or outstanding/due invoices (DashboardArap)
            Prefer DashboardSales for transaction details
            Prefer DashboardArap for outstanding/payment tracking
            If unsure which table, default to the most commonly used table for that business term

            Output Format
            Return only the SQL query with no explanations, markdown formatting, or additional text.
            
            Schema:{_schema}";

            var requestBody = new
            {
                model = _model,
                messages = new object[]
                {
                    new { role = "system", content = systemPrompt },
                    new { role = "user", content = userPrompt }
                }
            };

            var response = await _OpenAIHttpClient.PostAsJsonAsync("v1/chat/completions", requestBody);

            if (!response.IsSuccessStatusCode)
            {
                var error = await response.Content.ReadAsStringAsync();
                // Doing this as we are checking ex.Message.Contains("status code") in main layout so it will see the error is there
                throw new HttpRequestException($"OpenAI API request failed with status code {response.StatusCode}: {error}");
            }
            else
            {
                
                var openAIResponse = await response.Content.ReadFromJsonAsync<OpenAIResponse>();

                if (openAIResponse?.choices?.Length > 0 && openAIResponse.choices[0].message?.content != null)
                {
                    var sql = openAIResponse.choices[0].message.content.Trim();

                    if (!string.IsNullOrEmpty(sql) &&
                    !sql.Contains("INVALID", StringComparison.OrdinalIgnoreCase) &&
                    !sql.Contains("UNABLE_TO_GENERATE_SQL", StringComparison.OrdinalIgnoreCase))
                    {
                        // Remove newline characters from the SQL query
                        sql = sql.Replace(Environment.NewLine, " ").Replace("\n", " ").Replace("\r", " ");
                        return sql;
                    }
                }
                return string.Empty;
            }
        }

        public async Task<IEnumerable<dynamic>> GetDataFromSqlQuery(string sqlQuery, string? dbname)
        {
            var response = await _HttpClient.PostAsJsonAsync($"/api/aiinsights/getdatafromsqlquery/{dbname}", sqlQuery);

            if (!response.IsSuccessStatusCode)
            {
                var error = await response.Content.ReadAsStringAsync();
                throw new HttpRequestException($"API request to fetch query data failed with status code {response.StatusCode}: {error}");
            }

            var rows = await response.Content.ReadFromJsonAsync<IEnumerable<dynamic>>() ?? Enumerable.Empty<dynamic>();
            // Convert JsonElement to IDictionary<string, object> for easier consumption
            var convertedRows = rows.Select(row =>
            {
                if (row is JsonElement element)
                {
                    var dict = new Dictionary<string, object>();
                    foreach (var property in element.EnumerateObject())
                    {
                        dict[property.Name] = property.Value.ToString();
                    }
                    return dict;
                }
                return row;
            });

            return convertedRows;
        }

        public async Task AIInsightsLog(AIInsightsLog _AIInsightsLog, string? dbname)
        {
            await _HttpClient.PostAsJsonAsync($"/api/aiinsights/aiinsightslog/{dbname}", _AIInsightsLog);
        }
        
        public async Task<string> GetDbGroupCode(string? dbname)
        {
            var response = await _HttpClient.GetStringAsync($"/api/aiinsights/getdbgroupcode/{dbname}");
            return response;
        }

        public async Task<int> GetAIInsightsCredits(string dbGroupCode)
        {
            var response = await _HttpClient.GetFromJsonAsync<int>($"/api/aiinsights/getaiinsightscredits/{dbGroupCode}");
            return response;
        }

        public async Task UpdateAIInsightsCredit(string dbGroupCode)
        {
            var response = await _HttpClient.PostAsJsonAsync($"/api/aiinsights/updateaiinsightscredit/{dbGroupCode}", new { });
        }
        public async Task<AIInsightsExport> AIInsightsDownloadXl(AIInsightsExport exportData, string? dbname)
        {
            var response = await _HttpClient.PostJsonAsync<AIInsightsExport>
            ($"api/aiinsights/aiinsightsdownloadxl/{dbname}", exportData);
            return response;
        }
    }
}

using Coravel.Invocable;
using ErpCrystal_MFG.Api.Controllers;
using ErpCrystal_MFG.Api.Repositories;
using ErpCrystal_MFG.Models;


namespace ErpCrystal_MFG.Api.TaskScheduler
{
    public class AIInsightsScheduler
    (
        IEnumerable<string> dbnamelist,
        ISalesAnalysisReportRepository isalesanalysisreportrepository,
        IGrnAnalysisReportRepository igrnanalysisreportrepository,
        IArApReportRepository iarapreportrepository,
        IUtilityMethodsRepository iutilitymethodsrepository,
        IAIInsightsRepository iaiinsightsrepository,
        IEmailRepository iemailrepository,
        IPurchaseAnalysisReportRepository ipurchaseanalysisreportrepository,
        ISalesOrderAnalysisReportRepository isalesorderanalysisreportrepository,
        IIndentAnalysisReportRepository iindentanalysisreportrepository,
        IConsumptionOfMaterialReportRepository iconsumptionofmaterialreportrepository,
        IBomReportRepository ibomreportrepository,
        ICashBankReportRepository icashbankreportrepository

    ) : IInvocable
    {
        private readonly ISalesAnalysisReportRepository _ISalesAnalysisReportRepository = isalesanalysisreportrepository;
        private readonly IGrnAnalysisReportRepository _IGrnAnalysisReportRepository = igrnanalysisreportrepository;
        private readonly IArApReportRepository _IArApReportRepository = iarapreportrepository;
        private readonly IUtilityMethodsRepository _IUtilityMethodsRepository = iutilitymethodsrepository;
        private readonly IAIInsightsRepository _IAIInsightsRepository = iaiinsightsrepository;
        private readonly IEmailRepository _IEmailRepository = iemailrepository;
        private readonly IPurchaseAnalysisReportRepository _IPurchaseAnalysisReportRepository = ipurchaseanalysisreportrepository;
        private readonly ISalesOrderAnalysisReportRepository _ISalesOrderAnalysisReportRepository = isalesorderanalysisreportrepository;
        private readonly IIndentAnalysisReportRepository _IIndentAnalysisReportRepository = iindentanalysisreportrepository;
        private readonly IConsumptionOfMaterialReportRepository _IConsumptionOfMaterialReportRepository = iconsumptionofmaterialreportrepository;
        private readonly IBomReportRepository _IBomReportRepository = ibomreportrepository;
        private readonly ICashBankReportRepository _ICashBankReportRepository = icashbankreportrepository;


        public async Task Invoke()
        {
            foreach (var dbname in dbnamelist)
            {
                try
                {
                    var fyStartDate = await _IAIInsightsRepository.GetPreviousFyStartDate(dbname,"3");

                    // Dashboard Sales
                    SalesAnalysisReport _SalesAnalysisReport = new()
                    {
                        FromDate = fyStartDate,
                        ToDate = DateTime.Now.AddDays(-1).Date,
                        UnitCode = "00",
                        ReportFilter = "0"
                    };

                    await _ISalesAnalysisReportRepository.SalesByProductReport(_SalesAnalysisReport, dbname, "C");


                    // Dashboard GRN
                    GrnAnalysisReport _GrnAnalysisReport = new()
                    {
                        FromDate = fyStartDate,
                        ConsiderBillDateUpto = DateTime.Now.AddDays(-1).Date,
                        ToDate = DateTime.Now.AddDays(-1).Date,
                        UnitCode = "00",
                        ReportFilter = "0",
                        ReportType = "B"
                    };
                    await _IGrnAnalysisReportRepository.GrnReport(_GrnAnalysisReport, dbname, "C");

                    var arAp = new string[] { "R", "P" };
                    _IArApReportRepository.TruncateDashboardArAp(dbname);
                    // Dashboard ARAP
                    foreach (var arApType in arAp)
                    {
                        var controlAcList = await _IArApReportRepository.ControlAcCombo(dbname, arApType);
                        foreach (var controlAc in controlAcList)
                        {

                            var _ArApReport = new ArApReport
                            {
                                AgeDocType = "R",
                                ArApType = arApType,
                                ControlAcCode = controlAc.ControlAcCode,
                                FromDate = Convert.ToDateTime("01-Jan-01"),
                                ToDate = DateTime.Now.Date,
                                ReportType = "A", // Summary report
                                ShowOnlyDue = "N",
                                ScanDocType = "R",
                                ShowOnlyMatched = "N",
                                ReportFilter = "0"

                            };
                            var yearLabel = await _IUtilityMethodsRepository.GetActiveYearlabel();
                            await _IArApReportRepository.ArApAnalysis(_ArApReport, dbname, yearLabel, "C");
                        }
                    }
                    // Dashboard Puchase By Product
                    PurchaseAnalysisReport _PurchaseAnalysisReport = new()
                    {
                        FromDate = fyStartDate,
                        ToDate = DateTime.Now.AddDays(-1).Date,
                        UnitCode = "00",
                        ReportFilter = "0"
                    };

                    await _IPurchaseAnalysisReportRepository.PurchaseByProductReport(_PurchaseAnalysisReport, dbname, "C");

                    // Dashboard Order Register
                    SalesOrderAnalysisReport _SalesOrderAnalysisReport = new()
                    {
                        FromDate = fyStartDate,
                        ToDate = DateTime.Now.AddDays(-1).Date,
                        UnitCode = "00",
                        ReportFilter = "0"
                    };

                    await _ISalesOrderAnalysisReportRepository.OrderRegisterReport(_SalesOrderAnalysisReport, dbname, "C");

                    // Indent Register Report
                    IndentAnalysisReport _IndentAnalysisReport = new()
                    {
                        FromDate = fyStartDate,
                        ToDate = DateTime.Now.AddDays(-1).Date,
                        UnitCode = "00",
                        ReportFilter = "0"
                    };

                    await _IIndentAnalysisReportRepository.IndentRegisterReport(_IndentAnalysisReport, dbname, "1", "C");

                    // Dashboard Iirs Report
                    var yearLabelList = await _IAIInsightsRepository.GetYearLabelList();
                    _IConsumptionOfMaterialReportRepository.TruncateDashboardIirs(dbname);
                    _IConsumptionOfMaterialReportRepository.TruncateDashboardInputWise(dbname);
                    foreach (var yearLabel in yearLabelList)
                    {
                        var fyDates = await _IUtilityMethodsRepository.GetFYDates(yearLabel);
                        var fystart = fyDates.fystart;
                        var fyend = fyDates.fyend;

                        var toDate = fyDates.fyend?.Date > DateTime.Now.Date ? 
                        DateTime.Now.AddDays(-1).Date : fyDates.fyend?.Date;

                        ConsumptionOfMaterialReport _ConsumptionOfMaterialReport = new()
                        {
                            FromDate = fystart,
                            ToDate = toDate 
                        }; 

                        await _IConsumptionOfMaterialReportRepository.IirsConsumptionReport(_ConsumptionOfMaterialReport, dbname, yearLabel, "C");

                        _ConsumptionOfMaterialReport = new()
                        {
                            FromDate = fystart,
                            ToDate = toDate,
                            ReportType = "A",
                            ScanBom = true
                        }; 

                        await _IConsumptionOfMaterialReportRepository.ConsumptionOfMaterialReport(_ConsumptionOfMaterialReport, dbname, yearLabel, "C");
                    }

                    // Dashboard Bom Report
                    BomReport _BomReport = new()
                    {
                        ReportFilter = "0",
                        ReportType = "A",
                        RateType = "WAR",
                        PrintType = "B"    
                    }; 

                    await _IBomReportRepository.BomReport(_BomReport, dbname, "C");


                    // Dashboard Cash Bank Book Report
                    _ICashBankReportRepository.TruncateDashboardCashBankBook(dbname);
                    foreach (var yearLabel in yearLabelList)
                    {
                        var fyDates = await _IUtilityMethodsRepository.GetFYDates(yearLabel);
                        var fystart = fyDates.fystart;
                        var fyend = fyDates.fyend;

                        var toDate = fyDates.fyend?.Date > DateTime.Now.Date ? 
                        DateTime.Now.AddDays(-1).Date : fyDates.fyend?.Date;

                        CashBankReport _CashBankReport = new()
                        {
                            FyStart = fystart,
                            FromDate = fystart,
                            ToDate = toDate,
                            ReportBankCode = "0",
                            DivisionCode = "0"
                        };

                        await _ICashBankReportRepository.CashBankReport(_CashBankReport, dbname, yearLabel, "C");
                    }
                }
                catch (Exception ex)
                {
                    var emailController = new EmailController(_IEmailRepository, _IUtilityMethodsRepository);
                    var _ReceiverEmail = new ReceiverEmail
                    {
                        email1 = "support@erpcrystal.in", // System Admin Email
                        emailsubject = $"AI Insights Scheduler Error for {dbname}",
                        emailmessage = $"An error occurred in the AI Insights Scheduler for database '{dbname}'.<br><br><b>Exception:</b><br>{ex.Message}<br><br><b>Stack Trace:</b><br>{ex.StackTrace}"
                    };
                    await emailController.SendEmail(dbname, _ReceiverEmail, "S");
                }
            }
        }
    }
}

public async Task<List<string>> GetYearLabelList()
{
    var query = @"WITH A (yearLabel) AS
    (
        SELECT TOP 3 yearlabel FROM Fydates
        WHERE active IN ('Y','N')
        ORDER BY ID DESC
    )
    SELECT yearlabel FROM A
    ORDER BY yearlabel ASC";

    using var connection = _DapperContext.SetCommonConnection();
    var data = await connection.QueryAsync<string>(query);
    return data.ToList();
}

//need to execute
public void TruncateDashboardIirs(string dbname)
{
    var query = @"TRUNCATE TABLE dash.DashboardIirs";
    using var connection = _DapperContext.SetClientConnection(dbname);
    connection.Execute(query);
}

public void TruncateDashboardInputWise(string dbname)
{
    var query = @"TRUNCATE TABLE dash.DashboardInputWise";
    using var connection = _DapperContext.SetClientConnection(dbname);
    connection.Execute(query);
}

//-------------------- IIRS_Consumption_LeadTime --------------------//
// Add itemgroupcode, ItemSegmentCode, InputStageCode also in select query
var dashboardInsert = @"INSERT INTO dash.DashboardIirs(ItemId, ItemShortName, ItemName,
ItemGroupName, InputStageName, qty, rate, amount, LeadTime, ItemSegmentName,
itemgroupcode, ItemSegmentCode, InputStageCode,yearlabel)
SELECT ItemId, ItemShortName, ItemName,ItemGroupName, 
StageName AS InputStageName, qty, rate, amount, LeadTime, ItemSegmentName,
itemgroupcode, ItemSegmentCode, InputStageCode,@yearLabel1
FROM #charttemp
WHERE ind = 'X'";

// Where Clause to be Added in select query if ind is C
// WHERE H.Ind = 'X'

if(ind == "C")
{
	var fullQuery = $@"{query1.Replace("-- INTO_temp", "INTO #charttemp")}
    {dashboardInsert}";

    connection.Execute(fullQuery, new
    {
        FromDate = _ConsumptionOfMaterialReport.FromDate?.Date,
        ToDate = _ConsumptionOfMaterialReport.ToDate?.Date,
        yearLabel = yearLabel.Replace("-", ""),
        yearLabel1 = yearLabel
    });
}

//-------------------- Input_Wise_Consumption_Summary --------------------//
// Add input stagecode also in select query
var dashboardInsert = @"INSERT INTO dash.DashboardInputWise(ItemId,ItemShortName,
ItemName,InputStageName,iirsconsumptionqty,consumptionqty,
rate,iirsamount,bomamount,itemgroupname, itemsegmentname,
itemgroupcode, ItemSegmentCode, InputStageCode, yearLabel)
SELECT Z.ItemId,Z.ItemShortName,Z.ItemName,
Z.InputStageName,Z.iirsconsumptionqty,Z.consumptionqty,
Z.rate,Z.iirsamount,Z.bomamount, IG.groupname AS ItemGroupName,
S.description AS ItemSegmentName,I0.itemgroupid AS itemgroupcode, 
S.segmentcode AS ItemSegmentCode, Z.InputStageCode,@yearLabel1
FROM #charttemp Z
LEFT JOIN Inventorymst I0 ON Z.itemid = I0.Itemid
LEFT JOIN itemgroupmst IG ON I0.itemgroupid = IG.groupcode
LEFT JOIN Segmentmst S ON I0.segmentcode = S.segmentcode
WHERE Z.ind = 'X'";

if(ind == "C")
{
	var fullQuery = $@"{query4.Replace("-- INTO_temp", "INTO #charttemp")}
    {dashboardInsert}";

    fullQuery = $@"{fullQuery.Replace("-- WHERE ind = 'X'", "WHERE ind = 'X'")}";

    connection.Execute(fullQuery,
    new
    {
        FromDate = _ConsumptionOfMaterialReport.FromDate?.Date,
        ToDate = _ConsumptionOfMaterialReport.ToDate?.Date,
        yearlabel = yearLabel.Replace("-", ""),
        useiirs = "M",
        itemstate = "G",
        stage = "00",
        type = "S",
        yearlabel1 = yearLabel
    });
}

---need to execute this if ind is C
TRUNCATE TABLE dash.DashboardBom

----insert query
-- Add productgroupcode, itemgroupcode, uomcode, inputstagecode also in select query
INSERT INTO dash.DashboardBom(BomNo,BomDate,productid,ProductShortName,
ProductGroupName,ProcessName,ItemGroupName,itemid,ItemShortName,
UomName,InputStageName,qty,rate,value,ismanualratename,inputtypename,
level,refbomno,identification,RackInfo,IndentDate,LinkedCustomerCode,
productgroupcode, itemgroupcode, uomcode, inputstagecode)
SELECT BomNo,BomDate,productid,ProductShortName,
ProductGroupName,ProcessName,ItemGroupName,itemid,ItemShortName,
UomName,InputStageName,qty,rate,value,ismanualratename,inputtypename,
level,refbomno,identification,RackInfo,IndentDate,LinkedCustomerCode,
productgroupcode, itemgroupcode, uomcode, inputstagecode
FROM #charttemp
WHERE ind = 'X'

var whereclause1 = ind == "C" ? "" : "AND mainac = @bankcode AND divisionid = @DivisionCode";
var whereclause2 = ind == "C" ? "" : "AND V.bankcode = @bankcode AND V.divisionid = @DivisionCode";

var opgBalInsert = $@"WITH A (amount, bankcode) AS  
(
    SELECT IIF(drcr = 'C', amount *(-1), amount) AS amount, mainac AS bankcode FROM opgbalances 
    WHERE  yearlabel = @yearlabel {whereclause1}
    UNION ALL
    SELECT SUM(IIF(drcr = 'D', V1.amount *(-1), V1.amount)) AS amount, V.bankcode FROM voucher V
    LEFT JOIN Voucher1 V1 ON V.yearvoucherno = V1.yearvoucherno
    WHERE CONVERT(DATE,V.dated) BETWEEN @FyStart AND DATEADD(DAY, -1, @FromDate)
    {whereclause2}
    GROUP BY V.bankcode
),
B (amount, bankcode) AS
( SELECT SUM(amount) AS amount, bankcode FROM A
  GROUP BY BankCode )
INSERT INTO #temp (id,voucherno,voucherdate,bankcode,subno,cheqno,cheqdate,drawnon,vouchertype,receipt,
payment,amount,balance,refdoc,refno,refdate,refacname,valuedate,yearvoucherno,notes)
SELECT 0, '' AS voucherno, @FromDate AS voucherdate,bankcode,'' AS subno, '' AS cheqno, 
'01-Jan-01' AS cheqdate, '' AS drawnon, 'O' AS vouchertype, 0 AS receipt, 0 AS payment, amount,
amount AS balance, '' AS refdoc, '' AS refno, '01-Jan-01' AS refdate, 'Opening Balance' AS refacname, 
'01-Jan-01' AS valuedate,'' AS yearvoucherno, '' AS notes FROM B";

// For inserting into the temp table.
var insertToTemp = $@"WITH A (id ,voucherno,yearvoucherno,voucherdate,bankcode,subno,cheqno,cheqdate,drawnon,Vouchertype,
receipt,payment,amount,refdoc,refno,refdate,notes,valuedate,mainac,subac,balance,drcr) AS
(
    SELECT V1.id, V.voucherno,V.yearvoucherno,V.dated AS voucherdate,bankcode,subno,
    IIF(COALESCE(utrno,'') != '', utrno, cheqno) AS cheqno,cheqdate AS cheqdate,
    drawnon,vouchertype,IIF(vouchertype = 'R', V.amount, 0) AS receipt,
    IIF(vouchertype = 'P', V.amount, 0) AS payment, IIF(V1.drcr = 'C', V1.amount, V1.amount*(-1)) AS amount,
    V1.refdoc,V1.refno,V1.refdate,V1.notes,
    COALESCE(V.ValueDate,'01-Jan-01') AS valuedate,V1.mainac,V1.subac, 0 AS balance,drcr FROM voucher V
    LEFT JOIN Voucher1 V1 ON V.yearVoucherno = V1.yearVoucherno
    WHERE CONVERT(DATE,V.dated) BETWEEN @FromDate AND @ToDate
    {whereclause2}
),
B (Id,Voucherno,yearvoucherno,voucherdate,bankcode,subno,Cheqno,cheqdate,drawnon,vouchertype,receipt,payment,amount,
balance,refdoc,refno,refdate,notes,refacname,valuedate,drcr) AS
(
    SELECT A.Id,Voucherno,A.yearvoucherno,voucherdate,bankcode,subno,cheqno,cheqdate,drawnon,vouchertype,receipt,payment,amount,
    balance,refdoc,refno,refdate,A.notes, IIF(A1.achead = A2.acname, A1.achead,
    CONCAT_WS(',',A1.achead,A2.acname)) AS refacname,valuedate,drcr FROM A
    LEFT JOIN accounts A1 ON A.mainac = A1.mainac
    LEFT JOIN accounts1 A2 ON A.mainac + A.subac = A2.mainac + A2.subac
    WHERE A.refdoc ='GL'
    UNION ALL
    SELECT A.Id,voucherno,A.yearvoucherno,voucherdate,bankcode,subno,cheqno,cheqdate,drawnon,vouchertype,receipt,payment,amount,
    balance,refdoc,A.refno,refdate,A.notes,P.partyname AS refacname,valuedate,drcr FROM A
    LEFT JOIN Partymst P ON A.mainac + A.subac = P.controlac + P.partyid 
    WHERE A.refdoc !='GL'
)
INSERT INTO #temp 
(id ,voucherno,yearvoucherno,voucherdate,bankcode,subno,cheqno,cheqdate,drawnon,vouchertype,
receipt,payment,amount,refdoc,refno,refdate,notes,valuedate,refacname,balance,drcr)
SELECT id,voucherno,yearvoucherno,voucherdate,bankcode,subno,cheqno,cheqdate,drawnon,vouchertype,
receipt,payment,amount,Refdoc,refno,Refdate,Notes,valuedate,refacname,balance,drcr FROM B ";

---need to execute
TRUNCATE TABLE dash.DashboardCashBankBook

----insert query
var dashboardInsert = @"INSERT INTO dash.DashboardCashBankBook(voucherno,voucherdate,bankcode,
vouchersubno, cheqno,cheqdate,drawnon, ind,receipt,payment,
amount,balance,refdoc,refno, refdate,refacname,valuedate,bankname)
SELECT voucherno,voucherdate,C.bankcode,
vouchersubno, cheqno,cheqdate,drawnon, ind,receipt,payment,
amount,balance,refdoc,refno, refdate,refacname,valuedate,A.achead AS bankname
FROM #charttemp C
LEFT JOIN accounts A ON C.bankcode = A.mainac";