import type { Kysely } from "kysely";
import { sql } from "kysely";

const ANY_COUNTRY = "";

const SEED_ALIASES: { country: string; city: string; aliases: string[] }[] = [
    {
        country: "india",
        city: "bengaluru",
        aliases: [
            "bangalore", "bangaloru", "bangaloure", "bangalouru", "bangalure", "bangaluru", "bangalur",
            "bangaore", "banglaore", "banglore", "bangluru", "bengalaru", "bengalore", "bengalur",
            "bengalure", "blr",
        ],
    },
    { country: "india", city: "mumbai", aliases: ["bombay", "mumbaiv"] },
    { country: "india", city: "delhi", aliases: ["new delhi", "newdelhi", "dehli", "delhiy"] },
    { country: "india", city: "gurugram", aliases: ["gurgaon"] },
    { country: "india", city: "mysuru", aliases: ["mysore"] },
    { country: "india", city: "mangaluru", aliases: ["mangalore"] },
    { country: "india", city: "belagavi", aliases: ["belgaum", "belgavi"] },
    { country: "india", city: "kolkata", aliases: ["calcutta"] },
    { country: "india", city: "chennai", aliases: ["madras"] },
    { country: "india", city: "prayagraj", aliases: ["allahabad"] },
    { country: "india", city: "ahilyanagar", aliases: ["ahmednagar", "ahmadnagar"] },
    { country: "india", city: "mohali", aliases: ["sas nagar", "sahibzada ajit singh nagar"] },
    { country: "india", city: "hyderabad", aliases: ["hyderbad", "hydrebad"] },
    { country: "india", city: "ghaziabad", aliases: ["gaziabad"] },
    { country: "india", city: "nashik", aliases: ["nasik"] },
    { country: "india", city: "ahmedabad", aliases: ["ahemdabad", "ahmed abad", "ahemedabad", "ahmadabad", "ahmdabad"] },
    { country: "india", city: "kolhapur", aliases: ["kohlapur", "kolapur"] },
    { country: "india", city: "vadodara", aliases: ["vadodra", "baroda"] },
    { country: "india", city: "tumakuru", aliases: ["tumkur", "tumakur"] },
    { country: "india", city: "davanagere", aliases: ["davangere"] },
    { country: "india", city: "tiruppur", aliases: ["tirupur"] },
    { country: "india", city: "tiruchirappalli", aliases: ["tiruchirapalli", "trichy"] },
    { country: "india", city: "thiruvananthapuram", aliases: ["tiruvananthapuram", "trivandrum"] },
    { country: "india", city: "vijayapura", aliases: ["vijayapur"] },
    { country: "india", city: "dombivli", aliases: ["dombivili"] },
    { country: "india", city: "ulhasnagar", aliases: ["ulhas nagar"] },
    { country: "india", city: "faridabad", aliases: ["fabridabad"] },
    { country: "india", city: "secunderabad", aliases: ["secunderadad"] },
    { country: "india", city: "amritsar", aliases: ["amristsar"] },
    { country: "india", city: "jalandhar", aliases: ["jalandar", "jalandher"] },
    { country: "india", city: "pathankot", aliases: ["pathan kot"] },
    { country: "india", city: "alappuzha", aliases: ["allappuzha", "alleppey"] },
    { country: "india", city: "ernakulam", aliases: ["eruakulam"] },
    { country: "india", city: "kochi", aliases: ["cochin"] },
    { country: "india", city: "malappuram", aliases: ["mallapuram"] },
    { country: "india", city: "junagadh", aliases: ["junagarh"] },
    { country: "india", city: "bareilly", aliases: ["barelly", "bereilly"] },
    { country: "india", city: "bulandshahr", aliases: ["bulandshahar"] },
    { country: "india", city: "muzaffarnagar", aliases: ["muzaffar nagar", "muzzaffarnagar"] },
    { country: "india", city: "gautam buddha nagar", aliases: ["gautam budh nagar", "gautambuddh nagar"] },
    { country: "india", city: "puducherry", aliases: ["pondicherry"] },
    { country: "india", city: "varanasi", aliases: ["banaras", "benares"] },
    { country: ANY_COUNTRY, city: "auckland", aliases: ["aukland"] },
    { country: ANY_COUNTRY, city: "abu dhabi", aliases: ["abudhabi"] },
];

