import { eq, desc, asc, ne, sql } from 'drizzle-orm';
import { db } from './index';
import {
	entries,
	images,
	tags,
	entryTags,
	lower,
	type Entry,
	type NewEntry,
	type Image,
	type NewImage,
	type Tag
} from './schema';
import { nanoid } from 'nanoid';

export interface EntryRepository {
	create(entry: Omit<NewEntry, 'id' | 'createdAt' | 'updatedAt'>): Entry;
	getById(id: string): Entry | undefined;
	getAll(options?: { limit?: number; offset?: number }): Entry[];
	getOrdered(options?: { showCompleted?: boolean }): Entry[];
	update(id: string, updates: Partial<Omit<Entry, 'id' | 'createdAt'>>): Entry | undefined;
	delete(id: string): boolean;
	count(): number;
}

class SQLiteEntryRepository implements EntryRepository {
	create(entry: Omit<NewEntry, 'id' | 'createdAt' | 'updatedAt'>): Entry {
		const now = new Date().toISOString();
		const newEntry: NewEntry = {
			...entry,
			id: nanoid(),
			createdAt: now,
			updatedAt: now
		};

		db.insert(entries).values(newEntry).run();
		return this.getById(newEntry.id)!;
	}

	getById(id: string): Entry | undefined {
		return db.select().from(entries).where(eq(entries.id, id)).get();
	}

	getAll(options: { limit?: number; offset?: number } = {}): Entry[] {
		const { limit = 100, offset = 0 } = options;
		return db.select().from(entries).orderBy(desc(entries.createdAt)).limit(limit).offset(offset).all();
	}

	getOrdered(options: { showCompleted?: boolean } = {}): Entry[] {
		const { showCompleted = false } = options;

		if (!showCompleted) {
			return db
				.select()
				.from(entries)
				.where(ne(entries.status, 'done'))
				.orderBy(asc(entries.type), desc(entries.createdAt))
				.all();
		}

		return db
			.select()
			.from(entries)
			.orderBy(asc(entries.type), desc(entries.createdAt))
			.all();
	}

	update(id: string, updates: Partial<Omit<Entry, 'id' | 'createdAt'>>): Entry | undefined {
		const existing = this.getById(id);
		if (!existing) return undefined;

		db.update(entries)
			.set({
				...updates,
				updatedAt: new Date().toISOString()
			})
			.where(eq(entries.id, id))
			.run();

		return this.getById(id);
	}

	delete(id: string): boolean {
		const existing = this.getById(id);
		if (!existing) return false;

		db.delete(entries).where(eq(entries.id, id)).run();
		return true;
	}

	count(): number {
		const result = db.select().from(entries).all();
		return result.length;
	}
}

// Singleton instance
export const entryRepository: EntryRepository = new SQLiteEntryRepository();

// Image Repository
export interface ImageRepository {
	create(image: Omit<NewImage, 'id' | 'createdAt'>): Image;
	getById(id: string): Image | undefined;
	getByEntryId(entryId: string): Image[];
	delete(id: string): boolean;
	deleteByEntryId(entryId: string): number;
}

class SQLiteImageRepository implements ImageRepository {
	create(image: Omit<NewImage, 'id' | 'createdAt'>): Image {
		const newImage: NewImage = {
			...image,
			id: nanoid(),
			createdAt: new Date().toISOString()
		};

		db.insert(images).values(newImage).run();
		return this.getById(newImage.id)!;
	}

	getById(id: string): Image | undefined {
		return db.select().from(images).where(eq(images.id, id)).get();
	}

	getByEntryId(entryId: string): Image[] {
		return db
			.select()
			.from(images)
			.where(eq(images.entryId, entryId))
			.orderBy(asc(images.createdAt))
			.all();
	}

	delete(id: string): boolean {
		const existing = this.getById(id);
		if (!existing) return false;

		db.delete(images).where(eq(images.id, id)).run();
		return true;
	}

	deleteByEntryId(entryId: string): number {
		const existing = this.getByEntryId(entryId);
		if (existing.length === 0) return 0;

		db.delete(images).where(eq(images.entryId, entryId)).run();
		return existing.length;
	}
}

export const imageRepository: ImageRepository = new SQLiteImageRepository();

// Tag Repository
export interface TagRepository {
	findOrCreate(name: string): Tag;
	getAll(): Tag[];
	getById(id: string): Tag | undefined;
	getByEntryId(entryId: string): Tag[];
	updateEntryTags(entryId: string, tagNames: string[]): void;
}

class SQLiteTagRepository implements TagRepository {
	findOrCreate(name: string): Tag {
		const normalizedName = name.trim();

		// Try to find existing tag (case-insensitive)
		const existing = db
			.select()
			.from(tags)
			.where(sql`lower(${tags.name}) = lower(${normalizedName})`)
			.get();

		if (existing) return existing;

		// Create new tag
		const newTag: Tag = {
			id: nanoid(),
			name: normalizedName,
			createdAt: new Date().toISOString()
		};
		db.insert(tags).values(newTag).run();
		return newTag;
	}

	getAll(): Tag[] {
		return db.select().from(tags).orderBy(asc(tags.name)).all();
	}

	getById(id: string): Tag | undefined {
		return db.select().from(tags).where(eq(tags.id, id)).get();
	}

	getByEntryId(entryId: string): Tag[] {
		return db
			.select({
				id: tags.id,
				name: tags.name,
				createdAt: tags.createdAt
			})
			.from(entryTags)
			.innerJoin(tags, eq(entryTags.tagId, tags.id))
			.where(eq(entryTags.entryId, entryId))
			.orderBy(asc(tags.name))
			.all();
	}

	updateEntryTags(entryId: string, tagNames: string[]): void {
		// Delete existing associations
		db.delete(entryTags).where(eq(entryTags.entryId, entryId)).run();

		// Find or create each tag and create associations
		for (const name of tagNames) {
			const tag = this.findOrCreate(name);
			db.insert(entryTags).values({ entryId, tagId: tag.id }).run();
		}
	}
}

export const tagRepository: TagRepository = new SQLiteTagRepository();
