import chalk from 'chalk';
import type { Command } from 'commander';
import { sql } from 'drizzle-orm';
import { getDb } from '../../db';
import type { Feed } from '../../db/schema';
import { articles } from '../../db/schema';
import {
  addFeed,
  getCategories,
  getFeed,
  listFeeds,
  removeBrokenFeeds,
  removeFeed,
  updateFeed,
} from '../../fetchers/feed';
import { createFeedsTable } from '../../utils/feed-formatter';
import { sanitizeTerminalText } from '../../utils/terminal';
import { validatePositiveInt, validateUrl } from '../../utils/validation';
import { assertValid, fail, requireForceForBulkDelete, requireValue } from '../error-handler';
import { runCommand } from '../run-command';

interface FeedWithCount extends Feed {
  articleCount: number;
}

async function getFeedsWithCounts(feedsList: Feed[]): Promise<FeedWithCount[]> {
  if (feedsList.length === 0) return [];
  const db = getDb();
  // Single GROUP BY query instead of N+1 per-feed COUNT queries
  const counts = await db
    .select({
      feedId: articles.feedId,
      count: sql<number>`count(*)`,
    })
    .from(articles)
    .groupBy(articles.feedId);

  const countMap = new Map(counts.map((c) => [c.feedId, c.count]));
  return feedsList.map((feed) => ({ ...feed, articleCount: countMap.get(feed.id) || 0 }));
}

