import { sql, type RawBuilder } from "kysely";

export type CityAliasRow = { country_key: string; city_key: string; alias_key: string };

export type CityRuleRow = {
    country_key: string;
    city_key: string;
    include_sub_areas: boolean;
    exclude_keys: string[];
    include_state: string | null;
    state_exclude_keys: string[];
};

export type CityMatchConfig = { aliases: CityAliasRow[]; rules: CityRuleRow[] };

export const EMPTY_CITY_CONFIG: CityMatchConfig = { aliases: [], rules: [] };

const ANY_COUNTRY = "";
const FILLER_WORDS = ["dist", "district", "city", "urban", "rural"];
const COMBINING_MARKS = "[\\u0300-\\u036f]";
const SINGLE_LETTERS_FROM = "đĐøØłŁıħĦŧŦ";
const SINGLE_LETTERS_TO = "ddoollihhtt";
const MULTI_LETTERS: [string, string][] = [
    ["ß", "ss"],
    ["æ", "ae"],
    ["Æ", "ae"],
    ["œ", "oe"],
    ["Œ", "oe"],
    ["þ", "th"],
    ["Þ", "th"],
];
const NON_LETTERS = "[\\x21-\\x40\\x5b-\\x60\\x7b-\\x7e\\u00a0-\\u00bf\\u2000-\\u206f\\u3000\\ufeff]";
const FILLER_PATTERN = `(^|\\s)(${FILLER_WORDS.join("|")})(?=\\s|$)`;
const WHITESPACE_RUN = "\\s+";

function foldLetters(value: string): string {
    const stripped = value.normalize("NFD").replace(new RegExp(COMBINING_MARKS, "g"), "");
    const single = [...stripped]
        .map((ch) => {
            const index = SINGLE_LETTERS_FROM.indexOf(ch);
            return index === -1 ? ch : SINGLE_LETTERS_TO[index];
        })
        .join("");
    return MULTI_LETTERS.reduce((acc, [from, to]) => acc.split(from).join(to), single).toLowerCase();
}

const collapse = (value: string) => value.replace(new RegExp(WHITESPACE_RUN, "g"), " ").trim();

const lettersOnly = (value: string) => foldLetters(value).replace(new RegExp(NON_LETTERS, "g"), " ");

export function cityKey(value: string): string {
    return collapse(lettersOnly(value).replace(new RegExp(FILLER_PATTERN, "g"), " "));
}

export function countryKey(value: string): string {
    return collapse(lettersOnly(value));
}

function foldLettersSql(value: RawBuilder<string>): RawBuilder<string> {
    const stripped = sql<string>`regexp_replace(normalize(coalesce(${value}, ''), NFD), ${COMBINING_MARKS}, '', 'g')`;
    const single = sql<string>`translate(${stripped}, ${SINGLE_LETTERS_FROM}, ${SINGLE_LETTERS_TO})`;
    const multi = MULTI_LETTERS.reduce(
        (acc, [from, to]) => sql<string>`replace(${acc}, ${from}, ${to})`,
        single,
    );
    return sql<string>`regexp_replace(lower(${multi}), ${NON_LETTERS}, ' ', 'g')`;
}

function cityKeyOf(value: RawBuilder<string>): RawBuilder<string> {
    return sql<string>`btrim(regexp_replace(
        regexp_replace(${foldLettersSql(value)}, ${FILLER_PATTERN}, ' ', 'g'),
        ${WHITESPACE_RUN}, ' ', 'g'))`;
}

export function cityKeySql(column: string): RawBuilder<string> {
    return cityKeyOf(sql<string>`${sql.ref(column)}`);
}

function countryKeySql(column: string): RawBuilder<string> {
    return sql<string>`btrim(regexp_replace(${foldLettersSql(sql<string>`${sql.ref(column)}`)}, ${WHITESPACE_RUN}, ' ', 'g'))`;
}

const CITY = cityKeySql("member_profiles.city");
const COUNTRY = countryKeySql("member_profiles.country");

const escapeRegex = (value: string) => value.replace(/[.*+?^${}()|[\]\\]/g, "\\$&");

const wordPattern = (words: string[]) => `\\y(${words.map(escapeRegex).join("|")})\\y`;

const inCountry = (country: string, condition: RawBuilder<boolean>): RawBuilder<boolean> =>
    country === ANY_COUNTRY ? condition : sql<boolean>`(${COUNTRY} = ${country} AND ${condition})`;

function canonicalCities(key: string, config: CityMatchConfig): string[] {
    const viaAlias = config.aliases.filter((a) => a.alias_key === key).map((a) => a.city_key);
    return [...new Set([key, ...viaAlias])];
}

function keysInScope(city: string, country: string, config: CityMatchConfig): string[] {
    const aliases = config.aliases
        .filter((a) => a.city_key === city && (a.country_key === country || a.country_key === ANY_COUNTRY))
        .map((a) => a.alias_key);
    return [...new Set([city, ...aliases])];
}

function aliasConditions(city: string, config: CityMatchConfig): RawBuilder<boolean>[] {
    const countries = new Set(config.aliases.filter((a) => a.city_key === city).map((a) => a.country_key));
    return [...countries].map((country) => {
        const keys = config.aliases
            .filter((a) => a.city_key === city && a.country_key === country)
            .map((a) => a.alias_key);
        return inCountry(country, sql<boolean>`${CITY} = ANY(${keys}::text[])`);
    });
}

function ruleConditions(city: string, config: CityMatchConfig): RawBuilder<boolean>[] {
    return config.rules
        .filter((rule) => rule.city_key === city)
        .flatMap((rule) => {
            const conditions: RawBuilder<boolean>[] = [];
            if (rule.include_sub_areas) {
                const subArea = sql<boolean>`${CITY} ~ ${wordPattern(keysInScope(city, rule.country_key, config))}`;
                conditions.push(
                    rule.exclude_keys.length
                        ? sql<boolean>`(${subArea} AND ${CITY} !~ ${wordPattern(rule.exclude_keys)})`
                        : subArea,
                );
            }
            if (rule.include_state) {
                const inState = sql<boolean>`member_profiles.state ~* ${rule.include_state}`;
                conditions.push(
                    rule.state_exclude_keys.length
                        ? sql<boolean>`(${inState} AND ${CITY} !~ ${wordPattern(rule.state_exclude_keys)})`
                        : inState,
                );
            }
            return conditions.map((condition) => inCountry(rule.country_key, condition));
        });
}

export function cityMatchCondition(
    cities: string[],
    config: CityMatchConfig = EMPTY_CITY_CONFIG,
): RawBuilder<boolean> | null {
    const requested = cities.filter((city) => typeof city === "string" && city.trim() !== "");
    if (requested.length === 0) return null;

    const selected = [...new Set(requested.map(cityKey).filter(Boolean))];
    if (selected.length === 0) return sql<boolean>`false`;

    const canonical = [...new Set(selected.flatMap((key) => canonicalCities(key, config)))];
    const conditions: RawBuilder<boolean>[] = [
        sql<boolean>`${CITY} IN (SELECT ${cityKeyOf(sql<string>`${sql.ref("selected_city.value")}`)} FROM unnest(${requested}::text[]) AS selected_city(value))`,
        sql<boolean>`${CITY} = ANY(${canonical}::text[])`,
        ...canonical.flatMap((city) => [...aliasConditions(city, config), ...ruleConditions(city, config)]),
    ];

    return sql<boolean>`(${sql.join(conditions, sql` OR `)})`;
}
