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 List? Provinces { get; set; }
    public List? Wards { get; set; }

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

        [JsonPropertyName("code")]
        public int? ProvinceCode { get; set; }

        [JsonPropertyName("division_type")]
        public string? DivisionType { get; set; }

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

        [JsonPropertyName("phone_code")]
        public int? PhoneCode { get; set; }
    }

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

        [JsonPropertyName("code")]
        public int? WardCode { get; set; }

        [JsonPropertyName("division_type")]
        public string? DivisionType { get; set; }

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

        [JsonPropertyName("province_code")]
        public int? ProvinceCode { get; set; }
    }

    public class Province
    {
        public int Id { get; set; }

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

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

        public string? DivisionType { get; set; }
        public string? ShortCode { get; set; }
        public int? PhoneCode { 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(255)]
        [Required]
        public string Name { get; set; } = default!;

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

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

        public string? ShortCode { get; set; }
        public string? DivisionType { 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 System.Text.Json.JsonSerializerOptions jsonOptions = new()
    {
        PropertyNamingPolicy = System.Text.Json.JsonNamingPolicy.SnakeCaseLower,
        PropertyNameCaseInsensitive = true
    };

    private static string GetConnectionString()
    {
        return new NpgsqlConnectionStringBuilder
        {
            Host = "14.225.218.90",
            Port = 5432,
            Database = "agentfinder25.dev",
            Username = "postgres",
            Password = "123456a@",
            SslMode = SslMode.Prefer,
            TrustServerCertificate = true
        }.ToString();
    }

    private static async Task> FetchProvincesAsync()
    {
        var json = await http.GetStringAsync("https://provinces.open-api.vn/api/v2/");

        var response = JsonSerializer.Deserialize>(json, jsonOptions);

        return response ?? new();
    }

    private static async Task> FetchWardDetailAsync()
    {
        var json = await http.GetStringAsync("https://provinces.open-api.vn/api/v2/w/");

        var result = JsonSerializer.Deserialize>(json, jsonOptions);

        return result ?? new();
    }

    private static async Task InsertProvinceAsync(NpgsqlConnection conn, ProvinceDtos dto)
    {
        var sql = @"
            INSERT INTO public.""Provinces""(""ProvinceCode"",""Name"",
                                                ""CreatedBy"", ""CreatedOn"", ""UpdatedBy"", ""UpdatedOn"", ""IsDeleted"",
                                                ""PhoneCode"", ""ShortCode"", ""DivisionType"")
            VALUES ( @ProvinceCode, @Name,
                        @CreatedBy, @CreatedOn, @UpdatedBy, @UpdatedOn, @IsDeleted,
                        @PhoneCode, @ShortCode, @DivisionType
            )";

        var province = new Province
        {
            ProvinceCode = dto.ProvinceCode,
            ShortCode = dto.ShortCode,
            Name = dto.Name,
            DivisionType = dto.DivisionType,
            PhoneCode = dto.PhoneCode,
            CreatedBy = "admin",
            CreatedOn = DateTime.UtcNow,
            UpdatedBy = "admin",
            UpdatedOn = DateTime.UtcNow,
            IsDeleted = false
        };

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

    private static async Task InsertWardsAsync(NpgsqlConnection conn, WardDtos dto)
    {
        var sql = @"
            INSERT INTO public.""Wards""(
                ""WardCode"", ""Name"",
                ""CreatedBy"", ""CreatedOn"", ""UpdatedBy"", ""UpdatedOn"", ""IsDeleted"",
                ""ProvinceCode"", ""DivisionType"", ""ShortCode"")
            VALUES (
                @WardCode, @Name,
                @CreatedBy, @CreatedOn, @UpdatedBy, @UpdatedOn, @IsDeleted,
                @ProvinceCode, @DivisionType, @ShortCode
            )";

        var ward = new Ward
        {
            ProvinceCode = dto.ProvinceCode,
            DivisionType = dto.DivisionType,
            Name = dto.Name,
            ShortCode = dto.ShortCode,
            WardCode = dto.WardCode,
            CreatedBy = "admin",
            CreatedOn = DateTime.UtcNow,
            UpdatedBy = "admin",
            UpdatedOn = DateTime.UtcNow,
            IsDeleted = false
        };

        await conn.ExecuteAsync(sql, ward);

        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();
        var wards = await FetchWardDetailAsync();

        foreach (var province in provinces)
        {
            await InsertProvinceAsync(conn, province);
        }
        foreach (var ward in wards)
        {
            await InsertWardsAsync(conn, ward);
        }

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