import { and, asc, desc, eq, like, lt, or, type SQL, sql } from 'drizzle-orm';
import { getDb } from '../db';
import { type Article, articles, type Feed, feeds } from '../db/schema';
import { runAndCountChanges } from '../db/sqlite-result';

function validateOptionalFeedId(feedId: number | undefined): void {
  if (feedId !== undefined && (!Number.isSafeInteger(feedId) || feedId <= 0)) {
    throw new RangeError('feedId must be a positive safe integer');
  }
}

/**
 * Mark article as read
 */
export async function markArticleRead(articleId: number, read: boolean = true): Promise<Article | undefined> {
  const db = getDb();

  const [article] = await db
    .update(articles)
    .set({ isRead: read, updatedAt: new Date() })
    .where(eq(articles.id, articleId))
    .returning();

  return article;
}

/** Toggle read state in SQLite, returning the state produced by this write. */
export async function toggleArticleRead(articleId: number): Promise<Article | undefined> {
  const db = getDb();

  const [article] = await db
    .update(articles)
    .set({ isRead: sql<boolean>`not ${articles.isRead}`, updatedAt: new Date() })
    .where(eq(articles.id, articleId))
    .returning();

  return article;
}

/**
 * Star/unstar article. Returns the article and whether the state changed.
 */
export async function starArticle(
  articleId: number,
  starred: boolean = true,
): Promise<{ article: Article; changed: boolean } | undefined> {
  const db = getDb();

  const [existing] = await db.select().from(articles).where(eq(articles.id, articleId));
  if (!existing) return undefined;

  if (existing.isStarred === starred) {
    return { article: existing, changed: false };
  }

  const [article] = await db
    .update(articles)
    .set({ isStarred: starred, updatedAt: new Date() })
    .where(eq(articles.id, articleId))
    .returning();

  return article ? { article, changed: true } : undefined;
}

/** Toggle star state in SQLite, returning the state produced by this write. */
export async function toggleArticleStar(articleId: number): Promise<Article | undefined> {
  const db = getDb();

  const [article] = await db
    .update(articles)
    .set({ isStarred: sql<boolean>`not ${articles.isStarred}`, updatedAt: new Date() })
    .where(eq(articles.id, articleId))
    .returning();

  return article;
}

/**
 * Mark all articles as read
 */
export async function markAllRead(options: { feedId?: number; olderThan?: Date } = {}): Promise<number> {
  validateOptionalFeedId(options.feedId);
  const db = getDb();

  const conditions = [eq(articles.isRead, false)];

  if (options.feedId !== undefined) {
    conditions.push(eq(articles.feedId, options.feedId));
  }

  if (options.olderThan) {
    conditions.push(lt(articles.publishedAt, options.olderThan));
  }

  return runAndCountChanges(db, () =>
    db
      .update(articles)
      .set({ isRead: true, updatedAt: new Date() })
      .where(and(...conditions))
      .run(),
  );
}

/**
 * Historic releases could set `isBroken` for timeout/extraction failures. Only
 * a recorded 404/410 is safe for destructive cleanup; other rows remain
 * visible to the user and retryable rather than being silently deleted.
 */
function definitivelyBrokenCondition(): SQL {
  const condition = and(
    eq(articles.isBroken, true),
    or(like(articles.brokenReason, 'HTTP 404%'), like(articles.brokenReason, 'HTTP 410%')),
  );
  if (!condition) throw new Error('Unable to build definitive-broken Article filter');
  return condition;
}

