import type { DB } from "@/internal/datastore/db";
import type { Kysely } from "kysely";
import { sql } from "kysely";

/** Statuses a campaign can never be picked up from again. */
const TERMINAL_STATUSES = ["SENT", "COMPLETED", "FAILED", "DELETED"];

/**
 * Key for the transaction-scoped advisory lock that serialises SENDING claims.
 * Arbitrary but must be stable across every process talking to this database,
 * otherwise two workers can read the same free slot and both take it.
 */
const SENDING_SLOT_LOCK_KEY = 728291001;

export type CampaignClaimResult =
    | { claimed: true; campaign: any; previousStatus: string; activeCount: number }
    | { claimed: false; reason: "not_found" | "terminal" | "already_sending" | "no_slot"; campaign?: any; activeCount?: number };

export class CampaignRepository {
    private db: Kysely<DB>;

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

    async create(data: {
        name: string;
        title: string;
        body: string;
        image_url?: string | null;
        target_criteria: any;
        additional_data?: any;
        status?: string;
        scheduled_at?: string | null;
        total_count?: number;
        published_by?: string | null;
    }) {
        return await this.db
            .insertInto("campaigns")
            .values({
                name: data.name,
                title: data.title,
                body: data.body,
                image_url: data.image_url,
                status: data.status || "DRAFT",
                target_criteria: JSON.stringify(data.target_criteria),
                additional_data: JSON.stringify(data.additional_data || {}),
                scheduled_at: data.scheduled_at ? new Date(data.scheduled_at) : null,
                total_count: data.total_count || 0,
                published_by: data.published_by || null,
            })
            .returningAll()
            .executeTakeFirst();
    }

    async update(id: string, data: Partial<{
        name: string;
        title: string;
        body: string;
        image_url?: string | null;
        target_criteria: any;
        additional_data?: any;
        campaignID?: string | null;
        status?: string;
        scheduled_at?: string | Date | null;
        total_count?: number;
        published_by?: string | null;
    }>) {
        const updateData: any = {
            updated_at: new Date()
        };

        if (data.name !== undefined)
            updateData.name = data.name;
        if (data.title !== undefined)
            updateData.title = data.title;
        if (data.body !== undefined)
            updateData.body = data.body;
        if (data.image_url !== undefined)
            updateData.image_url = data.image_url;
        if (data.status !== undefined)
            updateData.status = data.status;
        if (data.target_criteria !== undefined)
            updateData.target_criteria = JSON.stringify(data.target_criteria);
        if (data.additional_data !== undefined)
            updateData.additional_data = JSON.stringify(data.additional_data);
        if (data.campaignID !== undefined)
            updateData.campaignID = data.campaignID;
        if (data.scheduled_at !== undefined)
            updateData.scheduled_at = data.scheduled_at ? new Date(data.scheduled_at) : null;
        if (data.total_count !== undefined)
            updateData.total_count = data.total_count;
        if (data.published_by !== undefined)
            updateData.published_by = data.published_by;

        return await this.db
            .updateTable("campaigns")
            .set(updateData)
            .where("id", "=", id)
            .returningAll()
            .executeTakeFirst();
    }

    async getById(id: string) {
        return await this.db
            .selectFrom("campaigns")
            .selectAll()
            .where("id", "=", id)
            .executeTakeFirst();
    }

    async updateStatus(id: string, status: string) {
        return await this.db
            .updateTable("campaigns")
            .set({ status, updated_at: new Date() })
            .where("id", "=", id)
            .returningAll()
            .executeTakeFirst();
    }

    async logSystemActivity(campaignId: string, action: string, details: any) {
        return await this.db
            .insertInto("admin_activity_log" as any)
            .values({
                admin_email: process.env.UPDOT_MEMBER_CORE_EMAIL || "system@karma.com",
                action: action,
                method: "BACKGROUND",
                url: `/v1/admin-console/notifications/campaigns/${campaignId}`,
                status_code: 200,
                log_category: "SYSTEM",
                payload: JSON.stringify(details),
                metadata: JSON.stringify({ entityId: campaignId }),
                performed_at: new Date()
            })
            .execute();
    }

    async getScheduledPending() {
        return await this.db
            .updateTable("campaigns")
            .set({ status: "QUEUED", updated_at: new Date() })
            .where("status", "=", "SCHEDULED")
            .where((eb) => eb.or([
                eb("scheduled_at", "<=", new Date()),
                eb("scheduled_at", "is", null)
            ]))
            .returningAll()
            .execute();
    }

