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

export class ExternalMemberRepository {
    private db: Kysely<any>;

    constructor(db: Kysely<any>) {
        this.db = db;
    }

    async findEmailsByCriteria(criteria: any): Promise<string[]> {
        try {
            if (!criteria) {
                return [];
            }
            const hasDemographics = (criteria.membershipType && criteria.membershipType.length > 0) ||
                (criteria.country && criteria.country.length > 0) ||
                (criteria.city && criteria.city.length > 0) ||
                (criteria.userSegment && criteria.userSegment.length > 0) ||
                (criteria.membershipStatus && criteria.membershipStatus.length > 0) ||
                (!!criteria.onlyWithValidVersion);

            let emailsFromDB: string[] = [];

            if (hasDemographics) {
                // Mirrors the reference audience SQL:
                //   SELECT DISTINCT u.email, mp.country, mp.state, mp.city, m.membership_number
                //   FROM members m
                //   INNER JOIN users u ON m.user_id = u.id
                //   INNER JOIN member_preferences mpref ON m.id = mpref.member_id
                //   INNER JOIN member_profiles mp ON m.id = mp.member_id
                //   INNER JOIN (SELECT user_id, MAX(application_version) AS latest_version
                //               FROM sessions WHERE application_version >= '0.0.14'
                //               GROUP BY user_id) s ON u.id = s.user_id
                //   WHERE mpref.enable_personal_notifications = TRUE
                //     AND u.email IS NOT NULL AND u.email != ''
                //     AND mp.country ILIKE ANY (ARRAY[...]);
                const joined: any = this.db.selectFrom("members")
                    .innerJoin("users", "users.id", "members.user_id")
                    .innerJoin("member_preferences", "member_preferences.member_id", "members.id")
                    .innerJoin("member_profiles", "member_profiles.member_id", "members.id")
                    .innerJoin(
                        (eb: any) => eb.selectFrom("sessions")
                            .select([
                                "sessions.user_id",
                                sql<string>`MAX(sessions.application_version)`.as("latest_version"),
                            ])
                            .where("sessions.application_version", ">=", "0.0.14")
                            .groupBy("sessions.user_id")
                            .as("s"),
                        (join: any) => join.onRef("s.user_id", "=", "users.id"),
                    );

                let query: any = joined
                    .select([
                        "users.email",
                        "member_profiles.country",
                        "member_profiles.state",
                        "member_profiles.city",
                        "members.membership_number",
                    ])
                    .distinct()
                    .where("member_preferences.enable_personal_notifications", "=", true)
                    .where("users.email", "is not", null)
                    .where("users.email", "!=", "");

                if (criteria.membershipType && criteria.membershipType.length > 0) {
                    query = query.where("members.membership_type_id", "in", criteria.membershipType.map((id: any) => Number(id)));
                }

                if (criteria.membershipStatus && criteria.membershipStatus.length > 0) {
                    query = query.where("members.membership_status_id", "in", criteria.membershipStatus.map((id: any) => Number(id)));
                }



                if ((criteria.country && criteria.country.length > 0) || (criteria.city && criteria.city.length > 0)) {
                    if (criteria.country && criteria.country.length > 0) {
                        query = query.where((eb: any) =>
                            eb.or(criteria.country.map((c: string) => eb("member_profiles.country", "ilike", c)))
                        );
                    }
                    if (criteria.city && criteria.city.length > 0) {
                        const countryAlsoSelected = criteria.country && criteria.country.length > 0;
                        query = query.where((eb: any) =>
                            countryAlsoSelected
                                ? eb.or([
                                    ...criteria.city.map((c: string) => eb("member_profiles.city", "ilike", c)),
                                    eb("member_profiles.city", "is", null),
                                    eb("member_profiles.city", "=", ""),
                                ])
                                : eb.or(criteria.city.map((c: string) => eb("member_profiles.city", "ilike", c)))
                        );
                    }
                }

                // Version gating (Subito v0.0.14 or above) is handled by the `s` join above.

                if (criteria.userSegment && criteria.userSegment.length > 0) {
                    query = query.where((eb: any) => {
                        return eb.exists(
                            eb.selectFrom("sessions")
                                .select("sessions.id")
                                .whereRef("sessions.user_id", "=", "users.id")
                                .where((ebSession: any) => {
                                    const orExps = [];
                                    if (criteria.userSegment.includes("android") || criteria.userSegment.includes("Android")) {
                                        orExps.push(ebSession("sessions.os", "=", "Android"));
                                    }
                                    if (criteria.userSegment.includes("ios") || criteria.userSegment.includes("iOS")) {
                                        orExps.push(ebSession("sessions.os", "=", "iOS"));
                                    }
                                    if (criteria.userSegment.includes("web") || criteria.userSegment.includes("Web")) {
                                        orExps.push(ebSession("sessions.os", "in", ["Windows", "macOS", "Linux", "Unknown OS", "Unknown", "BlackBerry"]));
                                    }
                                    return orExps.length > 0 ? ebSession.or(orExps) : ebSession("sessions.os", "=", "Unknown OS");
                                })
                        );
                    });
                }

                const rows = await query.execute();
                emailsFromDB = rows
                    .map((r: any) => r.email)
                    .filter((e: any) => typeof e === "string" && e.includes("@"));
            }

            const directEmails: string[] = [];
            const directIds: string[] = [];

            if (criteria.members && Array.isArray(criteria.members)) {
                criteria.members.forEach((m: string) => {
                    if (m.includes("@")) {
                        directEmails.push(m);
                    } else if (m.trim().length > 0) {
                        directIds.push(m.trim());
                    }
                });
            }

            if (directIds.length > 0) {
                const idRows = await this.db.selectFrom("members")
                    .innerJoin("users", "users.id", "members.user_id")
                    .select("users.email")
                    .where("members.member_number", "in", directIds)
                    .execute();
                idRows.forEach((r: any) => {
                    if (r.email)
                        directEmails.push(r.email);
                });
            }

            const allEmails = [...emailsFromDB, ...directEmails];
            return allEmails;
        } catch (error) {
            console.error("Failed to query external member DB:", error);
            throw error;
        }
    }

