Spaces:
Running
Running
File size: 7,920 Bytes
13240d3 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 | namespace BakeryErp.Infrastructure.Database;
public static class SqliteSchema
{
public const string CreateTables = """
PRAGMA foreign_keys = ON;
CREATE TABLE IF NOT EXISTS Suppliers (
Id TEXT PRIMARY KEY,
Code TEXT NOT NULL UNIQUE,
Name TEXT NOT NULL,
TaxId TEXT NULL,
ContactName TEXT NULL,
Phone TEXT NULL,
Email TEXT NULL,
Address TEXT NULL,
PaymentTermDays INTEGER NOT NULL DEFAULT 30,
CreatedAt TEXT NOT NULL,
UpdatedAt TEXT NULL,
IsActive INTEGER NOT NULL DEFAULT 1
);
CREATE TABLE IF NOT EXISTS Stores (
Id TEXT PRIMARY KEY,
Code TEXT NOT NULL UNIQUE,
Name TEXT NOT NULL,
StoreType INTEGER NOT NULL,
Phone TEXT NULL,
Address TEXT NULL,
CreatedAt TEXT NOT NULL,
UpdatedAt TEXT NULL,
IsActive INTEGER NOT NULL DEFAULT 1
);
CREATE TABLE IF NOT EXISTS Warehouses (
Id TEXT PRIMARY KEY,
Code TEXT NOT NULL UNIQUE,
Name TEXT NOT NULL,
StoreId TEXT NULL,
WarehouseType INTEGER NOT NULL,
CreatedAt TEXT NOT NULL,
UpdatedAt TEXT NULL,
IsActive INTEGER NOT NULL DEFAULT 1,
FOREIGN KEY (StoreId) REFERENCES Stores(Id)
);
CREATE TABLE IF NOT EXISTS Items (
Id TEXT PRIMARY KEY,
Sku TEXT NOT NULL UNIQUE,
Name TEXT NOT NULL,
ItemType INTEGER NOT NULL,
CategoryName TEXT NOT NULL,
BaseUnit TEXT NOT NULL,
SafetyStockQuantity NUMERIC NOT NULL DEFAULT 0,
ShelfLifeDays INTEGER NULL,
CreatedAt TEXT NOT NULL,
UpdatedAt TEXT NULL,
IsActive INTEGER NOT NULL DEFAULT 1
);
CREATE TABLE IF NOT EXISTS PurchaseOrders (
Id TEXT PRIMARY KEY,
OrderNumber TEXT NOT NULL UNIQUE,
SupplierId TEXT NOT NULL,
OrderDate TEXT NOT NULL,
Status INTEGER NOT NULL,
TotalAmount NUMERIC NOT NULL DEFAULT 0,
CreatedAt TEXT NOT NULL,
UpdatedAt TEXT NULL,
IsActive INTEGER NOT NULL DEFAULT 1,
FOREIGN KEY (SupplierId) REFERENCES Suppliers(Id)
);
CREATE TABLE IF NOT EXISTS PurchaseOrderLines (
Id TEXT PRIMARY KEY,
PurchaseOrderId TEXT NOT NULL,
ItemId TEXT NOT NULL,
Quantity NUMERIC NOT NULL,
Unit TEXT NOT NULL,
UnitPrice NUMERIC NOT NULL,
CreatedAt TEXT NOT NULL,
UpdatedAt TEXT NULL,
IsActive INTEGER NOT NULL DEFAULT 1,
FOREIGN KEY (PurchaseOrderId) REFERENCES PurchaseOrders(Id),
FOREIGN KEY (ItemId) REFERENCES Items(Id)
);
CREATE TABLE IF NOT EXISTS StockBalances (
Id TEXT PRIMARY KEY,
WarehouseId TEXT NOT NULL,
ItemId TEXT NOT NULL,
QuantityOnHand NUMERIC NOT NULL DEFAULT 0,
Unit TEXT NOT NULL,
LastTransactionAt TEXT NOT NULL,
CreatedAt TEXT NOT NULL,
UpdatedAt TEXT NULL,
IsActive INTEGER NOT NULL DEFAULT 1,
UNIQUE (WarehouseId, ItemId),
FOREIGN KEY (WarehouseId) REFERENCES Warehouses(Id),
FOREIGN KEY (ItemId) REFERENCES Items(Id)
);
CREATE TABLE IF NOT EXISTS InventoryTransactions (
Id TEXT PRIMARY KEY,
TransactionNumber TEXT NOT NULL UNIQUE,
WarehouseId TEXT NOT NULL,
ItemId TEXT NOT NULL,
TransactionType INTEGER NOT NULL,
Quantity NUMERIC NOT NULL,
Unit TEXT NOT NULL,
OccurredAt TEXT NOT NULL,
SourceDocumentNumber TEXT NULL,
Note TEXT NULL,
CreatedAt TEXT NOT NULL,
UpdatedAt TEXT NULL,
IsActive INTEGER NOT NULL DEFAULT 1,
FOREIGN KEY (WarehouseId) REFERENCES Warehouses(Id),
FOREIGN KEY (ItemId) REFERENCES Items(Id)
);
CREATE TABLE IF NOT EXISTS TransferOrders (
Id TEXT PRIMARY KEY,
TransferNumber TEXT NOT NULL UNIQUE,
FromWarehouseId TEXT NOT NULL,
ToWarehouseId TEXT NOT NULL,
TransferDate TEXT NOT NULL,
Status INTEGER NOT NULL,
CreatedAt TEXT NOT NULL,
UpdatedAt TEXT NULL,
IsActive INTEGER NOT NULL DEFAULT 1,
FOREIGN KEY (FromWarehouseId) REFERENCES Warehouses(Id),
FOREIGN KEY (ToWarehouseId) REFERENCES Warehouses(Id)
);
CREATE TABLE IF NOT EXISTS TransferOrderLines (
Id TEXT PRIMARY KEY,
TransferOrderId TEXT NOT NULL,
ItemId TEXT NOT NULL,
RequestedQuantity NUMERIC NOT NULL,
ShippedQuantity NUMERIC NOT NULL DEFAULT 0,
ReceivedQuantity NUMERIC NOT NULL DEFAULT 0,
Unit TEXT NOT NULL,
CreatedAt TEXT NOT NULL,
UpdatedAt TEXT NULL,
IsActive INTEGER NOT NULL DEFAULT 1,
FOREIGN KEY (TransferOrderId) REFERENCES TransferOrders(Id),
FOREIGN KEY (ItemId) REFERENCES Items(Id)
);
CREATE TABLE IF NOT EXISTS PettyCashTransactions (
Id TEXT PRIMARY KEY,
TransactionNumber TEXT NOT NULL UNIQUE,
StoreId TEXT NOT NULL,
TransactionDate TEXT NOT NULL,
TransactionType INTEGER NOT NULL,
ExpenseCategory TEXT NOT NULL,
Amount NUMERIC NOT NULL,
ReceiptImagePath TEXT NULL,
Status INTEGER NOT NULL,
CreatedAt TEXT NOT NULL,
UpdatedAt TEXT NULL,
IsActive INTEGER NOT NULL DEFAULT 1,
FOREIGN KEY (StoreId) REFERENCES Stores(Id)
);
CREATE TABLE IF NOT EXISTS OcrDocuments (
Id TEXT PRIMARY KEY,
DocumentNumber TEXT NOT NULL UNIQUE,
FilePath TEXT NOT NULL,
SupplierName TEXT NULL,
DocumentDate TEXT NULL,
Status INTEGER NOT NULL,
TotalAmount NUMERIC NULL,
RawText TEXT NULL,
CreatedAt TEXT NOT NULL,
UpdatedAt TEXT NULL,
IsActive INTEGER NOT NULL DEFAULT 1
);
""";
public const string SeedData = """
INSERT OR IGNORE INTO Stores (Id, Code, Name, StoreType, CreatedAt, IsActive)
VALUES
('11111111-1111-1111-1111-111111111111', 'HQ', '中央廚房', 3, datetime('now'), 1),
('22222222-2222-2222-2222-222222222222', 'S001', '直營一店', 1, datetime('now'), 1);
INSERT OR IGNORE INTO Warehouses (Id, Code, Name, StoreId, WarehouseType, CreatedAt, IsActive)
VALUES
('33333333-3333-3333-3333-333333333333', 'WH-HQ', '中央廚房主倉', '11111111-1111-1111-1111-111111111111', 2, datetime('now'), 1),
('44444444-4444-4444-4444-444444444444', 'WH-S001', '直營一店倉', '22222222-2222-2222-2222-222222222222', 3, datetime('now'), 1);
INSERT OR IGNORE INTO Suppliers (Id, Code, Name, PaymentTermDays, CreatedAt, IsActive)
VALUES ('55555555-5555-5555-5555-555555555555', 'SUP-FLOUR', '範例麵粉供應商', 30, datetime('now'), 1);
INSERT OR IGNORE INTO Items (Id, Sku, Name, ItemType, CategoryName, BaseUnit, SafetyStockQuantity, ShelfLifeDays, CreatedAt, IsActive)
VALUES
('66666666-6666-6666-6666-666666666666', 'RM-FLOUR-001', '高筋麵粉', 1, '麵粉', 'kg', 50, 180, datetime('now'), 1),
('77777777-7777-7777-7777-777777777777', 'PKG-BAG-001', '吐司袋', 4, '包材', '個', 500, NULL, datetime('now'), 1);
""";
}
|