petwash/PetWash.Api/Program.cs

using System.Data;
using System.Data.Common;
using Microsoft.EntityFrameworkCore;
using PetWash.Api.Data;
using PetWash.Api.Models;
using PetWash.Api.Services;

var builder = WebApplication.CreateBuilder(args);

var dbProvider = builder.Configuration.GetValue<string>("DatabaseProvider") ?? "Sqlite";
var connectionString = dbProvider == "MySql"
    ? builder.Configuration.GetConnectionString("MySqlConnection")
    : builder.Configuration.GetConnectionString("DefaultConnection");

builder.Services.AddDbContext<PetWashDbContext>(options =>
{
    if (dbProvider == "MySql")
    {
        var serverVersion = new MySqlServerVersion(new Version(8, 0, 21));
        options.UseMySql(connectionString, serverVersion);
    }
    else
    {
        options.UseSqlite(connectionString ?? "Data Source=petwash.db");
    }
});

builder.Services.AddSingleton<MqttService>();
builder.Services.AddHostedService(provider => provider.GetRequiredService<MqttService>());
builder.Services.AddScoped<OrderService>();
builder.Services.Configure<WeChatPayOptions>(builder.Configuration.GetSection("WeChatPay"));
builder.Services.AddHttpClient<WeChatPayService>(client =>
{
    client.BaseAddress = new Uri("https://api.mch.weixin.qq.com");
    client.Timeout = TimeSpan.FromSeconds(15);
});

builder.Services.AddCors(options =>
{
    options.AddDefaultPolicy(policy =>
    {
        policy.AllowAnyOrigin()
              .AllowAnyMethod()
              .AllowAnyHeader();
    });
});

builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<PetWashDbContext>();
    var logger = scope.ServiceProvider.GetRequiredService<ILogger<Program>>();

    try
    {
        logger.LogInformation("Starting database initialization.");

        var created = db.Database.EnsureCreated();
        if (created)
        {
            logger.LogInformation("Database created and seed data inserted.");
        }
        else
        {
            logger.LogInformation("Database already exists.");
        }

        EnsurePackageTimingColumns(db, logger);
        EnsureOrderPaymentColumns(db, logger);

        foreach (var package in PackageCatalog.DefaultPackages)
        {
            var existingPackage = db.Packages.FirstOrDefault(x => x.Id == package.Id);
            if (existingPackage is null)
            {
                db.Packages.Add(new Package
                {
                    Id = package.Id,
                    Name = package.Name,
                    Price = package.Price,
                    DurationMinutes = package.DurationMinutes,
                    Description = package.Description,
                    FirstSprayWaterTime = package.FirstSprayWaterTime,
                    AfterShampoo1SprayTime = package.AfterShampoo1SprayTime,
                    AfterShampoo2SprayTime = package.AfterShampoo2SprayTime,
                    AfterShampoo3SprayTime = package.AfterShampoo3SprayTime,
                    SprayShampoo1Time = package.SprayShampoo1Time,
                    SprayShampoo2Time = package.SprayShampoo2Time,
                    SprayShampoo3Time = package.SprayShampoo3Time,
                    HotAirTime = package.HotAirTime,
                    ColdAirTime = package.ColdAirTime,
                    UvSterilizationTime = package.UvSterilizationTime
                });
            }
            else
            {
                existingPackage.Name = package.Name;
                existingPackage.Price = package.Price;
                existingPackage.DurationMinutes = package.DurationMinutes;
                existingPackage.Description = package.Description;
                existingPackage.FirstSprayWaterTime = package.FirstSprayWaterTime;
                existingPackage.AfterShampoo1SprayTime = package.AfterShampoo1SprayTime;
                existingPackage.AfterShampoo2SprayTime = package.AfterShampoo2SprayTime;
                existingPackage.AfterShampoo3SprayTime = package.AfterShampoo3SprayTime;
                existingPackage.SprayShampoo1Time = package.SprayShampoo1Time;
                existingPackage.SprayShampoo2Time = package.SprayShampoo2Time;
                existingPackage.SprayShampoo3Time = package.SprayShampoo3Time;
                existingPackage.HotAirTime = package.HotAirTime;
                existingPackage.ColdAirTime = package.ColdAirTime;
                existingPackage.UvSterilizationTime = package.UvSterilizationTime;
            }
        }

        db.SaveChanges();
    }
    catch (Exception ex)
    {
        logger.LogError(ex, "Database initialization failed.");
        throw;
    }
}

