AI Insights - System Prompt Management

CREATE TABLE AISystemPrompts (
    id INT PRIMARY KEY IDENTITY(1,1),
    ContextTable NVARCHAR(20),  -- 'Global' or table name (DashboardSales, DashboardPurchase, etc.)
    PromptCategory NVARCHAR(20), -- Sales, Purchase, GRN, Outstanding, etc.
    PromptText NVARCHAR(MAX),
    sysrolename NVARCHAR(50)
);

CREATE TABLE AISuggestedPrompts (
    id INT PRIMARY KEY IDENTITY(1,1),
    PromptCategory NVARCHAR(20),
    SuggestedText NVARCHAR(MAX)
);

INSERT INTO AISystemPrompts (ContextTable, PromptCategory, PromptText) VALUES
('Global', 'General', '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., donot 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)'),

-- Sales-specific prompts
INSERT INTO AISystemPrompts (ContextTable, PromptCategory, PromptText, sysrolename) VALUES
('DashboardSales', 'Sales', 'Table: DashboardSales - Use for all sales-related queries...','Sales' );

-- Purchase-specific prompts
INSERT INTO AISystemPrompts (ContextTable, PromptCategory, PromptText, sysrolename) VALUES
('DashboardPurchase', 'Purchase', 'Table: DashboardPurchase - Use for all purchase-related queries...', 'Purchase');

INSERT INTO AISuggestedPrompts (PromptCategory, SuggestedText) VALUES
('Sales', 'Show me top 10 customers by sales amount'),
('Sales', 'What were the sales trends last month?'),
('Purchase', 'Show me top 10 suppliers by purchase amount'),
('Purchase', 'What are the pending purchase orders?');

public class AIInsights
{
    public string PromptCategory { get; set; } = string.Empty;
    public string ContextTable { get; set; } = string.Empty;
    public string RoleName { get; set; } = string.Empty;
}

<!-- Data Type Selection -->
<div class="d-flex align-items-center gap-2 mb-2">
    <MudSelect @bind-Value="_AIInsights.PromptCategory"
               Label="Select Data Type"
               @onchange="PromptCategoryOnChange"
               Style="min-width: 200px;">
        @foreach (var category in promptCategoryCombo)
        {
            <MudSelectItem Value="@category">@category</MudSelectItem>
        }
    </MudSelect>
</div>

<!-- Suggested Prompts -->
@if (suggestedPrompts.Any())
{
    <div class="mb-2">
        <MudAutocomplete T="string"
                         Label="Select or type your prompt"
                         @bind-Value="userPrompt"
                         SearchFunc="SearchSuggestedPrompts"
                         ToStringFunc="(x) => x"
                         Placeholder="Type your question or select from suggestions"
                         Clearable="true"
                         Adornment="Adornment.Start"
                         AdornmentIcon="@Icons.Material.Filled.Search" />
    </div>
}

AIInsights _AIInsights = new();
private List<string> promptCategoryCombo = new();
private List<string> suggestedPrompts = new();

protected override async Task OnInitializedAsync()
{
    _PostLogin = await _LocalSession.GetItemAsync<PostLogin>("userinfo");

    var usersessionid = _IPostLoginService.GetUserSessionId(_PostLogin.userdb);
    usersessionid = usersessionid.Replace("\"","");

    if(_PostLogin.BrowserSessionId != usersessionid)
    {
        NavManager.NavigateTo("/invaliduser/5");
    }
    else
    {
        // Load available prompt categories
        promptCategoryCombo = await _IAIInsightsService.PromptCategoryCombo(_PostLogin.dbname);
    }
}

private async Task PromptCategoryOnChange()
{
    if(!string.IsNullOrEmpty(_AIInsights.PromptCategory))
    {
        var roleName = await _IAIInsightsService.GetPromptCategoryRole(_AIInsights.PromptCategory, _PostLogin.dbname);
        var rolecnt = _IPostLoginService.GetUserRolesCnt(_PostLogin.userdb, roleName, _PostLogin.dbname);

        if(rolecnt == 0)
        {
            NavManager.NavigateTo("/invaliduser/5");
            return;
        }
        else
        {
            suggestedPrompts = await _IAIInsightsService.GetSuggestedPrompts(_AIInsights.PromptCategory, _PostLogin.dbname);
        }
    }
    else
    {
        suggestedPrompts.Clear();
    }
}

