/**
 * Smart Collection Rule Engine
 * Translates JSON rules into parameterized SQL WHERE clauses.
 * All values are parameterized — no SQL injection possible.
 */

export interface FieldRule {
  type: 'field';
  field: string;
  op: string;
  value: any;
}

export interface CategoryRule {
  type: 'category';
  slug: string;
}

export interface TagRule {
  type: 'tag';
  slug: string;
}

export interface TimeRule {
  type: 'time';
  field: string;
  op: string;
  value: number;
}

export interface PriceHistoryRule {
  type: 'price_history';
  op: string;
  period_days: number;
  min_drop_pct: number;
}

export interface NestedRule {
  type: 'nested';
  operator: 'AND' | 'OR';
  rules: Rule[];
}

export type Rule = FieldRule | CategoryRule | TagRule | TimeRule | PriceHistoryRule | NestedRule;

export interface RuleGroup {
  operator: 'AND' | 'OR';
  rules: Rule[];
}

export interface SQLResult {
  where: string;
  params: any[];
}

/** Allowed fields for field rules (whitelist to prevent injection) */
const ALLOWED_FIELDS: Record<string, string> = {
  '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`,
};

/** Allowed time fields */
const ALLOWED_TIME_FIELDS: Record<string, string> = {
  'first_seen_at': 'p.first_seen_at',
  'created_at': 'p.created_at',
  'updated_at': 'p.updated_at',
};

/** Translate a single field rule to SQL */
function fieldToSQL(rule: FieldRule, params: any[]): string {
  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: string) => {
        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: string) => {
        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: any) => {
        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}`);
  }
}

/** Translate a time rule to SQL */
function timeToSQL(rule: TimeRule, params: any[]): string {
  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}`);
}

/** Translate a category rule to SQL */
function categoryToSQL(rule: CategoryRule, params: any[]): string {
  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})
  )`;
}

/** Translate a tag rule to SQL */
function tagToSQL(rule: TagRule, params: any[]): string {
  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}
  )`;
}

/** Translate a price history rule to SQL */
function priceHistoryToSQL(rule: PriceHistoryRule, params: any[]): string {
  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}`);
}

/** Translate a rule group to SQL WHERE clause */
export function rulesToSQL(ruleGroup: RuleGroup): SQLResult {
  const params: any[] = [];
  const where = translateGroup(ruleGroup, params);
  return { where, params };
}

function translateGroup(group: RuleGroup | NestedRule, params: any[]): string {
  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: Rule, params: any[]): string {
  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:
      // Handle rules without explicit type (legacy format)
      if ('field' in rule) return fieldToSQL(rule as FieldRule, params);
      throw new Error(`Unknown rule type: ${(rule as any).type}`);
  }
}

/** Get the SQL ORDER BY expression for a sort_by value */
export function getSortExpression(sortBy: string): string {
  const sortMap: Record<string, string> = {
    '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'];
}

/** Get the sort score expression for materialization */
export function getSortScoreExpression(sortBy: string): string {
  const scoreMap: Record<string, string> = {
    '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';
}
