import Database from 'better-sqlite3';
import { dirname, join } from 'path';
import { fileURLToPath } from 'url';

const __dirname = dirname(fileURLToPath(import.meta.url));
const DB_FILE = process.env.DB_PATH || join(__dirname, 'recipes.db');

const db = new Database(DB_FILE);

db.pragma('journal_mode = WAL');

export interface User {
  id: number;
  username: string;
  password?: string;
  is_admin: number;
  created_at: string;
}

export interface Recipe {
  id: number;
  name: string;
  description: string;
  ingredients: string;
  steps: string;
  category: string;
  source: string;
  source_url: string | null;
  source_author: string | null;
  tiktok_url: string | null;
  tiktok_author: string | null;
  thumbnail_url: string | null;
  user_id: number | null;
  created_at: string;
  updated_at: string;
}

export interface InviteToken {
  id: number;
  token: string;
  created_by: number;
  used_by: number | null;
  created_at: string;
  used_at: string | null;
  used_by_username?: string | null;
}

function now(): string {
  return new Date().toISOString().replace('T', ' ').slice(0, 19);
}

export function getDb() {
  return db;
}

export function getAllRecipes(search?: string, category?: string, userId?: number | string): Recipe[] {
  let query = 'SELECT * FROM recipes WHERE 1=1';
  const params: (string | number)[] = [];

  if (search) {
    query += ' AND (LOWER(name) LIKE ? OR LOWER(description) LIKE ? OR LOWER(ingredients) LIKE ?)';
    const term = `%${search.toLowerCase()}%`;
    params.push(term, term, term);
  }

  if (category && category !== 'Alle') {
    query += ' AND category = ?';
    params.push(category);
  }

  if (userId) {
    query += ' AND user_id = ?';
    params.push(Number(userId));
  }

  query += ' ORDER BY created_at DESC';

  return db.prepare(query).all(...params) as Recipe[];
}

export function getRecipeById(id: number | string): Recipe | undefined {
  return db.prepare('SELECT * FROM recipes WHERE id = ?').get(Number(id)) as Recipe | undefined;
}

export function createRecipe(recipe: Partial<Recipe> & { ingredients?: any; steps?: any }): Recipe {
  const nowStr = now();
  const stmt = db.prepare(`
    INSERT INTO recipes (
      name, description, ingredients, steps, category,
      source, source_url, source_author, tiktok_url, tiktok_author,
      thumbnail_url, user_id, created_at, updated_at
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `);

  const info = stmt.run(
    recipe.name,
    recipe.description || '',
    typeof recipe.ingredients === 'string' ? recipe.ingredients : JSON.stringify(recipe.ingredients || []),
    typeof recipe.steps === 'string' ? recipe.steps : JSON.stringify(recipe.steps || []),
    recipe.category || 'Sonstiges',
    recipe.source || 'tiktok',
    recipe.source_url || recipe.tiktok_url || null,
    recipe.source_author || recipe.tiktok_author || null,
    recipe.tiktok_url || recipe.source_url || null,
    recipe.tiktok_author || recipe.source_author || null,
    recipe.thumbnail_url || null,
    recipe.user_id || null,
    nowStr,
    nowStr
  );

  return getRecipeById(info.lastInsertRowid as number)!;
}

export function updateRecipe(id: number | string, recipe: Partial<Recipe> & { ingredients?: any; steps?: any }): Recipe | undefined {
  const existing = getRecipeById(id);
  if (!existing) return undefined;

  const nowStr = now();
  const stmt = db.prepare(`
    UPDATE recipes SET
      name = ?, description = ?, ingredients = ?, steps = ?, category = ?,
      source = ?, source_url = ?, source_author = ?, tiktok_url = ?, tiktok_author = ?,
      thumbnail_url = ?, updated_at = ?
    WHERE id = ?
  `);

  stmt.run(
    recipe.name !== undefined ? recipe.name : existing.name,
    recipe.description !== undefined ? recipe.description : existing.description,
    recipe.ingredients !== undefined ? (typeof recipe.ingredients === 'string' ? recipe.ingredients : JSON.stringify(recipe.ingredients)) : existing.ingredients,
    recipe.steps !== undefined ? (typeof recipe.steps === 'string' ? recipe.steps : JSON.stringify(recipe.steps)) : existing.steps,
    recipe.category || existing.category,
    recipe.source || existing.source,
    recipe.source_url || recipe.tiktok_url || existing.source_url,
    recipe.source_author || recipe.tiktok_author || existing.source_author,
    recipe.tiktok_url || recipe.source_url || existing.tiktok_url,
    recipe.tiktok_author || recipe.source_author || existing.tiktok_author,
    recipe.thumbnail_url !== undefined ? recipe.thumbnail_url : existing.thumbnail_url,
    nowStr,
    Number(id)
  );

  return getRecipeById(id);
}

