public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public int CategoryId { get; set; }
    public bool IsActive { get; set; }
}

// Простий raw SQL запит
var products = await context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE IsActive = 1")
    .ToListAsync();
// Маппить результат на Product entity

// Важливо: запит МАЄ повертати всі стовпці entity (або хоча б ті, що не nullable)
// Інакше — MapColumn error або null-поля

// Позиційні placeholders: {0}, {1}
var minPrice = 1000m;
var categoryId = 5;

var products = await context.Products
    .FromSqlRaw(
        "SELECT * FROM Products WHERE Price >= {0} AND CategoryId = {1}",
        minPrice,   // {0}
        categoryId  // {1}
    )
    .ToListAsync();
// SQL (параметризований): WHERE Price >= @p0 AND CategoryId = @p1
// EF Core автоматично перетворює на параметри — безпечно від SQL Injection!

// Через SqlParameter для іменованих параметрів
using Microsoft.Data.SqlClient;

var products = await context.Products
    .FromSqlRaw(
        "SELECT * FROM Products WHERE Price >= @minPrice AND CategoryId = @categoryId",
        new SqlParameter("@minPrice",    minPrice),
        new SqlParameter("@categoryId",  categoryId)
    )
    .ToListAsync();

// Інтерполяція: виглядає як звичайний рядок, але повністю параметризований
decimal minPrice = 1000m;
int categoryId = 5;
string city = "Kyiv";

var products = await context.Products
    .FromSqlInterpolated(
        $"SELECT * FROM Products WHERE Price >= {minPrice} AND CategoryId = {categoryId}")
    .ToListAsync();
// SQL: SELECT * FROM Products WHERE Price >= @p0 AND CategoryId = @p1
// minPrice і categoryId → параметри, не вбудовані у рядок

// EF Core 8+: єдиний FromSql
var products = await context.Products
    .FromSql($"SELECT * FROM Products WHERE Price >= {minPrice}")
    .ToListAsync();
// Автоматично параметризує — безпечно

// Base SQL + LINQ фільтрація зверху
var expensiveProducts = await context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE IsActive = 1")
    .Where(p => p.Price > 5000)       // додає AND Price > 5000 до SQL
    .OrderBy(p => p.Price)            // додає ORDER BY Price
    .Take(10)                         // додає TOP 10
    .ToListAsync();
// Генерований SQL:
// SELECT TOP 10 ... FROM (
//     SELECT * FROM Products WHERE IsActive = 1
// ) AS t
// WHERE t.Price > 5000
// ORDER BY t.Price

// FromSql + Include (EF Core 7+)
var ordersWithCustomer = await context.Orders
    .FromSqlRaw("SELECT * FROM Orders WHERE Status = 'Pending'")
    .Include(o => o.Customer)  // ← JOIN Customers
    .ToListAsync();

// SqlQuery: довільний результат, не лише entity types
var stats = await context.Database
    .SqlQueryRaw<ProductStats>(
        "SELECT CategoryId, COUNT(*) AS Count, AVG(Price) AS AvgPrice FROM Products GROUP BY CategoryId")
    .OrderByDescending(s => s.Count)
    .ToListAsync();

// Клас-результат (не entity — немає DbSet)
public class ProductStats
{
    public int CategoryId { get; set; }
    public int Count { get; set; }
    public decimal AvgPrice { get; set; }
}

// UPDATE без завантаження в пам'ять
int affected = await context.Database.ExecuteSqlRawAsync(
    "UPDATE Products SET IsActive = 0 WHERE CategoryId = {0} AND Stock = 0",
    categoryId
);
Console.WriteLine($"Деактивовано {affected} продуктів");

// ExecuteSqlInterpolated
await context.Database.ExecuteSqlInterpolatedAsync(
    $"DELETE FROM AuditLogs WHERE CreatedAt < {DateTime.UtcNow.AddYears(-2)}"
);

// ExecuteSql (EF Core 8+)
await context.Database.ExecuteSqlAsync(
    $"UPDATE Products SET Price = Price * {1.1m} WHERE CategoryId = {categoryId}"
);

// C# клас для View — без PK, без auto-generated Id
public class OrderSummaryView
{
    public int OrderId { get; set; }
    public string OrderNumber { get; set; } = string.Empty;
    public string CustomerName { get; set; } = string.Empty;
    public string CustomerEmail { get; set; } = string.Empty;
    public int ItemCount { get; set; }
    public decimal TotalAmount { get; set; }
    public decimal TaxAmount { get; set; }
    public string Status { get; set; } = string.Empty;
    public DateTime PlacedAt { get; set; }
}

public class OrderSummaryViewConfiguration : IEntityTypeConfiguration<OrderSummaryView>
{
    public void Configure(EntityTypeBuilder<OrderSummaryView> builder)
    {
        builder.HasNoKey();          // ← Keyless: немає PK
        builder.ToView("vw_OrderSummary"); // ← маппить на View у БД
    }
}

// У DbContext
public DbSet<OrderSummaryView> OrderSummaries => Set<OrderSummaryView>();

