/**
 * Set up FTS5 full-text search tables and triggers
 * Run with: pnpm db:fts
 */

import { createClient } from '@libsql/client';

const CONTENT_DB_PATH = process.env.CONTENT_DB_PATH || './data/content.db';

// FTS setup statements - each is a complete statement
const statements = [
	// Posts FTS table
	`CREATE VIRTUAL TABLE IF NOT EXISTS posts_fts USING fts5(
		uri UNINDEXED,
		title,
		text,
		url,
		content='posts',
		content_rowid='rowid'
	)`,

	// Posts triggers
	`CREATE TRIGGER IF NOT EXISTS posts_fts_insert AFTER INSERT ON posts BEGIN
		INSERT INTO posts_fts(rowid, uri, title, text, url)
		VALUES (new.rowid, new.uri, new.title, new.text, new.url);
	END`,

	`CREATE TRIGGER IF NOT EXISTS posts_fts_delete AFTER DELETE ON posts BEGIN
		INSERT INTO posts_fts(posts_fts, rowid, uri, title, text, url)
		VALUES ('delete', old.rowid, old.uri, old.title, old.text, old.url);
	END`,

	`CREATE TRIGGER IF NOT EXISTS posts_fts_update AFTER UPDATE ON posts BEGIN
		INSERT INTO posts_fts(posts_fts, rowid, uri, title, text, url)
		VALUES ('delete', old.rowid, old.uri, old.title, old.text, old.url);
		INSERT INTO posts_fts(rowid, uri, title, text, url)
		VALUES (new.rowid, new.uri, new.title, new.text, new.url);
	END`,

	// Comments FTS table
	`CREATE VIRTUAL TABLE IF NOT EXISTS comments_fts USING fts5(
		uri UNINDEXED,
		post_uri UNINDEXED,
		text,
		content='comments',
		content_rowid='rowid'
	)`,

	// Comments triggers
	`CREATE TRIGGER IF NOT EXISTS comments_fts_insert AFTER INSERT ON comments BEGIN
		INSERT INTO comments_fts(rowid, uri, post_uri, text)
		VALUES (new.rowid, new.uri, new.post_uri, new.text);
	END`,

	`CREATE TRIGGER IF NOT EXISTS comments_fts_delete AFTER DELETE ON comments BEGIN
		INSERT INTO comments_fts(comments_fts, rowid, uri, post_uri, text)
		VALUES ('delete', old.rowid, old.uri, old.post_uri, old.text);
	END`,

	`CREATE TRIGGER IF NOT EXISTS comments_fts_update AFTER UPDATE ON comments BEGIN
		INSERT INTO comments_fts(comments_fts, rowid, uri, post_uri, text)
		VALUES ('delete', old.rowid, old.uri, old.post_uri, old.text);
		INSERT INTO comments_fts(rowid, uri, post_uri, text)
		VALUES (new.rowid, new.uri, new.post_uri, new.text);
	END`,

	// Rebuild FTS indexes from existing data
	`INSERT INTO posts_fts(posts_fts) VALUES('rebuild')`,
	`INSERT INTO comments_fts(comments_fts) VALUES('rebuild')`
];

async function setupFTS() {
	console.log(`Setting up FTS5 for ${CONTENT_DB_PATH}...`);

	const client = createClient({
		url: `file:${CONTENT_DB_PATH}`
	});

	for (const sql of statements) {
		const preview = sql.slice(0, 60).replace(/\s+/g, ' ').trim();
		try {
			await client.execute(sql);
			console.log('✓', preview + '...');
		} catch (err) {
			if (err instanceof Error && err.message.includes('already exists')) {
				console.log('⊘', preview + '... (already exists)');
			} else {
				console.error('✗', preview);
				console.error('  Error:', err instanceof Error ? err.message : err);
			}
		}
	}

	console.log('\nFTS5 setup complete!');
	process.exit(0);
}

setupFTS();