    async countByCriteria(criteria: any): Promise<number> {
        try {
            if (!criteria) {
                return 0;
            }
            const emails = await this.findEmailsByCriteria(criteria);
            return emails.length;
        } catch (error) {
            console.error("Failed to count external members:", error);
            return 0;
        }
    }

    async getCountries() {
        try {
            return await this.db.selectFrom("member_profiles")
                .select("country")
                .distinct()
                .where("country", "is not", null)
                .orderBy("country")
                .execute();
        } catch (e) {
            console.error("[ExternalMemberRepository] getCountries failed:", e);
            throw e;
        }
    }

    async getCities(countries?: string[]) {
        try {
            let query = this.db.selectFrom("member_profiles")
                .select("city")
                .distinct()
                .where("city", "is not", null)
                .orderBy("city");

            if (countries && countries.length > 0) {
                query = query.where("country", "in", countries);
            }
            return await query.execute();
        } catch (e) {
            console.error("[ExternalMemberRepository] getCities failed:", e);
            throw e;
        }
    }

    async getMembershipTypes() {
        try {
            const res = await this.db.selectFrom("member_account_types")
                .selectAll()
                .execute();
            return res;
        } catch (e) {
            console.error("[ExternalMemberRepository] getMembershipTypes failed:", e);
            throw e;
        }
    }

    async getAccountStatuses() {
        try {
            const res = await this.db.selectFrom("member_account_statuses")
                .selectAll()
                .execute();
            return res;
        } catch (e) {
            console.error("[ExternalMemberRepository] getAccountStatuses failed:", e);
            throw e;
        }
    }

    async getClubs() {
        try {
            const res = await this.db.selectFrom("membership_clubs")
                .selectAll()
                .execute();
            return res;
        } catch (e) {
            console.error("[ExternalMemberRepository] getClubs failed:", e);
            throw e;
        }
    }

