import { query } from '../db.js';
import { rulesToSQL, getSortExpression, getSortScoreExpression, type RuleGroup } from './rule-engine.js';

export interface Collection {
  id: string;
  slug: string;
  name_en: string;
  name_nl: string;
  description: string | null;
  type: 'smart' | 'manual' | 'hybrid';
  rules_json: RuleGroup | null;
  sort_by: string;
  is_active: boolean;
  is_featured: boolean;
  homepage_sort: number | null;
  image_url: string | null;
  max_products: number | null;
  cache_ttl_sec: number;
  created_at: string;
  updated_at: string;
}

/** List all collections */
export async function listCollections(options: { activeOnly?: boolean } = {}): Promise<Collection[]> {
  const where = options.activeOnly !== false ? 'WHERE is_active = TRUE' : '';
  const { rows } = await query<Collection>(
    `SELECT * FROM collections ${where} ORDER BY homepage_sort NULLS LAST, name_en`
  );
  return rows;
}

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

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

/** Create a new collection */
export async function createCollection(data: {
  slug: string;
  name_en: string;
  name_nl: string;
  description?: string;
  type: 'smart' | 'manual' | 'hybrid';
  rules_json?: RuleGroup;
  sort_by?: string;
  is_featured?: boolean;
  homepage_sort?: number;
  max_products?: number;
  cache_ttl_sec?: number;
}): Promise<Collection> {
  const { rows } = await query<Collection>(
    `INSERT INTO collections (slug, name_en, name_nl, description, type, rules_json, sort_by, is_featured, homepage_sort, max_products, cache_ttl_sec)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
     RETURNING *`,
    [
      data.slug, data.name_en, data.name_nl, data.description || null,
      data.type, data.rules_json ? JSON.stringify(data.rules_json) : null,
      data.sort_by || 'relevance', data.is_featured || false,
      data.homepage_sort || null, data.max_products || null,
      data.cache_ttl_sec || 300,
    ]
  );
  return rows[0];
}

/** Update a collection */
export async function updateCollection(id: string, data: Partial<{
  name_en: string;
  name_nl: string;
  description: string;
  rules_json: RuleGroup;
  sort_by: string;
  is_active: boolean;
  is_featured: boolean;
  homepage_sort: number;
  max_products: number;
  cache_ttl_sec: number;
}>): Promise<Collection | null> {
  const fields: string[] = [];
  const params: any[] = [];
  let idx = 1;

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

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

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

/** Delete a collection */
export async function deleteCollection(id: string): Promise<boolean> {
  const { rowCount } = await query('DELETE FROM collections WHERE id = $1', [id]);
  return (rowCount ?? 0) > 0;
}

/** Materialize a smart collection: run rules → populate collection_products */
export async function refreshCollection(collectionId: string): Promise<number> {
  const collection = await getCollectionById(collectionId);
  if (!collection) throw new Error(`Collection not found: ${collectionId}`);
  if (!collection.rules_json) return 0;

  const ruleGroup = collection.rules_json as RuleGroup;
  const { where, params } = rulesToSQL(ruleGroup);
  const sortScore = getSortScoreExpression(collection.sort_by);
  const limitClause = collection.max_products ? `LIMIT ${collection.max_products}` : '';

  // Delete old materialization
  await query('DELETE FROM collection_products WHERE collection_id = $1', [collectionId]);

  // Insert new materialization
  params.push(collectionId);
  const collectionParam = `$${params.length}`;

  const sql = `
    INSERT INTO collection_products (collection_id, product_id, sort_score)
    SELECT ${collectionParam}, p.id, (${sortScore})::REAL
    FROM products p
    WHERE ${where}
    ORDER BY ${getSortExpression(collection.sort_by)}
    ${limitClause}
  `;

  const result = await query(sql, params);
  return result.rowCount ?? 0;
}

/** Refresh all active smart collections */
export async function refreshAllCollections(): Promise<Record<string, number>> {
  const collections = await listCollections({ activeOnly: true });
  const results: Record<string, number> = {};

  for (const col of collections) {
    if (col.type === 'smart' || col.type === 'hybrid') {
      try {
        results[col.slug] = await refreshCollection(col.id);
      } catch (e) {
        console.error(`Failed to refresh collection ${col.slug}:`, e);
        results[col.slug] = -1;
      }
    }
  }
  return results;
}

/** Get products in a collection (paginated, from materialized table) */
export async function getCollectionProducts(
  slug: string,
  options: { page?: number; limit?: number } = {}
): Promise<{ collection: Collection; products: any[]; total: number } | null> {
  const collection = await getCollectionBySlug(slug);
  if (!collection) return null;

  const page = options.page || 1;
  const limit = Math.min(options.limit || 24, 100);
  const offset = (page - 1) * limit;

  const { rows: [countRow] } = await query(
    'SELECT COUNT(*) as total FROM collection_products WHERE collection_id = $1',
    [collection.id]
  );

  const { rows: products } = await query(
    `SELECT p.* FROM products p
     JOIN collection_products cp ON cp.product_id = p.id
     WHERE cp.collection_id = $1
     ORDER BY cp.sort_score DESC
     LIMIT $2 OFFSET $3`,
    [collection.id, limit, offset]
  );

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