const SEED_RULES: {
    country: string;
    city: string;
    include_sub_areas: boolean;
    exclude_keys?: string[];
    include_state?: string;
    state_exclude_keys?: string[];
}[] = [
    { country: "india", city: "bengaluru", include_sub_areas: true },
    {
        country: "india",
        city: "mumbai",
        include_sub_areas: true,
        exclude_keys: ["navi mumbai", "navimumbai", "nauimumbai", "new mumbai"],
    },
    {
        country: "india",
        city: "delhi",
        include_sub_areas: true,
        include_state: "delhi",
        state_exclude_keys: ["gurugram", "gurgaon", "noida", "faridabad", "ghaziabad", "indirapuram"],
    },
    { country: "india", city: "thane", include_sub_areas: true },
    { country: "india", city: "kalyan", include_sub_areas: true },
];

export async function up(db: Kysely<any>): Promise<void> {
    await db.schema
        .createTable("city_aliases")
        .ifNotExists()
        .addColumn("id", "uuid", (col) => col.primaryKey().defaultTo(sql`gen_random_uuid()`))
        .addColumn("country_key", "text", (col) => col.notNull().defaultTo(ANY_COUNTRY))
        .addColumn("city_key", "text", (col) => col.notNull())
        .addColumn("alias_key", "text", (col) => col.notNull())
        .addColumn("created_by", "text")
        .addColumn("created_at", "timestamptz", (col) => col.notNull().defaultTo(sql`now()`))
        .addUniqueConstraint("city_aliases_country_alias_unique", ["country_key", "alias_key"])
        .execute();

    await db.schema
        .createTable("city_rules")
        .ifNotExists()
        .addColumn("id", "uuid", (col) => col.primaryKey().defaultTo(sql`gen_random_uuid()`))
        .addColumn("country_key", "text", (col) => col.notNull().defaultTo(ANY_COUNTRY))
        .addColumn("city_key", "text", (col) => col.notNull())
        .addColumn("include_sub_areas", "boolean", (col) => col.notNull().defaultTo(false))
        .addColumn("exclude_keys", sql`text[]`, (col) => col.notNull().defaultTo(sql`'{}'::text[]`))
        .addColumn("include_state", "text")
        .addColumn("state_exclude_keys", sql`text[]`, (col) => col.notNull().defaultTo(sql`'{}'::text[]`))
        .addColumn("updated_by", "text")
        .addColumn("updated_at", "timestamptz", (col) => col.notNull().defaultTo(sql`now()`))
        .addUniqueConstraint("city_rules_country_city_unique", ["country_key", "city_key"])
        .execute();

    await db
        .insertInto("city_aliases")
        .values(
            SEED_ALIASES.flatMap(({ country, city, aliases }) =>
                aliases.map((alias) => ({ country_key: country, city_key: city, alias_key: alias, created_by: "seed" })),
            ),
        )
        .onConflict((oc) => oc.columns(["country_key", "alias_key"]).doNothing())
        .execute();

    await db
        .insertInto("city_rules")
        .values(
            SEED_RULES.map((rule) => ({
                country_key: rule.country,
                city_key: rule.city,
                include_sub_areas: rule.include_sub_areas,
                exclude_keys: sql`${rule.exclude_keys ?? []}::text[]`,
                include_state: rule.include_state ?? null,
                state_exclude_keys: sql`${rule.state_exclude_keys ?? []}::text[]`,
                updated_by: "seed",
            })),
        )
        .onConflict((oc) => oc.columns(["country_key", "city_key"]).doNothing())
        .execute();
}

export async function down(db: Kysely<any>): Promise<void> {
    await db.schema.dropTable("city_rules").ifExists().execute();
    await db.schema.dropTable("city_aliases").ifExists().execute();
}