    async getNotificationStatsForCampaign(campaignId: string, campaignIDText?: string | null) {
        try {
            let query = this.db.selectFrom("notifications")
                .select([
                    sql<number>`count(case when _status = 'unseen' then 1 end)`.as("unseen_count"),
                    sql<number>`count(case when _status = 'seen' then 1 end)`.as("seen_count"),
                    sql<number>`count(id)`.as("total_count")
                ]);

            if (campaignIDText) {
                query = query.where((eb: any) => eb.or([
                    eb(sql`additional->>'campaign_id'`, 'in', [campaignId, campaignIDText]),
                    eb(sql`additional->>'campaignID'`, 'in', [campaignId, campaignIDText])
                ]));
            } else {
                query = query.where((eb: any) => eb.or([
                    eb(sql`additional->>'campaign_id'`, '=', campaignId),
                    eb(sql`additional->>'campaignID'`, '=', campaignId)
                ]));
            }

            const result = await query.executeTakeFirst();
            return {
                unseen: Number(result?.unseen_count || 0),
                seen: Number(result?.seen_count || 0),
                total: Number(result?.total_count || 0)
            };
        } catch (e) {
            console.error(`[ExternalMemberRepository] getNotificationStatsForCampaign failed:`, e);
            return { unseen: 0, seen: 0, total: 0 };
        }
    }

    async getNotificationStatsForCampaigns(campaigns: Array<{ id: string; campaignID?: string | null }>) {
        if (campaigns.length === 0) {
            return {};
        }

        try {
            let query = this.db.selectFrom("notifications")
                .select([
                    sql<string | null>`additional->>'campaign_id'`.as("campaign_id_field"),
                    sql<string | null>`additional->>'campaignID'`.as("campaign_id_text_field"),
                    sql<number>`count(case when _status = 'unseen' then 1 end)`.as("unseen_count"),
                    sql<number>`count(case when _status = 'seen' then 1 end)`.as("seen_count"),
                    sql<number>`count(id)`.as("total_count")
                ])
                .groupBy([
                    sql`additional->>'campaign_id'`,
                    sql`additional`
                ]);

            const allIdsSet = new Set<string>();
            for (const c of campaigns) {
                if (c.id) allIdsSet.add(c.id);
                if (c.campaignID) allIdsSet.add(c.campaignID);
            }
            const allIds = Array.from(allIdsSet);

            if (allIds.length > 0) {
                query = query.where((eb: any) => eb.or([
                    eb(sql`additional->>'campaign_id'`, 'in', allIds),
                    eb(sql`additional->>'campaignID'`, 'in', allIds)
                ]));
            } else {
                return {};
            }

            // Explicitly group by the JSONB keys extracted
            query = this.db.selectFrom("notifications")
                .select([
                    sql<string | null>`additional->>'campaign_id'`.as("campaign_id_field"),
                    sql<string | null>`additional->>'campaignID'`.as("campaign_id_text_field"),
                    sql<number>`count(case when _status = 'unseen' then 1 end)`.as("unseen_count"),
                    sql<number>`count(case when _status = 'seen' then 1 end)`.as("seen_count"),
                    sql<number>`count(id)`.as("total_count")
                ])
                .where((eb: any) => eb.or([
                    eb(sql`additional->>'campaign_id'`, 'in', allIds),
                    eb(sql`additional->>'campaignID'`, 'in', allIds)
                ]))
                .groupBy([
                    sql`additional->>'campaign_id'`,
                    sql`additional->>'campaignID'`
                ]);

            const rows = await query.execute();
            const result: Record<string, { unseen: number; seen: number; total: number }> = {};

            for (const c of campaigns) {
                let unseen = 0;
                let seen = 0;
                let total = 0;

                for (const row of rows) {
                    const matchesCampaign =
                        (row.campaign_id_field && (row.campaign_id_field === c.id || row.campaign_id_field === c.campaignID)) ||
                        (row.campaign_id_text_field && (row.campaign_id_text_field === c.id || row.campaign_id_text_field === c.campaignID));

                    if (matchesCampaign) {
                        unseen += Number(row.unseen_count || 0);
                        seen += Number(row.seen_count || 0);
                        total += Number(row.total_count || 0);
                    }
                }

                result[c.id] = { unseen, seen, total };
            }

            return result;
        } catch (e) {
            console.error(`[ExternalMemberRepository] getNotificationStatsForCampaigns failed:`, e);
            const fallback: Record<string, { unseen: number; seen: number; total: number }> = {};
            for (const c of campaigns) {
                fallback[c.id] = { unseen: 0, seen: 0, total: 0 };
            }
            return fallback;
        }
    }


