src/BakeryErp.Infrastructure/Database/SqliteSchema.cs

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);
        """;
}