Upgrade places data after merger using .NET Core and PostgreSQL

 

using Dapper;
using Npgsql;
using System.ComponentModel.DataAnnotations;
using System.Text.Json;
using System.Text.Json.Serialization;

namespace ConsoleApp1;

public class Program
{
    public class ProvinceResponse
    {
        public bool success { get; set; }
        public List? data { get; set; }
    }
    public class WardResponse
    {
        public bool success { get; set; }
        public ProvinceDto? data { get; set; }
    }
    public class ProvinceDto
    {
        [JsonPropertyName("province_code")]
        public string? ProvinceCode { get; set; }

        [JsonPropertyName("short_code")]
        public string? ShortCode { get; set; }

        [JsonPropertyName("place_type")]
        public string? PlaceType { get; set; }

        [JsonPropertyName("name")]
        public string? Name { get; set; }

        public byte Type { get; set; }

        [JsonPropertyName("ward_count")]
        public int WardCount { get; set; }

        public List? sample_wards { get; set; }
    }
    public class SampleWard
    {
        [JsonPropertyName("ward_code")]
        public string? WardCode { get; set; }

        [JsonPropertyName("new_name")]
        public string? Name { get; set; }
        public string? ProvinceCode { get; set; }
        public byte Type { get; set; }
    }
    public class Province
    {
        public int Id { get; set; }

        [Required]
        [MaxLength(255)]
        public string? Name { get; set; }

        public byte Type { get; set; }

        [MaxLength(20)]
        public string? ProvinceCode { get; set; }

        public string? ShortCode { get; set; }
        public string? PlaceType { get; set; }
        public byte WardCount { get; set; }

        public string? CreatedBy { get; set; }

        public DateTime CreatedOn { get; set; }
        public string? UpdatedBy { get; set; }

        public DateTime? UpdatedOn { get; set; }

        public bool IsDeleted { get; set; }
    }
    public class Ward
    {
        public int Id { get; set; } = default!;

        [MaxLength(20)]
        public string? WardCode { get; set; }

        [MaxLength(255)]
        [Required]
        public string Name { get; set; } = default!;

        [MaxLength(2)]
        public string? ProvinceCode { get; set; }

        public byte Type { get; set; }

        public string? CreatedBy { get; set; }

        public DateTime CreatedOn { get; set; }

        public string? UpdatedBy { get; set; }

        public DateTime? UpdatedOn { get; set; }

        public bool IsDeleted { get; set; }
    }

    private static readonly HttpClient http = new();
    private static readonly JsonSerializerOptions jsonOptions = new()
    {
        PropertyNamingPolicy = JsonNamingPolicy.SnakeCaseLower,
        PropertyNameCaseInsensitive = true
    };

    private static string GetConnectionString()
    {
        return new NpgsqlConnectionStringBuilder
        {
            Host = "xxx",
            Port = xxx,
            Database = "xxx",
            Username = "postgres",
            Password = "xxx",
            SslMode = SslMode.Prefer,
            TrustServerCertificate = true
        }.ToString();
    }

    private static async Task> FetchProvincesAsync()
    {
        var json = await http.GetStringAsync("https://don-vi-hanh-chinh.vercel.app/api/provinces");
        var response = JsonSerializer.Deserialize(json, jsonOptions);
        return response?.data ?? new();
    }

    private static async Task FetchProvinceDetailAsync(string provinceName, int sampleLimit)
    {
        var encodedName = Uri.EscapeDataString(provinceName);
        var url = $"https://don-vi-hanh-chinh.vercel.app/api/provinces/{encodedName}?include_sample=true&sample_limit={sampleLimit}";
        var json = await http.GetStringAsync(url);
        var result = JsonSerializer.Deserialize(json, jsonOptions);
        return result;
    }

    private static async Task InsertProvinceAsync(NpgsqlConnection conn, ProvinceDto dto)
    {
        int type = dto.Name.Contains("Thành phố") ? 1 : dto.Name.Contains("Tỉnh") ? 0 : (byte)0;

        var sql = @"
            INSERT INTO public.""Provinces""(
                ""ProvinceCode"", ""ShortCode"", ""PlaceType"", ""WardCount"", ""Name"", ""Type"",
                ""CreatedBy"", ""CreatedOn"", ""UpdatedBy"", ""UpdatedOn"", ""IsDeleted"")
            VALUES (
                @ProvinceCode, @ShortCode, @PlaceType, @WardCount, @Name, @Type,
                @CreatedBy, @CreatedOn, @UpdatedBy, @UpdatedOn, @IsDeleted
            )";

        var province = new Province
        {
            ProvinceCode = dto.ProvinceCode,
            ShortCode = dto.ShortCode,
            PlaceType = dto.PlaceType,
            Name = dto.Name,
            WardCount = (byte)dto.WardCount,
            Type = (byte)type,
            CreatedBy = "system",
            CreatedOn = DateTime.UtcNow,
            UpdatedBy = null,
            UpdatedOn = null,
            IsDeleted = false
        };

        await conn.ExecuteAsync(sql, province);
        Console.WriteLine($"✅ Inserted province: {province.Name}");
    }

    private static async Task InsertWardsAsync(NpgsqlConnection conn, string provinceCode, string provinceName, int wardCount)
    {
        var provinceDetail = await FetchProvinceDetailAsync(provinceName, wardCount);

        if (provinceDetail?.data?.sample_wards == null) return;

        var sql = @"
            INSERT INTO public.""Wards""(
                ""WardCode"", ""Name"", ""ProvinceCode"", ""Type"",
                ""CreatedBy"", ""CreatedOn"", ""UpdatedBy"", ""UpdatedOn"", ""IsDeleted"")
            VALUES (
                @WardCode, @Name, @ProvinceCode, @Type,
                @CreatedBy, @CreatedOn, @UpdatedBy, @UpdatedOn, @IsDeleted
            )";

        foreach (var wardDto in provinceDetail?.data?.sample_wards)
        {
            int type = wardDto.Name.Contains("Xã") ? 0 :
                       wardDto.Name.Contains("Phường") ? 1 :
                       wardDto.Name.Contains("Đặc khu") ? 2 : (byte)0;

            var ward = new Ward
            {
                ProvinceCode = provinceCode,
                WardCode = wardDto.WardCode,
                Name = wardDto.Name,
                Type = (byte)type,
                CreatedBy = "system",
                CreatedOn = DateTime.UtcNow,
                UpdatedBy = null,
                UpdatedOn = null,
                IsDeleted = false
            };

            await conn.ExecuteAsync(sql, ward);
            Console.WriteLine($"    ↪️ Inserted ward: {ward.Name}");
        }

        Console.WriteLine("✅ Completed Wards.");
    }

    public static async Task Main(string[] args)
    {
        await using var conn = new NpgsqlConnection(GetConnectionString());
        await conn.OpenAsync();

        var provinces = await FetchProvincesAsync();

        foreach (var province in provinces)
        {
            await InsertProvinceAsync(conn, province);
            await InsertWardsAsync(conn, province.ProvinceCode, province.Name, province.WardCount);
        }

        Console.WriteLine("🎉 All done.");
    }
}