export function deleteRecipe(id: number | string): void {
  db.prepare('DELETE FROM recipes WHERE id = ?').run(Number(id));
}

export function getCategories(): { category: string; count: number }[] {
  return db.prepare(`
    SELECT category, COUNT(*) as count
    FROM recipes
    GROUP BY category
    ORDER BY count DESC
  `).all() as { category: string; count: number }[];
}

export function getUserByUsername(username: string): User | undefined {
  return db.prepare('SELECT * FROM users WHERE username = ?').get(username) as User | undefined;
}

export function getUserById(id: number | string): User | undefined {
  return db.prepare('SELECT * FROM users WHERE id = ?').get(Number(id)) as User | undefined;
}

export function createUser(username: string, hashedPassword: string, isAdmin: boolean = false): User {
  const nowStr = now();
  const stmt = db.prepare('INSERT INTO users (username, password, is_admin, created_at) VALUES (?, ?, ?, ?)');
  const info = stmt.run(username, hashedPassword, isAdmin ? 1 : 0, nowStr);

  return { id: info.lastInsertRowid as number, username, is_admin: isAdmin ? 1 : 0, created_at: nowStr };
}

export function updateUserCredentials(userId: number | string, { username, password }: { username?: string; password?: string }): User | null {
  const user = getUserById(userId);
  if (!user) return null;

  let query = 'UPDATE users SET ';
  const params: (string | number)[] = [];
  const updates: string[] = [];

  if (username) {
    updates.push('username = ?');
    params.push(username);
  }
  if (password) {
    updates.push('password = ?');
    params.push(password);
  }

  if (updates.length === 0) return { id: user.id, username: user.username, is_admin: user.is_admin, created_at: user.created_at };

  query += updates.join(', ') + ' WHERE id = ?';
  params.push(Number(userId));

  db.prepare(query).run(...params);
  return getUserById(userId) || null;
}

export function createInviteToken(token: string, createdBy: number | string): void {
  const nowStr = now();
  db.prepare('INSERT INTO invite_tokens (token, created_by, created_at) VALUES (?, ?, ?)').run(token, Number(createdBy), nowStr);
}

export function getInviteToken(token: string): InviteToken | undefined {
  return db.prepare('SELECT * FROM invite_tokens WHERE token = ?').get(token) as InviteToken | undefined;
}

export function useInviteToken(token: string, userId: number | string): void {
  const nowStr = now();
  db.prepare('UPDATE invite_tokens SET used_by = ?, used_at = ? WHERE token = ?').run(Number(userId), nowStr, token);
}

export function getInvitesByUser(userId: number | string): InviteToken[] {
  return db.prepare(`
    SELECT i.*, u.username as used_by_username
    FROM invite_tokens i
    LEFT JOIN users u ON i.used_by = u.id
    WHERE i.created_by = ?
    ORDER BY i.created_at DESC
  `).all(Number(userId)) as InviteToken[];
}

export function getAllUsers(): Omit<User, 'password'>[] {
  return db.prepare('SELECT id, username, is_admin, created_at FROM users ORDER BY created_at DESC').all() as Omit<User, 'password'>[];
}

export function deleteUser(id: number | string): boolean {
  const userId = Number(id);
  const user = getUserById(userId);
  if (!user) return false;

  const deleteTrans = db.transaction(() => {
    db.prepare('DELETE FROM users WHERE id = ?').run(userId);
    db.prepare('DELETE FROM invite_tokens WHERE created_by = ?').run(userId);
  });

  deleteTrans();
  return true;
}

export function getInviteStats(): { total: number; used: number; available: number } {
  const total = (db.prepare('SELECT COUNT(*) as count FROM invite_tokens').get() as { count: number }).count;
  const used = (db.prepare('SELECT COUNT(*) as count FROM invite_tokens WHERE used_by IS NOT NULL').get() as { count: number }).count;
  return { total, used, available: total - used };
}