private async Task<IEnumerable<string>> SearchSuggestedPrompts(string value)
{
    if (string.IsNullOrWhiteSpace(value))
        return suggestedPrompts;

    return suggestedPrompts.Where(x => x.Contains(value, StringComparison.InvariantCultureIgnoreCase));
}

private async Task GetUserPromptResult()
{
    // ... existing validation ...

    var sqlQuery = await _IAIInsightsService.GenerateSqlQueryFromPrompt(userPrompt,string promptCategory _PostLogin.dbname);

    // ... rest remains the same ...
}

Task<string> GenerateSqlQueryFromPrompt(string userPrompt, string promptCategory, string dbname);
Task<List<string>> PromptCategoryCombo(string dbname);
Task<string> GetPromptCategoryRole(string promptCategory, string dbname);
Task<List<string>> GetSuggestedPrompts(string category, string dbname);

public async Task<string> GenerateSqlQueryFromPrompt(string userPrompt,string promptCategory string dbname)
{
    // 1. Get selective system prompts (Global + table-specific)
    var selectedTable = await _IAIInsightsService.GetContextTableName(string promptCategory, _PostLogin.dbname);
    var tablePrompts = await GetSystemPromptsByTable(selectedTable, dbname);

    // 2. Get dynamic schema for selected table
    var schema = await GetTableSchema(selectedTable, dbname);

    // 3. Build system prompt
    var systemPrompt = $"{tablePrompts}{schema}";

    // 4. Call OpenAI (rest of the method remains similar)
    var requestBody = new
    {
        model = _model,
        messages = new object[]
        {
            new { role = "system", content = systemPrompt },
            new { role = "user", content = userPrompt }
        }
    };

    // ... rest of OpenAI call logic remains the same
}
private async Task<List<AISystemPrompt>> GetSystemPromptsByTable(string tableName, string dbname)
{
    var response = await _HttpClient.GetFromJsonAsync<List<AISystemPrompt>>($"/api/aiinsights/getsystempromptsbytable/{tableName}/{dbname}");
    return response ?? new List<AISystemPrompt>();
}

public async Task<string> GetPromptCategoryRole(string promptCategory, string dbname)
{
    var response = await _HttpClient.GetFromJsonAsync<string>($"/api/aiinsights/getpromptcategoryrole/{promptCategory}/{dbname}");
    return response ?? string.Empty;
}

public async Task<string> GetContextTableName(string promptCategory, string dbname)
{
    var response = await _HttpClient.GetFromJsonAsync<string>($"/api/aiinsights/getcontexttablename/{promptCategory}/{dbname}");
    return response ?? string.Empty;
}

public async Task<List<string>> PromptCategoryCombo(string dbname)
{
    var response = await _HttpClient.GetFromJsonAsync<List<string>>($"/api/aiinsights/promptcategorycombo/{dbname}");
    return response ?? new List<string>();
}

public async Task<List<string>> GetSuggestedPrompts(string category, string dbname)
{
    var response = await _HttpClient.GetFromJsonAsync<List<string>>($"/api/aiinsights/getsuggestedprompts/{category}/{dbname}");
    return response ?? new List<string>();
}

[HttpGet("{tableName}/{dbname}")]
public async Task<IActionResult> GetSystemPromptsByTable(string tableName, string dbname)
{
    var prompts = await _IAIInsightsRepository.GetSystemPromptsByTable(tableName, dbname);
    return Ok(prompts);
}

[HttpGet("{tableName}/{dbname}")]
public async Task<IActionResult> GetTableSchema(string tableName, string dbname)
{
    var schema = await _IAIInsightsRepository.GetTableSchema(tableName, dbname);
    return Ok(schema);
}


[HttpGet("getpromptcategoryrole/{promptCategory}/{dbname}")]
public async Task<IActionResult> GetPromptCategoryRole(string promptCategory, string dbname)
{
    var role = await _IAIInsightsRepository.GetPromptCategoryRole(promptCategory, dbname);
    return Ok(role);
}

[HttpGet("getcontexttablename/{promptCategory}/{dbname}")]
public async Task<IActionResult> GetContextTableName(string promptCategory, string dbname)
{
    var table = await _IAIInsightsRepository.GetContextTableName(promptCategory, dbname);
    return Ok(table);
}

[HttpGet("{dbname}")]
public async Task<IActionResult> PromptCategoryCombo(string dbname)
{
    var categories = await _IAIInsightsRepository.PromptCategoryCombo(dbname);
    return Ok(categories);
}


