import { statSync } from 'node:fs';
import chalk from 'chalk';
import type { Command } from 'commander';
import { count, sql } from 'drizzle-orm';
import { getDb, getDbPath, initDb } from '../../db';
import { articles, feeds } from '../../db/schema';
import { cleanupFetchCache } from '../../fetchers/cache-maintenance';
import { sanitizeTerminalText } from '../../utils/terminal';
import { validateIntRange } from '../../utils/validation';
import { requireValue } from '../error-handler';
import { runCommand } from '../run-command';

function safeFileSize(path: string): number | undefined {
  try {
    return statSync(path).size;
  } catch {
    return undefined;
  }
}

function formatBytes(bytes: number): string {
  if (bytes < 1024) return `${bytes} B`;
  const units = ['KB', 'MB', 'GB', 'TB'];
  let value = bytes / 1024;
  let unitIdx = 0;
  while (value >= 1024 && unitIdx < units.length - 1) {
    value /= 1024;
    unitIdx++;
  }
  return `${value.toFixed(2)} ${units[unitIdx]}`;
}

export function registerDbCommands(program: Command): void {
  const dbCmd = program.command('db').description('Database management');

  dbCmd
    .command('init')
    .description('Initialize database')
    .action(async () => {
      await runCommand(program, async (output) => {
        const opts = program.opts() as { database?: string };
        const dbPath = getDbPath(opts.database);
        initDb(opts.database);

        output.result({ initialized: true, dbPath }, () => chalk.green('Database initialized'));
      });
    });

  dbCmd
    .command('stats')
    .description('Show database statistics')
    .action(async () => {
      await runCommand(program, async (output) => {
        const opts = program.opts() as { database?: string };
        const dbPath = getDbPath(opts.database);
        const db = getDb();

        const [feedCount] = await db.select({ count: count() }).from(feeds);
        const [articleCount] = await db.select({ count: count() }).from(articles);
        const dbSizeBytes = safeFileSize(dbPath);

        const stats = {
          feeds: feedCount?.count ?? 0,
          articles: articleCount?.count ?? 0,
          dbSizeBytes,
          dbPath,
        };

        output.result(stats, (data) => {
          const lines: string[] = [];
          lines.push('');
          lines.push(chalk.bold.cyan('Database Statistics'));
          lines.push(chalk.cyan('═'.repeat(40)));
          lines.push(`  Feeds: ${chalk.white(data.feeds.toString())}`);
          lines.push(`  Articles: ${chalk.white(data.articles.toString())}`);
          if (typeof data.dbSizeBytes === 'number') {
            lines.push(`  Size: ${chalk.white(formatBytes(data.dbSizeBytes))}`);
          }
          lines.push(`  Path: ${chalk.gray(sanitizeTerminalText(data.dbPath))}`);
          return lines.join('\n');
        });
      });
    });

  dbCmd
    .command('vacuum')
    .description('Vacuum database to reclaim space')
    .action(async () => {
      await runCommand(program, async (output) => {
        const opts = program.opts() as { database?: string };
        const dbPath = getDbPath(opts.database);
        const sizeBefore = safeFileSize(dbPath);

        const spinner = output.progress('Vacuuming database...');

        const db = getDb();
        await db.run(sql`VACUUM`);

        const sizeAfter = safeFileSize(dbPath);
        spinner.stop();

        const result: {
          vacuumed: true;
          dbPath: string;
          sizeBefore?: number;
          sizeAfter?: number;
        } = { vacuumed: true, dbPath };
        if (typeof sizeBefore === 'number') result.sizeBefore = sizeBefore;
        if (typeof sizeAfter === 'number') result.sizeAfter = sizeAfter;

        output.result(result, (data) => {
          if (typeof data.sizeBefore === 'number' && typeof data.sizeAfter === 'number') {
            const reclaimed = Math.max(0, data.sizeBefore - data.sizeAfter);
            return chalk.green(
              `Database vacuumed (${formatBytes(data.sizeBefore)} -> ${formatBytes(data.sizeAfter)}, reclaimed ${formatBytes(reclaimed)})`,
            );
          }
          return chalk.green('Database vacuumed');
        });
      });
    });

  dbCmd
    .command('cleanup')
    .description('Remove old cache entries (use --days 0 to clear all)')
    .option('--days <n>', 'Remove entries older than N days; 0 clears all entries', '30')
    .action(async (options: { days: string }) => {
      await runCommand(program, async (output) => {
        const days = requireValue(validateIntRange(options.days, 'Days', 0, Number.MAX_SAFE_INTEGER));

        const spinner = output.progress('Cleaning up...');

        const removed = cleanupFetchCache(days);

        spinner.stop();

        output.result({ removed, days }, (data) =>
          chalk.green(
            data.days === 0
              ? `Cleared ${data.removed} cache entries`
              : `Cleaned up ${data.removed} old cache entries (older than ${data.days} days)`,
          ),
        );
      });
    });
}