import {
	sqliteTable,
	text,
	integer,
	real,
	primaryKey,
	foreignKey,
} from 'drizzle-orm/sqlite-core';

export const categories = sqliteTable('categories', {
	id: integer('id').primaryKey({ autoIncrement: true }),
	name: text('name').notNull().unique(),
	order: integer('order').notNull().default(0),
});

export const groceries = sqliteTable('groceries', {
	id: integer('id').primaryKey({ autoIncrement: true }),
	name: text('name').notNull().unique(),
	checked: integer('checked', { mode: 'boolean' }).notNull().default(false),
	createdAt: text('created_at').notNull(),
	categoryId: integer('category_id').references(() => categories.id, {
		onDelete: 'set null',
	}),
});

export const references = sqliteTable('references', {
	id: integer('id').primaryKey({ autoIncrement: true }),
	groceryId: integer('grocery_id')
		.notNull()
		.references(() => groceries.id, { onDelete: 'cascade' }),
	reference: text('reference').notNull(),
	price: real('price'),
	unit: text('unit'),
	qualityScore: integer('quality_score'),
});

export const projects = sqliteTable('projects', {
	id: integer('id').primaryKey({ autoIncrement: true }),
	name: text('name').notNull().unique(),
	archived: integer('archived', { mode: 'boolean' }).notNull().default(false),
});

// Append-only snapshot table: an edit inserts a new row with version+1, the
// current routine is the row with the max version for its id.
export const routines = sqliteTable(
	'routines',
	{
		id: integer('id').notNull(),
		version: integer('version').notNull(),
		projectId: integer('project_id')
			.notNull()
			.references(() => projects.id, { onDelete: 'cascade' }),
		name: text('name').notNull(),
		frequencyDays: integer('frequency_days').notNull(),
		archived: integer('archived', { mode: 'boolean' }).notNull().default(false),
		createdAt: text('created_at').notNull(),
	},
	(t) => [primaryKey({ columns: [t.id, t.version] })],
);

export const routineCompletions = sqliteTable(
	'routine_completions',
	{
		id: integer('id').primaryKey({ autoIncrement: true }),
		routineId: integer('routine_id').notNull(),
		routineVersion: integer('routine_version').notNull(),
		doneAt: text('done_at').notNull(),
		note: text('note'),
	},
	(t) => [
		foreignKey({
			columns: [t.routineId, t.routineVersion],
			foreignColumns: [routines.id, routines.version],
		}).onDelete('cascade'),
	],
);