    async list(limit: number = 50, offset: number = 0, filters?: { status?: string, search?: string, country?: string, city?: string, platform?: string, fromDate?: string; toDate?: string; sortBy?: string; sortOrder?: 'asc' | 'desc'; }) {
        // Platform lives inside target_criteria.userSegment. A campaign with no
        // userSegment (or a malformed one) targets every platform, so it matches
        // any filter — same rule the table used to apply client-side. Built once
        // and applied to both the page query and the count query so the two can't
        // disagree about what's being filtered.
        const platformFilter = (() => {
            const wanted = (filters?.platform ?? "")
                .split(",")
                .map((p) => p.trim().toLowerCase())
                .filter(Boolean);
            if (wanted.length === 0) return null;
            return sql<boolean>`(
                jsonb_typeof(target_criteria->'userSegment') IS DISTINCT FROM 'array'
                OR jsonb_array_length(target_criteria->'userSegment') = 0
                OR EXISTS (
                    SELECT 1 FROM jsonb_array_elements_text(target_criteria->'userSegment') AS seg
                    WHERE lower(seg) = ANY(${wanted}::text[])
                )
            )`;
        })();

        let query = this.db.selectFrom("campaigns")
            .selectAll();

        if (filters?.sortBy === "start") {
            query = query.orderBy(sql<string>`COALESCE(scheduled_at, created_at)`, filters.sortOrder || "desc");
        } else if (filters?.sortBy === "end" || filters?.sortBy === "updated_at") {
            query = query.orderBy("updated_at", filters.sortOrder || "desc");
        } else if (filters?.sortBy === "status") {
            query = query.orderBy("status", filters.sortOrder || "desc");
        } else {
            query = query.orderBy("created_at", "desc");
        }

        if (filters?.status) {
            const statuses = filters.status.split(",");
            if (statuses.length === 1) {
                query = query.where("status", "=", statuses[0]);
            } else {
                query = query.where("status", "in", statuses);
            }
        } else {
            query = query.where("status", "!=", "DELETED");
        }

        if (filters?.search) {
            query = query.where((eb) => eb.or([
                eb("title", "ilike", `%${filters.search}%`),
                eb("name", "ilike", `%${filters.search}%`)
            ]));
        }

        if (filters?.country) {
            query = query.where(sql<boolean>`target_criteria @> ${JSON.stringify({ country: [filters.country] })}::jsonb`);
        }

        if (filters?.city) {
            query = query.where(sql<boolean>`target_criteria @> ${JSON.stringify({ city: [filters.city] })}::jsonb`);
        }

        if (platformFilter) {
            query = query.where(platformFilter);
        }

        if (filters?.fromDate) {
            const startIso = `${filters.fromDate.split('T')[0]}T00:00:00.000Z`;
            query = query.where(sql<boolean>`COALESCE(scheduled_at, created_at) >= ${new Date(startIso)}`);
        }

        if (filters?.toDate) {
            const endIso = `${filters.toDate.split('T')[0]}T23:59:59.999Z`;
            query = query.where(sql<boolean>`COALESCE(scheduled_at, created_at) <= ${new Date(endIso)}`);
        }

        const items = await query
            .limit(limit)
            .offset(offset)
            .execute();

        let countQuery = this.db.selectFrom("campaigns")
            .select(this.db.fn.count<number>("id").as("total"));

        if (filters?.status) {
            const statuses = filters.status.split(",");
            if (statuses.length === 1) {
                countQuery = countQuery.where("status", "=", statuses[0]);
            } else {
                countQuery = countQuery.where("status", "in", statuses);
            }
        } else {
            countQuery = countQuery.where("status", "!=", "DELETED");
        }

        if (filters?.search) {
            countQuery = countQuery.where((eb) => eb.or([
                eb("title", "ilike", `%${filters.search}%`),
                eb("name", "ilike", `%${filters.search}%`)
            ]));
        }

        if (filters?.country) {
            countQuery = countQuery.where(sql<boolean>`target_criteria @> ${JSON.stringify({ country: [filters.country] })}::jsonb`);
        }

        if (filters?.city) {
            countQuery = countQuery.where(sql<boolean>`target_criteria @> ${JSON.stringify({ city: [filters.city] })}::jsonb`);
        }

        if (platformFilter) {
            countQuery = countQuery.where(platformFilter);
        }

        if (filters?.fromDate) {
            const startIso = `${filters.fromDate.split('T')[0]}T00:00:00.000Z`;
            countQuery = countQuery.where(sql<boolean>`COALESCE(scheduled_at, created_at) >= ${new Date(startIso)}`);
        }

        if (filters?.toDate) {
            const endIso = `${filters.toDate.split('T')[0]}T23:59:59.999Z`;
            countQuery = countQuery.where(sql<boolean>`COALESCE(scheduled_at, created_at) <= ${new Date(endIso)}`);
        }

        const totalResult = await countQuery.executeTakeFirst();
        const total = Number(totalResult?.total || 0);

        return { items, total };
    }

    async delete(id: string) {
        return await this.db
            .updateTable("campaigns")
            .set({ status: "DELETED", updated_at: new Date() })
            .where(sql<boolean>`id = ${id}::uuid`)
            .returningAll()
            .executeTakeFirst();
    }

    // Unpaginated fetch scoped to a date window — used for graph/summary
    // aggregation so results aren't truncated by table pagination.
    async listForAnalytics(range: { fromDate: Date; toDate: Date }) {
        return await this.db
            .selectFrom("campaigns")
            .select(["id", "campaignID", "sent_count", "scheduled_at", "created_at"])
            .where((eb) => eb.or([
                eb("status", "=", "SENT"),
                eb("status", "=", "COMPLETED"),
            ]))
            .where(sql<boolean>`COALESCE(scheduled_at, created_at) >= ${range.fromDate}`)
            .where(sql<boolean>`COALESCE(scheduled_at, created_at) <= ${range.toDate}`)
            .execute();
    }