[HttpGet("{category}/{dbname}")]
public async Task<IActionResult> GetSuggestedPrompts(string category, string dbname)
{
    var prompts = await _IAIInsightsRepository.GetSuggestedPrompts(category, dbname);
    return Ok(prompts);
}

Task<List<AISystemPrompt>> GetSystemPromptsByTable(string tableName, string dbname);
Task<string> GetTableSchema(string tableName, string dbname);
Task<string> GetPromptCategoryRole(string promptCategory, string dbname);
Task<string> GetContextTableName(string promptCategory, string dbname);
Task<List<string>> PromptCategoryCombo(string dbname);
Task<AISystemPromptsInfo> AISystemPromptsInfo(string promptCategory, string dbname);
Task<List<string>> GetSuggestedPrompts(string promptCategory, string dbname);

public async Task<List<AISystemPrompt>> GetSystemPromptsByTable(string tableName, string dbname)
{
    var query = @"WITH A AS (prompttext, contexttable) AS
(
    SELECT
        PromptText,
        ContextTable    
        FROM AISystemPrompts
    WHERE ContextTable IN ('Global', @tableName)
)
SELECT
    STRING_AGG(PromptText)
        WITHIN GROUP (ORDER BY ContextTable DESC, id) AS CombinedPromptText
FROM A"; // Global first, then table-specific

    using var connection = _DapperContext.SetClientConnection(dbname);
    var result = await connection.QueryAsync<AISystemPrompt>(query, new { tableName });
    return result.ToList();
}

public async Task<string> GetPromptCategoryRole(string promptCategory, string dbname)
{
    var query = @"SELECT SysRoleName FROM AISystemPrompts WHERE PromptCategory = @promptCategory";
    using var connection = _DapperContext.SetClientConnection(dbname);
    var result = await connection.QuerySingleOrDefaultAsync<string>(query, new { promptCategory });
    return result ?? string.Empty;
}

public async Task<string> GetContextTableName(string promptCategory, string dbname)
{
    var query = @"SELECT ContextTable FROM AISystemPrompts WHERE PromptCategory = @promptCategory";
    using var connection = _DapperContext.SetClientConnection(dbname);
    var result = await connection.QuerySingleOrDefaultAsync<string>(query, new { promptCategory });
    return result ?? string.Empty;
}

public async Task<string> GetTableSchema(string tableName, string dbname)
{
    var query = @"SELECT CONCAT('Column: ', CONCAT(C.name,' (',TY.name,
    IIF(C.max_length > 0, CONCAT('(',CAST(C.max_length AS NVARCHAR),')'), ''),
    ')')) AS ColumnInfo
    FROM SYS.schemas S
    LEFT JOIN SYS.tables T ON T.schema_id = S.schema_id
    LEFT JOIN SYS.columns C ON C.object_id = T.object_id
    LEFT JOIN SYS.types TY ON TY.user_type_id = C.user_type_id
    WHERE S.name = 'dash'
    AND t.name = @tableName
    ORDER BY c.column_id;";

    using var connection = _DapperContext.SetClientConnection(dbname);
    var columns = await connection.QueryAsync<string>(query, new { tableName });
    return string.Join("\n", columns);
}

public async Task<List<string>> PromptCategoryCombo(string dbname)
{
    var query = @"SELECT DISTINCT PromptCategory
                  FROM AISystemPrompts
                  WHERE ContextTable != 'Global'
                  ORDER BY PromptCategory";

    using var connection = _DapperContext.SetClientConnection(dbname);
    var result = await connection.QueryAsync<string>(query);
    return result.ToList();
}

public async Task<AISystemPromptsInfo> AISystemPromptsInfo(string promptCategory, string dbname)
{
    var query = @"SELECT ContextTable, SysRoleName AS RoleName
                  FROM AISystemPrompts
                  WHERE PromptCategory = @promptCategory";

    using var connection = _DapperContext.SetClientConnection(dbname);
    var result = await connection.QuerySingleOrDefaultAsync<AISystemPromptsInfo>(query, new { promptCategory });
    return result ?? new AISystemPromptsInfo();
}

public async Task<List<string>> GetSuggestedPrompts(string promptCategory, string dbname)
{
    var query = @"SELECT SuggestedText
                  FROM AISuggestedPrompts
                  WHERE PromptCategory = @promptCategory";

    using var connection = _DapperContext.SetClientConnection(dbname);
    var result = await connection.QueryAsync<string>(query, new { promptCategory });
    return result.ToList();
}