app.UseSwagger();
app.UseSwaggerUI();

app.UseCors();
app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();

app.Run();

static void EnsurePackageTimingColumns(PetWashDbContext db, ILogger logger)
{
    var providerName = db.Database.ProviderName ?? string.Empty;
    var isSqlite = providerName.Contains("Sqlite", StringComparison.OrdinalIgnoreCase);
    var missingColumns = GetMissingPackageTimingColumns(db.Database.GetDbConnection(), isSqlite);

    foreach (var column in missingColumns)
    {
        var sql = isSqlite
            ? $"ALTER TABLE Packages ADD COLUMN {column.Name} INTEGER NOT NULL DEFAULT {column.DefaultValue};"
            : $"ALTER TABLE Packages ADD COLUMN {column.Name} INT NOT NULL DEFAULT {column.DefaultValue};";

        db.Database.ExecuteSqlRaw(sql);
        logger.LogInformation("Added missing package timing column {ColumnName}", column.Name);
    }
}

static List<(string Name, int DefaultValue)> GetMissingPackageTimingColumns(DbConnection connection, bool isSqlite)
{
    var expectedColumns = new List<(string Name, int DefaultValue)>
    {
        ("FirstSprayWaterTime", 2),
        ("AfterShampoo1SprayTime", 2),
        ("AfterShampoo2SprayTime", 2),
        ("AfterShampoo3SprayTime", 2),
        ("SprayShampoo1Time", 1),
        ("SprayShampoo2Time", 1),
        ("SprayShampoo3Time", 1),
        ("HotAirTime", 5),
        ("ColdAirTime", 2),
        ("UvSterilizationTime", 2)
    };

    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
    }

    using var command = connection.CreateCommand();
    command.CommandText = isSqlite ? "PRAGMA table_info(Packages);" : "SHOW COLUMNS FROM Packages;";

    using var reader = command.ExecuteReader();
    var existingColumns = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
    while (reader.Read())
    {
        existingColumns.Add(reader.GetString(isSqlite ? 1 : 0));
    }

    return expectedColumns
        .Where(column => !existingColumns.Contains(column.Name))
        .ToList();
}

static void EnsureOrderPaymentColumns(PetWashDbContext db, ILogger logger)
{
    var providerName = db.Database.ProviderName ?? string.Empty;
    var isSqlite = providerName.Contains("Sqlite", StringComparison.OrdinalIgnoreCase);
    var missingColumns = GetMissingOrderPaymentColumns(db.Database.GetDbConnection(), isSqlite);

    foreach (var column in missingColumns)
    {
        var sql = isSqlite
            ? $"ALTER TABLE Orders ADD COLUMN {column.Name} {column.SqliteTypeClause};"
            : $"ALTER TABLE Orders ADD COLUMN {column.Name} {column.MySqlTypeClause};";

        db.Database.ExecuteSqlRaw(sql);
        logger.LogInformation("Added missing order payment column {ColumnName}", column.Name);
    }
}

static List<(string Name, string SqliteTypeClause, string MySqlTypeClause)> GetMissingOrderPaymentColumns(
    DbConnection connection,
    bool isSqlite)
{
    var expectedColumns = new List<(string Name, string SqliteTypeClause, string MySqlTypeClause)>
    {
        ("OutTradeNo", "TEXT NOT NULL DEFAULT ''", "VARCHAR(128) NOT NULL DEFAULT ''"),
        ("PaymentCodeUrl", "TEXT NOT NULL DEFAULT ''", "VARCHAR(2048) NOT NULL DEFAULT ''"),
        ("PaymentExpiresAt", "TEXT NULL", "DATETIME NULL"),
        ("PaymentInitError", "TEXT NOT NULL DEFAULT ''", "VARCHAR(2048) NOT NULL DEFAULT ''")
    };

    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
    }

    using var command = connection.CreateCommand();
    command.CommandText = isSqlite ? "PRAGMA table_info(Orders);" : "SHOW COLUMNS FROM Orders;";

    using var reader = command.ExecuteReader();
    var existingColumns = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
    while (reader.Read())
    {
        existingColumns.Add(reader.GetString(isSqlite ? 1 : 0));
    }

    return expectedColumns
        .Where(column => !existingColumns.Contains(column.Name))
        .ToList();
}