// Використання: як звичайний DbSet, але read-only (без SaveChanges)
var summaries = await context.OrderSummaries
    .Where(v => v.Status == "Delivered")
    .OrderByDescending(v => v.PlacedAt)
    .Take(100)
    .ToListAsync();
// SQL: SELECT TOP 100 * FROM vw_OrderSummary WHERE Status='Delivered' ORDER BY PlacedAt DESC

public partial class CreateOrderSummaryView : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"
            CREATE VIEW [vw_OrderSummary] AS
            SELECT
                o.[Id]          AS [OrderId],
                o.[OrderNumber],
                c.[FullName]    AS [CustomerName],
                c.[Email]       AS [CustomerEmail],
                COUNT(li.[Id])  AS [ItemCount],
                o.[TotalAmount],
                o.[TaxAmount],
                o.[Status],
                o.[PlacedAt]
            FROM [Orders] o
            INNER JOIN [Customers] c ON c.[Id] = o.[CustomerId]
            LEFT JOIN [OrderLineItems] li ON li.[OrderId] = o.[Id]
            WHERE o.[IsDeleted] = 0
            GROUP BY
                o.[Id], o.[OrderNumber], c.[FullName], c.[Email],
                o.[TotalAmount], o.[TaxAmount], o.[Status], o.[PlacedAt]
        ");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("DROP VIEW [vw_OrderSummary]");
    }
}

migrationBuilder.Sql(@"
    CREATE MATERIALIZED VIEW ""mv_DailySalesStats"" AS
    SELECT
        DATE(""PlacedAt"")    AS ""Date"",
        COUNT(*)              AS ""OrderCount"",
        SUM(""TotalAmount"")  AS ""Revenue"",
        AVG(""TotalAmount"")  AS ""AvgOrderValue""
    FROM ""Orders""
    WHERE ""IsDeleted"" = false
    GROUP BY DATE(""PlacedAt"");

    CREATE INDEX ON ""mv_DailySalesStats"" (""Date"");
");
// Refresh:
// REFRESH MATERIALIZED VIEW ""mv_DailySalesStats"";

// Рекурсивний CTE для ієрархії категорій
var categoryHierarchy = await context.Database
    .SqlQueryRaw<CategoryHierarchyResult>(@"
        WITH CategoryCTE AS (
            -- Базовий випадок: кореневі категорії
            SELECT Id, Name, ParentCategoryId, 0 AS [Level], CAST(Name AS NVARCHAR(MAX)) AS Path
            FROM Categories
            WHERE ParentCategoryId IS NULL

            UNION ALL

            -- Рекурсивний крок: дочірні категорії
            SELECT c.Id, c.Name, c.ParentCategoryId, cte.[Level] + 1,
                   cte.Path + N' > ' + c.Name
            FROM Categories c
            INNER JOIN CategoryCTE cte ON cte.Id = c.ParentCategoryId
        )
        SELECT Id, Name, ParentCategoryId, [Level], Path
        FROM CategoryCTE
        ORDER BY Path
    ")
    .ToListAsync();

public class CategoryHierarchyResult
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public int? ParentCategoryId { get; set; }
    public int Level { get; set; }
    public string Path { get; set; } = string.Empty;
}

// ROW_NUMBER для ranking в кожній категорії
var rankedProducts = await context.Database
    .SqlQueryRaw<ProductRankResult>(@"
        SELECT
            p.Id,
            p.Name,
            p.CategoryId,
            p.Price,
            ROW_NUMBER() OVER (PARTITION BY p.CategoryId ORDER BY p.Price DESC) AS PriceRank,
            RANK()       OVER (PARTITION BY p.CategoryId ORDER BY p.Price DESC) AS PriceRankWithTies,
            LAG(p.Price)  OVER (PARTITION BY p.CategoryId ORDER BY p.Price DESC) AS PreviousPrice,
            LEAD(p.Price) OVER (PARTITION BY p.CategoryId ORDER BY p.Price DESC) AS NextPrice
        FROM Products p
        WHERE p.IsActive = 1
    ")
    .Where(r => r.PriceRank <= 3) // TOP 3 у кожній категорії
    .ToListAsync();

public class ProductRankResult
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public int CategoryId { get; set; }
    public decimal Price { get; set; }
    public int PriceRank { get; set; }
    public int PriceRankWithTies { get; set; }
    public decimal? PreviousPrice { get; set; }
    public decimal? NextPrice { get; set; }
}

var runningTotal = await context.Database
    .SqlQueryRaw<DailySalesWithRunning>(@"
        SELECT
            CAST(PlacedAt AS DATE)       AS OrderDate,
            COUNT(*)                     AS DailyCount,
            SUM(TotalAmount)             AS DailyRevenue,
            SUM(SUM(TotalAmount)) OVER (
                ORDER BY CAST(PlacedAt AS DATE)
                ROWS UNBOUNDED PRECEDING
            )                            AS RunningRevenue
        FROM Orders
        WHERE IsDeleted = 0
          AND PlacedAt >= DATEADD(month, -3, GETUTCDATE())
        GROUP BY CAST(PlacedAt AS DATE)
        ORDER BY OrderDate
    ")
    .ToListAsync();