    async getDemographicsForEmails(emails: string[]) {
        if (emails.length === 0) {
            return {
                countries: [],
                accountTypes: [],
                statuses: []
            };
        }

        const countryCounts: Record<string, number> = {};
        const typeCounts: Record<string, number> = {};
        const statusCounts: Record<string, number> = {};

        const chunkSize = 5000;
        for (let i = 0; i < emails.length; i += chunkSize) {
            const chunk = emails.slice(i, i + chunkSize);
            try {
                const rows = await this.db.selectFrom("users")
                    .innerJoin("members", "members.user_id", "users.id")
                    .leftJoin("member_profiles", "member_profiles.member_id", "members.id")
                    .leftJoin("member_account_types", "member_account_types.id", "members.membership_type_id")
                    .leftJoin("member_account_statuses", "member_account_statuses.id", "members.membership_status_id")
                    .select([
                        "member_profiles.country",
                        "member_account_types.name as account_type",
                        "member_account_statuses.name as account_status"
                    ])
                    .distinctOn("users.id")
                    .orderBy("users.id")
                    .orderBy("members.created_at", "desc")
                    .where("users.email", "in", chunk)
                    .execute();

                rows.forEach(r => {
                    const country = r.country || "Unknown";
                    const type = r.account_type || "Unknown";
                    const status = r.account_status || "Unknown";

                    countryCounts[country] = (countryCounts[country] || 0) + 1;
                    typeCounts[type] = (typeCounts[type] || 0) + 1;
                    statusCounts[status] = (statusCounts[status] || 0) + 1;
                });
            } catch (err) {
                console.error(`[ExternalMemberRepository] Error fetching demographics chunk:`, err);
            }
        }

        return {
            countries: Object.entries(countryCounts).map(([name, value]) => ({ name, value })),
            accountTypes: Object.entries(typeCounts).map(([name, value]) => ({ name, value })),
            statuses: Object.entries(statusCounts).map(([name, value]) => ({ name, value }))
        };
    }

    /**
     * Returns a map of email => seen_status ("seen" | "unseen") for a given campaign.
     * Queries the external (Updot) DB which owns the users + notifications tables.
     */
    async getSeenStatusByEmails(campaignId: string, emails: string[]): Promise<Map<string, string>> {
        const result = new Map<string, string>();
        if (!emails || emails.length === 0) return result;

        const chunkSize = 500;
        for (let i = 0; i < emails.length; i += chunkSize) {
            const chunk = emails.slice(i, i + chunkSize);
            try {
                const rows = await this.db
                    .selectFrom("users")
                    .innerJoin("notifications", "notifications.user_id", "users.id")
                    .select(["users.email", "notifications._status as seen_status"])
                    .where("users.email", "in", chunk)
                    .where(sql<boolean>`(notifications.additional::jsonb ->> 'campaign_id' = ${campaignId} OR notifications.additional::jsonb ->> 'campaignID' = ${campaignId})`)
                    .execute();

                rows.forEach((r: any) => {
                    if (r.email) {
                        result.set(r.email, r.seen_status || "unseen");
                    }
                });
            } catch (err) {
                console.error(`[ExternalMemberRepository] Error fetching seen status chunk:`, err);
            }
        }

        return result;
    }
}