import { and, asc, desc, eq, type SQL } from 'drizzle-orm';
import { getDb } from '../db';
import { type Article, articles, type Feed, feeds } from '../db/schema';
import { parseHttpUrl } from '../utils/validation';
import { buildArticleConditions, buildSearchPredicates } from './article-query';
import { FeedUrlIndex } from './feed-url-index';

export interface ArticleListOptions {
  feedId?: number;
  feedUrl?: string;
  read?: boolean;
  starred?: boolean;
  broken?: boolean;
  startDate?: Date;
  endDate?: Date;
  search?: string;
  category?: string;
  author?: string;
  limit?: number;
  offset?: number;
  sortBy?: 'publishedAt' | 'createdAt' | 'title';
  sortOrder?: 'asc' | 'desc';
}

export type ArticleWithFeed = Article & { feed?: Feed };

async function resolveArticleFeedScope(options: Pick<ArticleListOptions, 'feedId' | 'feedUrl' | 'category'>) {
  const db = getDb();
  const feedIds: number[] = [];

  if (options.feedId !== undefined) {
    if (!Number.isSafeInteger(options.feedId) || options.feedId <= 0) {
      throw new RangeError('feedId must be a positive safe integer');
    }
    feedIds.push(options.feedId);
  } else if (options.feedUrl) {
    const parsedUrl = parseHttpUrl(options.feedUrl);
    if (!parsedUrl.valid) throw new Error(parsedUrl.error);

    // Feed rows created before URL canonicalization may still contain a raw,
    // canonical-equivalent value. Preserve Feed lookup priority: exact
    // canonical URL first, then the lowest-id equivalent legacy row.
    const feedRows = await db.select({ id: feeds.id, xmlUrl: feeds.xmlUrl }).from(feeds).orderBy(asc(feeds.id));
    const feedMatch = new FeedUrlIndex(feedRows).find(parsedUrl.value, options.feedUrl);
    if (!feedMatch) return null;
    feedIds.push(feedMatch.id);
  }

  // Keep category filtering as a correlated SQL predicate instead of
  // materializing every matching Feed ID. Besides avoiding an O(category-size)
  // memory spike, this stays below SQLite's bind-variable limit for large
  // installations and naturally intersects with a feedId/feedUrl scope.
  return {
    feedIds: feedIds.length > 0 ? feedIds : undefined,
    category: options.category || undefined,
  };
}

/**
 * List Articles with filtering.
 */
export async function listArticles(options: ArticleListOptions = {}): Promise<ArticleWithFeed[]> {
  if (options.limit !== undefined && (!Number.isSafeInteger(options.limit) || options.limit < 0)) {
    throw new RangeError('limit must be a non-negative safe integer');
  }
  if (options.offset !== undefined && (!Number.isSafeInteger(options.offset) || options.offset < 0)) {
    throw new RangeError('offset must be a non-negative safe integer');
  }
  const db = getDb();
  const feedScope = await resolveArticleFeedScope(options);
  if (feedScope === null) return [];

  const conditions = buildArticleConditions({
    feedIds: feedScope.feedIds,
    category: feedScope.category,
    read: options.read,
    starred: options.starred,
    broken: options.broken,
    startDate: options.startDate,
    endDate: options.endDate,
    author: options.author,
  });

  const sortColumn =
    options.sortBy === 'title'
      ? articles.title
      : options.sortBy === 'createdAt'
        ? articles.createdAt
        : articles.publishedAt;
  const sortFn = options.sortOrder === 'asc' ? asc : desc;

  const buildQuery = (conds: SQL[]) => {
    const baseQuery = db
      .select({ article: articles, feed: feeds })
      .from(articles)
      .leftJoin(feeds, eq(articles.feedId, feeds.id));
    const withWhere = conds.length > 0 ? baseQuery.where(and(...conds)) : baseQuery;
    // Add the immutable Article ID as a tie-breaker so pagination and TUI
    // refreshes remain stable when many rows share a timestamp/title.
    const withOrder = withWhere.orderBy(sortFn(sortColumn), sortFn(articles.id));
    const withLimit = options.limit !== undefined ? withOrder.limit(options.limit) : withOrder;
    return options.offset !== undefined ? withLimit.offset(options.offset) : withLimit;
  };

  const searchTerm = options.search?.trim();
  let results: Awaited<ReturnType<typeof buildQuery>>;
  if (searchTerm) {
    const search = buildSearchPredicates(searchTerm);
    if (search.fts === null) {
      results = await buildQuery([...conditions, search.like]);
    } else {
      try {
        results = await buildQuery([...conditions, search.fts]);
      } catch (error) {
        // Only fall back to LIKE for FTS-shaped failures (missing/disabled FTS
        // table or a MATCH-syntax error). Any other DB error is a real fault and
        // must surface rather than be silently masked as an empty/LIKE result (FETCH-B8).
        const message = error instanceof Error ? error.message : String(error);
        if (!/no such table:\s*(?:main\.)?articles_fts|fts5:\s*(?:syntax|parse|malformed)/i.test(message)) {
          throw error;
        }
        results = await buildQuery([...conditions, search.like]);
      }
    }
  } else {
    results = await buildQuery(conditions);
  }

  return results.map((row) => ({
    ...row.article,
    feed: row.feed || undefined,
  }));
}