/** Remove Articles whose original link is definitively missing (HTTP 404/410). */
export async function removeBrokenArticles(
  options: { dryRun?: boolean; feedId?: number } = {},
): Promise<{ removed: number; matching?: number; preview?: (Article & { feed?: Feed })[] }> {
  validateOptionalFeedId(options.feedId);
  const db = getDb();
  const conditions = [definitivelyBrokenCondition()];

  if (options.feedId !== undefined) {
    conditions.push(eq(articles.feedId, options.feedId));
  }

  if (options.dryRun) {
    const [countRows, rows] = await Promise.all([
      db
        .select({ count: sql<number>`count(*)` })
        .from(articles)
        .where(and(...conditions)),
      db
        .select({ article: articles, feed: feeds })
        .from(articles)
        .leftJoin(feeds, eq(articles.feedId, feeds.id))
        .where(and(...conditions))
        .limit(100),
    ]);

    return {
      removed: 0,
      matching: countRows[0]?.count ?? 0,
      preview: rows.map((row) => ({ ...row.article, feed: row.feed || undefined })),
    };
  }

  const removed = runAndCountChanges(db, () =>
    db
      .delete(articles)
      .where(and(...conditions))
      .run(),
  );
  return { removed };
}

/**
 * Cleanup articles based on various criteria
 */
export async function cleanupArticles(
  options: { broken?: boolean; read?: boolean; olderThan?: Date; dryRun?: boolean; feedId?: number } = {},
): Promise<{ removed: number; matching?: number; preview?: (Article & { feed?: Feed })[] }> {
  validateOptionalFeedId(options.feedId);
  if (!options.broken && !options.read && !options.olderThan) {
    throw new Error(
      'At least one cleanup criterion is required (--broken, --read, or --older-than); feedId only scopes cleanup',
    );
  }

  const db = getDb();
  const conditions: SQL[] = [];

  if (options.broken) {
    conditions.push(definitivelyBrokenCondition());
  }

  if (options.read) {
    conditions.push(eq(articles.isRead, true));
    conditions.push(eq(articles.isStarred, false));
  }

  if (options.olderThan) {
    conditions.push(lt(articles.publishedAt, options.olderThan));
  }

  if (options.feedId !== undefined) {
    conditions.push(eq(articles.feedId, options.feedId));
  }

  if (options.dryRun) {
    const [countRows, rows] = await Promise.all([
      db
        .select({ count: sql<number>`count(*)` })
        .from(articles)
        .where(and(...conditions)),
      db
        .select({ article: articles, feed: feeds })
        .from(articles)
        .leftJoin(feeds, eq(articles.feedId, feeds.id))
        .where(and(...conditions))
        .orderBy(desc(articles.publishedAt), desc(articles.id))
        .limit(100),
    ]);

    return {
      removed: 0,
      matching: countRows[0]?.count ?? 0,
      preview: rows.map((row) => ({ ...row.article, feed: row.feed || undefined })),
    };
  }

  const removed = runAndCountChanges(db, () =>
    db
      .delete(articles)
      .where(and(...conditions))
      .run(),
  );
  return { removed };
}

/**
 * Get article statistics
 */
export async function getArticleStats(): Promise<{
  total: number;
  read: number;
  unread: number;
  starred: number;
  broken: number;
  byFeed: Array<{ feedId: number; feedTitle: string; count: number; unread: number }>;
}> {
  const db = getDb();

  const statsResult = await db
    .select({
      total: sql<number>`count(*)`,
      read: sql<number>`sum(case when is_read = 1 then 1 else 0 end)`,
      starred: sql<number>`sum(case when is_starred = 1 then 1 else 0 end)`,
      broken: sql<number>`sum(case when is_broken = 1 then 1 else 0 end)`,
    })
    .from(articles);

  const total = statsResult[0]?.total || 0;
  const read = statsResult[0]?.read || 0;
  const starred = statsResult[0]?.starred || 0;
  const broken = statsResult[0]?.broken || 0;

  const byFeedResult = await db
    .select({
      feedId: articles.feedId,
      feedTitle: feeds.title,
      count: sql<number>`count(*)`,
      unread: sql<number>`sum(case when is_read = 0 then 1 else 0 end)`,
    })
    .from(articles)
    .leftJoin(feeds, eq(articles.feedId, feeds.id))
    .groupBy(articles.feedId)
    .orderBy(asc(articles.feedId));

  return {
    total,
    read,
    unread: total - read,
    starred,
    broken,
    byFeed: byFeedResult.map((row) => ({
      feedId: row.feedId,
      feedTitle: row.feedTitle || 'Unknown',
      count: row.count,
      unread: row.unread,
    })),
  };
}