import { sqliteTable, text, integer, uniqueIndex, primaryKey } from 'drizzle-orm/sqlite-core';
import { sql, type SQL } from 'drizzle-orm';
import type { AnySQLiteColumn } from 'drizzle-orm/sqlite-core';

// Helper function for case-insensitive indexing
export function lower(column: AnySQLiteColumn): SQL {
	return sql`lower(${column})`;
}

// Entry types: 'task' (actionable) or 'thought' (reference)
export const entries = sqliteTable('entries', {
	id: text('id').primaryKey(), // nanoid
	title: text('title'),
	content: text('content').notNull(),
	type: text('type', { enum: ['task', 'thought'] }).notNull().default('thought'),
	status: text('status', { enum: ['open', 'done', 'archived'] }).default('open'), // for tasks
	pinned: integer('pinned', { mode: 'boolean' }).default(false),
	dueDate: text('due_date'), // ISO date string, nullable
	createdAt: text('created_at')
		.notNull()
		.$defaultFn(() => new Date().toISOString()),
	updatedAt: text('updated_at')
		.notNull()
		.$defaultFn(() => new Date().toISOString())
});

// Type inference for TypeScript
export type Entry = typeof entries.$inferSelect;
export type NewEntry = typeof entries.$inferInsert;

// Images table: attachments linked to entries
export const images = sqliteTable('images', {
	id: text('id').primaryKey(), // nanoid
	entryId: text('entry_id')
		.notNull()
		.references(() => entries.id, { onDelete: 'cascade' }),
	filename: text('filename').notNull(), // original filename for display
	ext: text('ext').notNull(), // file extension without dot: jpg, png, etc.
	createdAt: text('created_at')
		.notNull()
		.$defaultFn(() => new Date().toISOString())
});

export type Image = typeof images.$inferSelect;
export type NewImage = typeof images.$inferInsert;

// Tags table: reusable tags for organizing entries
export const tags = sqliteTable(
	'tags',
	{
		id: text('id').primaryKey(), // nanoid
		name: text('name').notNull(),
		createdAt: text('created_at')
			.notNull()
			.$defaultFn(() => new Date().toISOString())
	},
	(table) => [uniqueIndex('tagNameUniqueIndex').on(lower(table.name))]
);

export type Tag = typeof tags.$inferSelect;
export type NewTag = typeof tags.$inferInsert;

// Junction table for many-to-many entry-tag relationship
export const entryTags = sqliteTable(
	'entry_tags',
	{
		entryId: text('entry_id')
			.notNull()
			.references(() => entries.id, { onDelete: 'cascade' }),
		tagId: text('tag_id')
			.notNull()
			.references(() => tags.id, { onDelete: 'cascade' })
	},
	(t) => [primaryKey({ columns: [t.entryId, t.tagId] })]
);

export type EntryTag = typeof entryTags.$inferSelect;