    async incrementSentCount(id: string, count: number) {
        return await this.db
            .updateTable("campaigns")
            .set({
                sent_count: sql`COALESCE(sent_count, 0) + ${count}`,
                updated_at: new Date()
            })
            .where(sql<boolean>`id = ${id}::uuid`)
            .returningAll()
            .executeTakeFirst();
    }

    async logRecipients(campaignId: string, recipients: { email: string, status: string }[]) {
        if (recipients.length === 0)
            return;

        const values = recipients.map(r => ({
            campaign_id: campaignId,
            email: r.email,
            status: r.status || "SENT",
            sent_at: new Date()
        }));

        return await this.db
            .insertInto("campaign_recipients" as any)
            .values(values)
            .execute();
    }

    /**
     * Atomically take one of the concurrent SENDING slots for a campaign.
     *
     * The slot check and the status flip happen inside one transaction behind an
     * advisory lock, so N workers racing on the last free slot produce exactly one
     * winner. A campaign already SENDING is refused, which is what makes a RabbitMQ
     * redelivery (or a duplicate enqueue) safe: the second consumer never starts a
     * parallel run of the same campaign and cannot double-send its batches.
     */
    async claimSendingSlot(id: string, maxConcurrent: number): Promise<CampaignClaimResult> {
        return await this.db.transaction().execute(async (trx) => {
            await sql`SELECT pg_advisory_xact_lock(${SENDING_SLOT_LOCK_KEY})`.execute(trx);

            const campaign = await trx
                .selectFrom("campaigns")
                .selectAll()
                .where("id", "=", id)
                .executeTakeFirst();

            if (!campaign) return { claimed: false as const, reason: "not_found" as const };
            if (TERMINAL_STATUSES.includes(campaign.status)) {
                return { claimed: false as const, reason: "terminal" as const, campaign };
            }
            if (campaign.status === "SENDING") {
                return { claimed: false as const, reason: "already_sending" as const, campaign };
            }

            const active = await trx
                .selectFrom("campaigns")
                .select(trx.fn.count<number>("id").as("total"))
                .where("status", "=", "SENDING")
                .executeTakeFirst();
            const activeCount = Number(active?.total || 0);

            if (activeCount >= maxConcurrent) {
                return { claimed: false as const, reason: "no_slot" as const, campaign, activeCount };
            }

            const updated = await trx
                .updateTable("campaigns")
                .set({ status: "SENDING", updated_at: new Date() })
                .where("id", "=", id)
                .returningAll()
                .executeTakeFirst();

            return {
                claimed: true as const,
                campaign: updated ?? campaign,
                previousStatus: campaign.status,
                activeCount: activeCount + 1,
            };
        });
    }

    async countSendingCampaigns() {
        const result = await this.db
            .selectFrom("campaigns")
            .select(this.db.fn.count<number>("id").as("total"))
            .where("status", "=", "SENDING")
            .executeTakeFirst();
        return Number(result?.total || 0);
    }

    async hasOtherSendingCampaign(currentId: string) {
        const result = await this.db
            .selectFrom("campaigns")
            .select(this.db.fn.count<number>("id").as("total"))
            .where("status", "=", "SENDING")
            .where(sql<boolean>`id::uuid != ${currentId}::uuid`)
            .executeTakeFirst();
        return Number(result?.total || 0) > 0;
    }

    async setTotalCount(id: string, count: number) {
        return await this.db
            .updateTable("campaigns")
            .set({
                total_count: count,
                updated_at: new Date()
            })
            .where(sql<boolean>`id = ${id}::uuid`)
            .execute();
    }

    async resetSendingCampaigns() {
        return await this.db
            .updateTable("campaigns")
            .set({
                status: "SCHEDULED",
                updated_at: new Date()
            })
            .where((eb) => eb.or([
                eb("status", "=", "SENDING"),
                eb("status", "=", "QUEUED"),
            ]))
            .execute();
    }

    async getRecipients(campaignId: string, limit: number = 50, offset: number = 0, search?: string, status?: string) {
        let query = this.db
            .selectFrom("campaign_recipients" as any)
            .selectAll()
            .where("campaign_id", "=", campaignId);

        if (search) {
            query = query.where("email", "ilike", `%${search}%`);
        }

        if (status) {
            query = query.where("status", "=", status);
        }

        const items = await query
            .limit(limit)
            .offset(offset)
            .orderBy("sent_at", "desc")
            .execute();

        let countQuery = this.db.selectFrom("campaign_recipients" as any)
            .select(this.db.fn.count<number>("id").as("total"))
            .where("campaign_id", "=", campaignId);

        if (search) {
            countQuery = countQuery.where("email", "ilike", `%${search}%`);
        }

        if (status) {
            countQuery = countQuery.where("status", "=", status);
        }

        const totalResult = await countQuery.executeTakeFirst();
        const total = Number(totalResult?.total || 0);

        return { items, total };
    }
}