import { q as query } from './db_Du3AmLCY.mjs';

const ALLOWED_FIELDS = {
  "price": "p.price",
  "original_price": "p.original_price",
  "brand": "LOWER(p.brand)",
  "name": "LOWER(p.name)",
  "gender": "LOWER(p.gender)",
  "shop": "LOWER(p.shop)",
  "clicks_48h": "p.clicks_48h",
  "clicks_7d": "p.clicks_7d",
  "conversions_30d": "p.conversions_30d",
  "first_seen_at": "p.first_seen_at",
  "created_at": "p.created_at",
  "discount_pct": `CASE WHEN p.original_price > 0 THEN ((p.original_price - p.price) / p.original_price * 100) ELSE 0 END`
};
const ALLOWED_TIME_FIELDS = {
  "first_seen_at": "p.first_seen_at",
  "created_at": "p.created_at",
  "updated_at": "p.updated_at"
};
function fieldToSQL(rule, params) {
  const col = ALLOWED_FIELDS[rule.field];
  if (!col) throw new Error(`Unknown field: ${rule.field}`);
  const idx = () => {
    params.push(rule.value);
    return `$${params.length}`;
  };
  switch (rule.op) {
    case "eq":
      return `${col} = ${idx()}`;
    case "neq":
      return `${col} != ${idx()}`;
    case "gt":
      return `${col} > ${idx()}`;
    case "gte":
      return `${col} >= ${idx()}`;
    case "lt":
      return `${col} < ${idx()}`;
    case "lte":
      return `${col} <= ${idx()}`;
    case "contains": {
      params.push(`%${rule.value}%`);
      return `${col} ILIKE $${params.length}`;
    }
    case "contains_any": {
      if (!Array.isArray(rule.value)) throw new Error("contains_any requires array value");
      const clauses = rule.value.map((v) => {
        params.push(`%${v}%`);
        return `${col} ILIKE $${params.length}`;
      });
      return `(${clauses.join(" OR ")})`;
    }
    case "contains_all": {
      if (!Array.isArray(rule.value)) throw new Error("contains_all requires array value");
      const clauses = rule.value.map((v) => {
        params.push(`%${v}%`);
        return `${col} ILIKE $${params.length}`;
      });
      return `(${clauses.join(" AND ")})`;
    }
    case "in": {
      if (!Array.isArray(rule.value)) throw new Error("in requires array value");
      const placeholders = rule.value.map((v) => {
        params.push(typeof v === "string" ? v.toLowerCase() : v);
        return `$${params.length}`;
      });
      return `${col} IN (${placeholders.join(", ")})`;
    }
    case "is_null":
      return `${col} IS NULL`;
    case "is_not_null":
      return `${col} IS NOT NULL`;
    default:
      throw new Error(`Unknown operator: ${rule.op}`);
  }
}
function timeToSQL(rule, params) {
  const col = ALLOWED_TIME_FIELDS[rule.field];
  if (!col) throw new Error(`Unknown time field: ${rule.field}`);
  if (rule.op === "within_days") {
    params.push(rule.value);
    return `${col} >= now() - ($${params.length} || ' days')::interval`;
  }
  throw new Error(`Unknown time operator: ${rule.op}`);
}
function categoryToSQL(rule, params) {
  params.push(rule.slug);
  const slugParam = `$${params.length}`;
  params.push(rule.slug + "/%");
  const likeParam = `$${params.length}`;
  return `EXISTS (
    SELECT 1 FROM product_categories pc2
    JOIN categories c2 ON c2.id = pc2.category_id
    WHERE pc2.product_id = p.id AND (c2.slug = ${slugParam} OR c2.slug LIKE ${likeParam})
  )`;
}
function tagToSQL(rule, params) {
  params.push(rule.slug);
  return `EXISTS (
    SELECT 1 FROM product_tags pt2
    JOIN tags t2 ON t2.id = pt2.tag_id
    WHERE pt2.product_id = p.id AND t2.slug = $${params.length}
  )`;
}
function priceHistoryToSQL(rule, params) {
  if (rule.op === "dropped") {
    params.push(rule.period_days);
    const daysParam = `$${params.length}`;
    params.push(rule.min_drop_pct);
    const pctParam = `$${params.length}`;
    return `EXISTS (
      SELECT 1 FROM price_history ph
      WHERE ph.product_id = p.id
        AND ph.recorded_at >= now() - (${daysParam} || ' days')::interval
        AND ph.price > p.price
        AND ((ph.price - p.price) / ph.price * 100) >= ${pctParam}
    )`;
  }
  throw new Error(`Unknown price_history operator: ${rule.op}`);
}
function rulesToSQL(ruleGroup) {
  const params = [];
  const where = translateGroup(ruleGroup, params);
  return { where, params };
}
function translateGroup(group, params) {
  if (!group.rules || group.rules.length === 0) return "TRUE";
  const operator = group.operator === "OR" ? " OR " : " AND ";
  const clauses = group.rules.map((rule) => translateRule(rule, params));
  return `(${clauses.join(operator)})`;
}
function translateRule(rule, params) {
  switch (rule.type) {
    case "field":
      return fieldToSQL(rule, params);
    case "category":
      return categoryToSQL(rule, params);
    case "tag":
      return tagToSQL(rule, params);
    case "time":
      return timeToSQL(rule, params);
    case "price_history":
      return priceHistoryToSQL(rule, params);
    case "nested":
      return translateGroup(rule, params);
    default:
      if ("field" in rule) return fieldToSQL(rule, params);
      throw new Error(`Unknown rule type: ${rule.type}`);
  }
}
function getSortExpression(sortBy) {
  const sortMap = {
    "relevance": "p.created_at DESC",
    "price_asc": "p.price ASC",
    "price_desc": "p.price DESC",
    "newest": "p.created_at DESC",
    "discount": `CASE WHEN p.original_price > 0 THEN (p.original_price - p.price) / p.original_price ELSE 0 END DESC`,
    "discount_desc": `CASE WHEN p.original_price > 0 THEN (p.original_price - p.price) / p.original_price ELSE 0 END DESC`,
    "clicks_48h_desc": "p.clicks_48h DESC",
    "conversions_desc": "p.conversions_30d DESC",
    "drop_pct_desc": `CASE WHEN p.original_price > 0 THEN (p.original_price - p.price) / p.original_price ELSE 0 END DESC`
  };
  return sortMap[sortBy] || sortMap["relevance"];
}
function getSortScoreExpression(sortBy) {
  const scoreMap = {
    "relevance": "0",
    "price_asc": "(-1 * p.price)",
    "price_desc": "p.price",
    "newest": "EXTRACT(EPOCH FROM p.created_at)",
    "discount": `CASE WHEN p.original_price > 0 THEN (p.original_price - p.price) / p.original_price ELSE 0 END`,
    "discount_desc": `CASE WHEN p.original_price > 0 THEN (p.original_price - p.price) / p.original_price ELSE 0 END`,
    "clicks_48h_desc": "COALESCE(p.clicks_48h, 0)",
    "conversions_desc": "COALESCE(p.conversions_30d, 0)",
    "drop_pct_desc": `CASE WHEN p.original_price > 0 THEN (p.original_price - p.price) / p.original_price ELSE 0 END`
  };
  return scoreMap[sortBy] || "0";
}

