| | | 1 | | using Dapper; |
| | | 2 | | using Microsoft.Data.Sqlite; |
| | | 3 | | |
| | | 4 | | namespace ShopGuard.Core.Database; |
| | | 5 | | |
| | | 6 | | /// <summary> |
| | | 7 | | /// Dapper-based access to the order validation database (SQLite locally and in CI; |
| | | 8 | | /// the SQL is kept ANSI-compatible so it also runs against SQL Server). |
| | | 9 | | /// </summary> |
| | 7 | 10 | | public sealed class OrderRepository(string connectionString) |
| | | 11 | | { |
| | | 12 | | /// <summary>Creates the Orders table when it does not exist yet.</summary> |
| | | 13 | | public void EnsureSchema() |
| | | 14 | | { |
| | 7 | 15 | | using var connection = Open(); |
| | 7 | 16 | | connection.Execute( |
| | 7 | 17 | | """ |
| | 7 | 18 | | CREATE TABLE IF NOT EXISTS Orders ( |
| | 7 | 19 | | Id INTEGER PRIMARY KEY AUTOINCREMENT, |
| | 7 | 20 | | OrderNumber TEXT NOT NULL UNIQUE, |
| | 7 | 21 | | CustomerEmail TEXT NOT NULL, |
| | 7 | 22 | | Total NUMERIC NOT NULL, |
| | 7 | 23 | | Status TEXT NOT NULL, |
| | 7 | 24 | | CreatedAtUtc TEXT NOT NULL |
| | 7 | 25 | | ) |
| | 7 | 26 | | """); |
| | 14 | 27 | | } |
| | | 28 | | |
| | | 29 | | /// <summary>Inserts a new order with status <see cref="OrderStatus.Pending"/>.</summary> |
| | | 30 | | public void InsertOrder(string orderNumber, string customerEmail, decimal total) |
| | | 31 | | { |
| | 9 | 32 | | using var connection = Open(); |
| | 9 | 33 | | connection.Execute( |
| | 9 | 34 | | """ |
| | 9 | 35 | | INSERT INTO Orders (OrderNumber, CustomerEmail, Total, Status, CreatedAtUtc) |
| | 9 | 36 | | VALUES (@orderNumber, @customerEmail, @total, @status, @createdAtUtc) |
| | 9 | 37 | | """, |
| | 9 | 38 | | new |
| | 9 | 39 | | { |
| | 9 | 40 | | orderNumber, |
| | 9 | 41 | | customerEmail, |
| | 9 | 42 | | total, |
| | 9 | 43 | | status = OrderStatus.Pending, |
| | 9 | 44 | | createdAtUtc = DateTime.UtcNow.ToString("O"), |
| | 9 | 45 | | }); |
| | 16 | 46 | | } |
| | | 47 | | |
| | | 48 | | /// <summary>Updates the status of an existing order.</summary> |
| | | 49 | | public void UpdateStatus(string orderNumber, string newStatus) |
| | | 50 | | { |
| | 2 | 51 | | using var connection = Open(); |
| | 2 | 52 | | var affected = connection.Execute( |
| | 2 | 53 | | "UPDATE Orders SET Status = @newStatus WHERE OrderNumber = @orderNumber", |
| | 2 | 54 | | new { newStatus, orderNumber }); |
| | | 55 | | |
| | 2 | 56 | | if (affected == 0) |
| | | 57 | | { |
| | 1 | 58 | | throw new InvalidOperationException($"Order '{orderNumber}' not found."); |
| | | 59 | | } |
| | 2 | 60 | | } |
| | | 61 | | |
| | | 62 | | /// <summary>Reads a single order by its business key, or null when absent.</summary> |
| | | 63 | | public Order? GetByOrderNumber(string orderNumber) |
| | | 64 | | { |
| | 3 | 65 | | using var connection = Open(); |
| | 3 | 66 | | return connection.QuerySingleOrDefault<Order>( |
| | 3 | 67 | | "SELECT Id, OrderNumber, CustomerEmail, Total, Status, CreatedAtUtc FROM Orders WHERE OrderNumber = @orderNu |
| | 3 | 68 | | new { orderNumber }); |
| | 3 | 69 | | } |
| | | 70 | | |
| | | 71 | | /// <summary>Counts orders for a customer; used for test data isolation checks.</summary> |
| | | 72 | | public int CountByCustomer(string customerEmail) |
| | | 73 | | { |
| | 2 | 74 | | using var connection = Open(); |
| | 2 | 75 | | return connection.ExecuteScalar<int>( |
| | 2 | 76 | | "SELECT COUNT(*) FROM Orders WHERE CustomerEmail = @customerEmail", |
| | 2 | 77 | | new { customerEmail }); |
| | 2 | 78 | | } |
| | | 79 | | |
| | | 80 | | /// <summary>Removes all orders of a customer; used to clean up scenario data.</summary> |
| | | 81 | | public void DeleteByCustomer(string customerEmail) |
| | | 82 | | { |
| | 1 | 83 | | using var connection = Open(); |
| | 1 | 84 | | connection.Execute( |
| | 1 | 85 | | "DELETE FROM Orders WHERE CustomerEmail = @customerEmail", |
| | 1 | 86 | | new { customerEmail }); |
| | 2 | 87 | | } |
| | | 88 | | |
| | | 89 | | private SqliteConnection Open() |
| | | 90 | | { |
| | 24 | 91 | | var connection = new SqliteConnection(connectionString); |
| | 24 | 92 | | connection.Open(); |
| | 24 | 93 | | return connection; |
| | | 94 | | } |
| | | 95 | | } |