export function registerFeedCommands(program: Command): void {
  const feedCmd = program.command('feed').description('Manage RSS/Atom feeds');

  // feed add
  feedCmd
    .command('add <url>')
    .description('Add a new feed')
    .option('-t, --title <title>', 'Feed title')
    .option('-c, --category <category>', 'Feed category')
    .option('--no-fetch', 'Do not fetch immediately')
    .action(async (url: string, options: { title?: string; category?: string; fetch: boolean }) => {
      await runCommand(program, async (output) => {
        assertValid(validateUrl(url));

        const spinner = output.progress('Adding feed...');

        const feed = await addFeed(url, {
          title: options.title,
          titleOverridden: options.title !== undefined,
          category: options.category,
          fetchNow: options.fetch !== false,
        });

        if (feed.initialFetchStatus === 'error') {
          spinner.fail(
            `Feed added, but initial fetch failed: ${sanitizeTerminalText(feed.initialFetchError ?? 'Unknown error')}`,
          );
        } else {
          spinner.succeed(`Feed added: ${sanitizeTerminalText(feed.title)} (ID: ${feed.id})`);
        }

        output.result(feed, (data) => {
          const lines = [chalk.gray(`  URL: ${sanitizeTerminalText(data.xmlUrl)}`)];
          if (data.category) lines.push(chalk.gray(`  Category: ${sanitizeTerminalText(data.category)}`));
          if (data.initialFetchStatus === 'error') {
            lines.push(
              chalk.red(`  Initial fetch failed: ${sanitizeTerminalText(data.initialFetchError ?? 'Unknown error')}`),
            );
          }
          return lines.join('\n');
        });
      });
    });

  // feed remove
  feedCmd
    .command('remove <id>')
    .alias('rm')
    .description('Remove a feed')
    .option('-f, --force', 'Force removal without confirmation')
    .action(async (id: string, options: { force?: boolean }) => {
      await runCommand(program, async (output) => {
        const feedId = requireValue(validatePositiveInt(id, 'Feed ID'));

        const feed = await getFeed(feedId);
        if (!feed) {
          fail('Feed not found');
        }

        if (!options.force) {
          if (program.opts().json) {
            fail('Feed removal requires --force in JSON mode');
          }

          // Interactive confirmation requires a TTY on both ends; refuse otherwise
          // so piped invocations cannot accidentally consume unrelated input.
          if (!process.stdin.isTTY || !process.stdout.isTTY) {
            fail('Feed removal requires --force in non-interactive mode');
          }

          const { createInterface } = await import('node:readline/promises');
          const rl = createInterface({
            input: process.stdin,
            output: process.stdout,
          });
          const answer = await rl.question(
            `Delete feed "${sanitizeTerminalText(feed.title)}" and all its articles? [y/N] `,
          );
          rl.close();

          const confirmed = /^(y|yes)$/i.test(answer.trim());
          if (!confirmed) {
            output.info(chalk.gray('Cancelled'));
            return;
          }
        }

        await removeFeed(feedId);

        output.result({ id: feedId }, () => chalk.green(`Feed removed: ${sanitizeTerminalText(feed.title)}`));
      });
    });

  // feed list
  feedCmd
    .command('list')
    .alias('ls')
    .description('List all feeds')
    .option('-c, --category <category>', 'Filter by category')
    .option('--active', 'Show only active feeds')
    .option('--inactive', 'Show only inactive feeds')
    .option('--errors', 'Show only feeds with errors')
    .action(async (options: { category?: string; active?: boolean; inactive?: boolean; errors?: boolean }) => {
      await runCommand(program, async (output) => {
        if (options.active && options.inactive) {
          fail('Use either --active or --inactive, not both');
        }

        const feedsList = await listFeeds({
          category: options.category,
          active: options.active ? true : options.inactive ? false : undefined,
          hasErrors: options.errors,
        });

        const feedsWithCounts = await getFeedsWithCounts(feedsList);

        output.result(feedsWithCounts, (feeds) =>
          feeds.length === 0
            ? chalk.gray('No feeds found')
            : `${createFeedsTable(feeds)}\n${chalk.gray(`\nTotal: ${feeds.length} feeds`)}`,
        );
      });
    });

  // feed update
  feedCmd
    .command('update <id>')
    .description('Update feed properties')
    .option('-t, --title <title>', 'New title')
    .option('-c, --category <category>', 'New category')
    .option('--clear-category', 'Remove the feed category')
    .option('--activate', 'Activate feed')
    .option('--deactivate', 'Deactivate feed')
    .action(
      async (
        id: string,
        options: {
          title?: string;
          category?: string;
          clearCategory?: boolean;
          activate?: boolean;
          deactivate?: boolean;
        },
      ) => {
        await runCommand(program, async (output) => {
          const feedId = requireValue(validatePositiveInt(id, 'Feed ID'));

          if (options.activate && options.deactivate) {
            fail('Use either --activate or --deactivate, not both');
          }
          if (options.category !== undefined && options.clearCategory) {
            fail('Use either --category or --clear-category, not both');
          }

          const updates: Partial<Pick<Feed, 'title' | 'category' | 'isActive'>> = {};
          if (options.title !== undefined) updates.title = options.title;
          if (options.category !== undefined) updates.category = options.category;
          if (options.clearCategory) updates.category = null;
          if (options.activate) updates.isActive = true;
          if (options.deactivate) updates.isActive = false;

          if (Object.keys(updates).length === 0) {
            fail('No updates specified');
          }

          const feed = await updateFeed(feedId, updates);
          if (!feed) {
            fail('Feed not found');
          }

          output.result(feed, (data) => chalk.green(`Feed updated: ${sanitizeTerminalText(data.title)}`));
        });
      },
    );

  // feed categories
  feedCmd
    .command('categories')
    .description('List all feed categories')
    .action(async () => {
      await runCommand(program, async (output) => {
        const categories = await getCategories();

        output.result(categories, (cats) => {
          if (cats.length === 0) return chalk.gray('No categories found');
          return cats.map((c) => `  ${sanitizeTerminalText(c)}`).join('\n');
        });
      });
    });

  // feed cleanup
  feedCmd
    .command('cleanup')
    .description('Remove feeds with errors')
    .option('-e, --errors <n>', 'Minimum error count', '1')
    .option('--dry-run', 'Show what would be removed without deleting')
    .option('--force', 'Confirm deletion after previewing with --dry-run')
    .action(async (options: { errors: string; dryRun?: boolean; force?: boolean }) => {
      await runCommand(program, async (output) => {
        const errorCount = requireValue(validatePositiveInt(options.errors, 'Error count'));
        requireForceForBulkDelete({ command: 'feed cleanup', force: options.force, dryRun: options.dryRun });

        const spinner = output.progress('Finding feeds with errors...');

        const result = await removeBrokenFeeds({
          errorCount,
          dryRun: options.dryRun,
        });

        if (options.dryRun) {
          const preview = result.preview ?? [];
          spinner.warn('Dry run - no feeds removed');
          output.result({ dryRun: true, matching: result.matching ?? 0, preview }, (data) => {
            if (data.preview.length === 0) return chalk.gray('No feeds with errors found');
            const lines = data.preview.map(
              (feed) => `  ${feed.id}: ${sanitizeTerminalText(feed.title)} (${feed.errorCount} errors)`,
            );
            lines.push(chalk.yellow(`\nWould remove ${data.matching} feeds`));
            return lines.join('\n');
          });
        } else {
          spinner.stop();
          output.result({ removed: result.removed }, (data) =>
            chalk.green(`Removed ${data.removed} feeds with errors`),
          );
        }
      });
    });
}