async function listCollections(options = {}) {
  const where = options.activeOnly !== false ? "WHERE is_active = TRUE" : "";
  const { rows } = await query(
    `SELECT * FROM collections ${where} ORDER BY homepage_sort NULLS LAST, name_en`
  );
  return rows;
}
async function getCollectionBySlug(slug) {
  const { rows } = await query(
    "SELECT * FROM collections WHERE slug = $1",
    [slug]
  );
  return rows[0] || null;
}
async function getCollectionById(id) {
  const { rows } = await query(
    "SELECT * FROM collections WHERE id = $1",
    [id]
  );
  return rows[0] || null;
}
async function createCollection(data) {
  const { rows } = await query(
    `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];
}
async function updateCollection(id, data) {
  const fields = [];
  const params = [];
  let idx = 1;
  for (const [key, value] of Object.entries(data)) {
    if (value !== void 0) {
      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(
    `UPDATE collections SET ${fields.join(", ")} WHERE id = $${idx} RETURNING *`,
    params
  );
  return rows[0] || null;
}
async function deleteCollection(id) {
  const { rowCount } = await query("DELETE FROM collections WHERE id = $1", [id]);
  return (rowCount ?? 0) > 0;
}
async function refreshCollection(collectionId) {
  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;
  const { where, params } = rulesToSQL(ruleGroup);
  const sortScore = getSortScoreExpression(collection.sort_by);
  const limitClause = collection.max_products ? `LIMIT ${collection.max_products}` : "";
  await query("DELETE FROM collection_products WHERE collection_id = $1", [collectionId]);
  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;
}
async function getCollectionProducts(slug, options = {}) {
  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)
  };
}

export { createCollection as c, deleteCollection as d, getCollectionProducts as g, listCollections as l, refreshCollection as r, updateCollection as u };
