import { db } from './db';
import { feedCache } from './db/schema';
import { and, eq, lt, desc, or, sql } from 'drizzle-orm';
import type { PostData } from '$lib/types/content-blocks';

/**
 * Upsert multiple posts into the cache for a user.
 */
export async function upsertFeedCache(userDid: string, posts: PostData[]) {
	const now = new Date();
	const values = posts.map((post) => {
		const cachedPost = { ...post, _cachedAt: now.toISOString() };
		return {
			userDid,
			postUri: post.uri,
			postData: JSON.stringify(cachedPost),
			createdAt: new Date(post.createdAt),
			indexedAt: now
		};
	});

	if (values.length === 0) return;

	// better-sqlite3 transactions are synchronous
	db.transaction((tx) => {
		for (const val of values) {
			tx.insert(feedCache)
				.values(val)
				.onConflictDoUpdate({
					target: [feedCache.userDid, feedCache.postUri],
					set: {
						postData: val.postData,
						indexedAt: val.indexedAt
					}
				})
				.run();
		}
	});
}

/**
 * Get cached feed posts for a user.
 */
export async function getCachedFeed(
    userDid: string,
    limit = 25,
    cursor?: string
): Promise<PostData[]> {
    let query = db
        .select()
        .from(feedCache)
        .where(eq(feedCache.userDid, userDid))
        .orderBy(desc(feedCache.createdAt))
        .limit(limit);

    if (cursor) {
        // Composite cursor: "timestamp::postUri" for stable keyset pagination
        const parts = cursor.split('::');
        const cursorTime = new Date(parts[0]);
        if (isNaN(cursorTime.getTime())) return [];
        const cursorUri = parts[1];

        query = db
            .select()
            .from(feedCache)
            .where(
                and(
                    eq(feedCache.userDid, userDid),
                    cursorUri
                        ? or(
                            lt(feedCache.createdAt, cursorTime),
                            and(eq(feedCache.createdAt, cursorTime), sql`${feedCache.postUri} < ${cursorUri}`)
                        )
                        : lt(feedCache.createdAt, cursorTime)
                )
            )
            .orderBy(desc(feedCache.createdAt))
            .limit(limit);
    }

    const results = await query;
    return results.map((r) => {
		const post = JSON.parse(r.postData) as PostData;
		// Ensure _cachedAt is present if it was missing from older cache entries
		if (!(post as any)._cachedAt) {
			(post as any)._cachedAt = r.indexedAt.toISOString();
		}
		return post;
	});
}

/**
 * Clear old feed cache entries (older than 3 days).
 */
export async function clearOldFeedCache() {
    const threeDaysAgo = new Date(Date.now() - 3 * 24 * 60 * 60 * 1000);
    await db.delete(feedCache).where(lt(feedCache.createdAt, threeDaysAgo));
}

/**
 * Remove a specific post from the feed cache.
 */
export async function deleteFromFeedCache(postUri: string) {
    await db.delete(feedCache).where(eq(feedCache.postUri, postUri));
}