import { query } from '../db.js';

export interface Category {
  id: string;
  slug: string;
  name_en: string;
  name_nl: string;
  dimension: string;
  parent_id: string | null;
  depth: number;
  sort_order: number;
  icon_url: string | null;
  is_active: boolean;
  created_at: string;
  updated_at: string;
  children?: Category[];
}

/** Get full category tree, optionally filtered by dimension */
export async function getCategoryTree(dimension?: string): Promise<Category[]> {
  const params: any[] = [];
  let where = 'WHERE c.is_active = TRUE';
  if (dimension) {
    where += ` AND c.dimension = $1`;
    params.push(dimension);
  }

  const { rows } = await query<Category>(
    `SELECT * FROM categories c ${where} ORDER BY c.depth, c.sort_order, c.name_en`,
    params
  );

  // Build tree
  const byId = new Map<string, Category>();
  const roots: Category[] = [];
  for (const row of rows) {
    row.children = [];
    byId.set(row.id, row);
  }
  for (const row of rows) {
    if (row.parent_id && byId.has(row.parent_id)) {
      byId.get(row.parent_id)!.children!.push(row);
    } else {
      roots.push(row);
    }
  }
  return roots;
}

/** Get a single category by slug */
export async function getCategoryBySlug(slug: string): Promise<Category | null> {
  const { rows } = await query<Category>(
    'SELECT * FROM categories WHERE slug = $1',
    [slug]
  );
  return rows[0] || null;
}

/** Get a single category by id */
export async function getCategoryById(id: string): Promise<Category | null> {
  const { rows } = await query<Category>(
    'SELECT * FROM categories WHERE id = $1',
    [id]
  );
  return rows[0] || null;
}

/** Create a new category */
export async function createCategory(data: {
  slug: string;
  name_en: string;
  name_nl: string;
  dimension: string;
  parent_id?: string;
  sort_order?: number;
  icon_url?: string;
}): Promise<Category> {
  let depth = 0;
  if (data.parent_id) {
    const parent = await getCategoryById(data.parent_id);
    if (parent) depth = parent.depth + 1;
  }

  const { rows } = await query<Category>(
    `INSERT INTO categories (slug, name_en, name_nl, dimension, parent_id, depth, sort_order, icon_url)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
     RETURNING *`,
    [data.slug, data.name_en, data.name_nl, data.dimension, data.parent_id || null, depth, data.sort_order || 0, data.icon_url || null]
  );
  return rows[0];
}

/** Update a category */
export async function updateCategory(id: string, data: Partial<{
  name_en: string;
  name_nl: string;
  sort_order: number;
  icon_url: string;
  is_active: boolean;
}>): Promise<Category | null> {
  const fields: string[] = [];
  const params: any[] = [];
  let idx = 1;

  for (const [key, value] of Object.entries(data)) {
    if (value !== undefined) {
      fields.push(`${key} = $${idx}`);
      params.push(value);
      idx++;
    }
  }
  if (fields.length === 0) return getCategoryById(id);

  fields.push(`updated_at = now()`);
  params.push(id);

  const { rows } = await query<Category>(
    `UPDATE categories SET ${fields.join(', ')} WHERE id = $${idx} RETURNING *`,
    params
  );
  return rows[0] || null;
}

/** Soft-delete a category */
export async function deleteCategory(id: string): Promise<boolean> {
  const { rowCount } = await query(
    'UPDATE categories SET is_active = FALSE, updated_at = now() WHERE id = $1',
    [id]
  );
  return (rowCount ?? 0) > 0;
}

/** Get products in a category (paginated) */
export async function getProductsByCategory(
  slug: string,
  options: { page?: number; limit?: number; sort?: string } = {}
): Promise<{ products: any[]; total: number }> {
  const page = options.page || 1;
  const limit = Math.min(options.limit || 24, 100);
  const offset = (page - 1) * limit;

  const sortMap: Record<string, string> = {
    'price_asc': 'p.price ASC',
    'price_desc': 'p.price DESC',
    'newest': 'p.created_at DESC',
    'name': 'p.name ASC',
    'discount': `CASE WHEN p.original_price > 0 THEN (p.original_price - p.price) / p.original_price ELSE 0 END DESC`,
  };
  const orderBy = sortMap[options.sort || 'newest'] || 'p.created_at DESC';

  // Get category and all children
  const { rows: [countRow] } = await query(
    `SELECT COUNT(*) as total FROM product_categories pc
     JOIN categories c ON c.id = pc.category_id
     WHERE (c.slug = $1 OR c.slug LIKE $1 || '/%') AND c.is_active = TRUE`,
    [slug]
  );

  const { rows: products } = await query(
    `SELECT DISTINCT p.* FROM products p
     JOIN product_categories pc ON pc.product_id = p.id
     JOIN categories c ON c.id = pc.category_id
     WHERE (c.slug = $1 OR c.slug LIKE $1 || '/%') AND c.is_active = TRUE
     ORDER BY ${orderBy}
     LIMIT $2 OFFSET $3`,
    [slug, limit, offset]
  );

  return { products, total: parseInt(countRow.total) };
}
