import { eq, gte, inArray, lte, or, type SQL, sql } from 'drizzle-orm';
import { articles, feeds } from '../db/schema';

/**
 * Predicate-building for `listArticles`. Pure helpers so each filter dimension
 * and the FTS5/LIKE search composition can be reasoned about (and added to) in
 * isolation, instead of expanding the conditional ladder inside `listArticles`.
 */

/**
 * Escape LIKE pattern wildcards in user input to prevent wildcard injection.
 * Callers pair this with `ESCAPE '\'` in the SQL predicate.
 */
export function escapeLike(value: string): string {
  return value.replace(/\\/g, '\\\\').replace(/%/g, '\\%').replace(/_/g, '\\_');
}

export interface ArticleFilters {
  /** Resolved feed IDs for exact feed scopes. */
  feedIds?: number[];
  /** Feed category resolved as a correlated subquery, avoiding a large ID list. */
  category?: string;
  read?: boolean;
  starred?: boolean;
  broken?: boolean;
  startDate?: Date;
  endDate?: Date;
  author?: string;
}

/**
 * Build the AND-combined non-search conditions for an article query.
 * Returns a flat SQL[] so callers can append search predicates and pass to `and()`.
 */
export function buildArticleConditions(filters: ArticleFilters): SQL[] {
  const conditions: SQL[] = [];

  if (filters.feedIds && filters.feedIds.length > 0) {
    conditions.push(inArray(articles.feedId, filters.feedIds));
  }
  if (filters.category !== undefined) {
    conditions.push(
      sql`EXISTS (
        SELECT 1 FROM ${feeds}
        WHERE ${feeds.id} = ${articles.feedId} AND ${feeds.category} = ${filters.category}
      )`,
    );
  }
  if (filters.read !== undefined) {
    conditions.push(eq(articles.isRead, filters.read));
  }
  if (filters.starred !== undefined) {
    conditions.push(eq(articles.isStarred, filters.starred));
  }
  if (filters.broken !== undefined) {
    conditions.push(eq(articles.isBroken, filters.broken));
  }
  if (filters.startDate) {
    conditions.push(gte(articles.publishedAt, filters.startDate));
  }
  if (filters.endDate) {
    conditions.push(lte(articles.publishedAt, filters.endDate));
  }
  if (filters.author) {
    conditions.push(sql`${articles.author} LIKE ${`%${escapeLike(filters.author)}%`} ESCAPE '\\'`);
  }

  return conditions;
}

export interface SearchPredicates {
  /** FTS5 predicate; `null` when sanitization strips all searchable tokens. */
  fts: SQL | null;
  /** LIKE-based fallback (always usable). */
  like: SQL;
}

/**
 * Compose the search predicates for a user-provided search term.
 * The caller decides whether to try FTS5 first (with a try/catch fallback to LIKE)
 * or use LIKE directly (when `fts` is null).
 */
export function buildSearchPredicates(searchTerm: string): SearchPredicates {
  const sanitizedSearch = searchTerm
    .replace(/['"(){}[\]*:^~]/g, '') // Remove FTS5 special chars
    .replace(/\b(AND|OR|NOT|NEAR)\b/gi, '') // Remove FTS5 boolean operators
    .trim();

  const escapedTerm = `%${escapeLike(searchTerm)}%`;
  // or() returns SQL | undefined; with three concrete predicates it's always defined.
  const like = or(
    sql`${articles.title} LIKE ${escapedTerm} ESCAPE '\\'`,
    sql`${articles.description} LIKE ${escapedTerm} ESCAPE '\\'`,
    sql`${articles.content} LIKE ${escapedTerm} ESCAPE '\\'`,
  ) as SQL;

  if (!sanitizedSearch) {
    return { fts: null, like };
  }

  // Drizzle's sql`` parameterizes interpolated values, so sanitizedSearch is
  // bound — sanitization is for FTS5 syntax correctness, not injection.
  const fts = sql`${articles.id} IN (SELECT rowid FROM articles_fts WHERE articles_fts MATCH ${sanitizedSearch})`;

  return { fts, like };
}