import 'dotenv/config';
import { createHash } from 'node:crypto';
import { readFile, readdir, stat } from 'node:fs/promises';
import { basename, dirname, extname, join } from 'node:path';
import { eq } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import { communityChannels, communityPosts, users } from '../src/database/schema';

/**
 * Publishes the finished analyses from the CK analysis hub as community posts.
 *
 *   HUB_DIR=".../19_CK_Analysehub/output/beitraege" \
 *   AUTHOR_EMAIL=office@ckinvest.at \
 *   npm run db:import-analysehub
 *
 * Add DRY_RUN=1 to see what would happen without writing anything.
 *
 * The hub already stores one folder per channel, so the folder name decides
 * where a post lands and a missing channel is created rather than skipped.
 *
 * Each post is keyed by its path inside the hub, and the file's SHA-256 decides
 * what happens on a re-run: unchanged files are left alone, revised files
 * update their post in place, new files are published. Nothing is ever deleted
 * — removing a file from the hub does not unpublish what customers may already
 * have read.
 *
 * The bodies are complete HTML documents with their own styling and embedded
 * images. They are stored verbatim and rendered inside a sandboxed frame, so
 * they look exactly as they were designed to.
 */

const DEFAULT_HUB = join(
  process.env.HOME ?? '',
  'Library/CloudStorage/OneDrive-CKInvest/19_CK_Analysehub/output/beitraege',
);

/**
 * Hub folder → community channel. Names and icons are only used when the
 * channel has to be created; an existing channel keeps whatever it has.
 */
const CHANNEL_MAP: Record<string, { id: string; name: string; icon: string; description: string }> = {
  'ck-daily': {
    id: 'ck-daily',
    name: 'CK Daily',
    icon: 'daily',
    description: 'Täglicher institutioneller Marktüberblick.',
  },
  'makro-marktpsychologie': {
    id: 'ck-macro',
    name: 'CK Macro',
    icon: 'macro',
    description: 'Fed, Inflation, Zinsen und Marktpsychologie.',
  },
  'charts-education': {
    id: 'ck-charts',
    name: 'CK Charts & Education',
    icon: 'chart',
    description: 'Chartanalysen, Methodik und Lerninhalte.',
  },
  deepdive: {
    id: 'ck-deepdive',
    name: 'CK DeepDive',
    icon: 'research',
    description: 'Ausführliche Coin- und Sektoranalysen.',
  },
  'market-data-news': {
    id: 'ck-marketdata',
    name: 'CK Market Data & News',
    icon: 'news',
    description: 'Marktdaten, Flows und Nachrichtenlage.',
  },
  'on-chain-metrics': {
    id: 'ck-onchain',
    name: 'CK On-Chain & Metrics',
    icon: 'onchain',
    description: 'On-Chain-Daten und Netzwerkkennzahlen.',
  },
  'ck-trading-info': {
    id: 'ck-trading-info',
    name: 'CK Trading Info',
    icon: 'announcement',
    description: 'Hinweise und Informationen rund um CK Trading.',
  },
};

const hubDir = process.env.HUB_DIR?.trim() || DEFAULT_HUB;
const authorEmail = process.env.AUTHOR_EMAIL?.trim().toLowerCase();
const dryRun = process.env.DRY_RUN === '1';
const forceMetadata = process.env.FORCE_METADATA === '1';

type Variant = 'FULL' | 'SHORT';

/** `CK_DAILY_KURZ_2026-08-07.html` is the short read of that day's analysis. */
function variantFromName(name: string): Variant {
  return /(^|[_-])KURZ([_-]|\.)/i.test(name) ? 'SHORT' : 'FULL';
}

interface Extracted {
  title: string;
  excerpt: string | null;
  publishedAt: Date | null;
  displayDate: string | null;
}

/** `CK_DAILY_2026-08-07.html` → 2026-08-07. */
function dateFromName(name: string): Date | null {
  const match = /(\d{4})-(\d{2})-(\d{2})/.exec(name);
  if (!match) return null;
  // Midday UTC so the date cannot slip across a day boundary in any timezone
  // the reader happens to be in.
  return new Date(`${match[1]}-${match[2]}-${match[3]}T12:00:00Z`);
}

function stripTags(value: string): string {
  return value
    .replace(/<[^>]+>/g, ' ')
    .replace(/&nbsp;/g, ' ')
    .replace(/&amp;/g, '&')
    .replace(/&lt;/g, '<')
    .replace(/&gt;/g, '>')
    .replace(/&quot;/g, '"')
    .replace(/&#0?39;/g, "'")
    .replace(/\s+/g, ' ')
    .trim();
}

/**
 * Title, teaser and date, in the order a human would look for them: the <h1>
 * the reader actually sees first, then the document <title>, then the filename.
 */
function extract(html: string, fileName: string): Extracted {
  const h1 = /<h1[^>]*>([\s\S]*?)<\/h1>/i.exec(html)?.[1];
  const docTitle = /<title[^>]*>([\s\S]*?)<\/title>/i.exec(html)?.[1];

  let title = stripTags(h1 ?? docTitle ?? '');
  // The exported documents carry a "· CK Capital" suffix meant for the browser
  // tab; inside the portal every post is a CK post.
  title = title.replace(/\s*·\s*CK Capital\s*$/i, '').trim();
  if (!title) title = basename(fileName, extname(fileName)).replace(/[_-]+/g, ' ');

  // The variant is a field on the post, not a suffix on the headline: both reads
  // of an analysis carry the same title, and the badge tells them apart.
  title = title.replace(/\s*\((Kurzfassung|Vollversion)\)\s*$/i, '').trim();

  const description = /<meta[^>]+name=["']description["'][^>]+content=["']([^"']+)["']/i.exec(html)?.[1];
  let excerpt = description ? stripTags(description) : null;
  if (!excerpt) {
    // First paragraph with actual prose in it, not a spacer or a caption.
    for (const match of html.matchAll(/<p[^>]*>([\s\S]*?)<\/p>/gi)) {
      const text = stripTags(match[1]);
      if (text.length >= 60) {
        excerpt = text;
        break;
      }
    }
  }
  if (excerpt && excerpt.length > 280) excerpt = `${excerpt.slice(0, 277).trimEnd()}…`;

  const published = dateFromName(fileName);
  return {
    title,
    excerpt,
    publishedAt: published,
    displayDate: published
      ? published.toLocaleDateString('de-DE', { day: '2-digit', month: 'long', year: 'numeric' })
      : null,
  };
}

/**
 * Cover image for a post, searched by its date.
 *
 * The hub does not keep covers in one place: the current ones sit next to the
 * post, older ones were filed into `archiv/`, and the day being worked on is
 * still in the `output/` root next to its documents. Searching all three is
 * what makes the long and the short read of a day share the same image, which
 * is how they are meant to appear.
 */
async function findCover(
  directories: string[],
  fileName: string,
  cache: Map<string, string | null>,
): Promise<string | null> {
  const date = /(\d{4}-\d{2}-\d{2})/.exec(fileName)?.[1];
  if (!date) return null;
  const cached = cache.get(date);
  if (cached !== undefined) return cached;

  for (const directory of directories) {
    // Dotfiles must go: macOS ships an AppleDouble companion (`._name.jpg`)
    // beside every file it copies, and that companion matches the same name
    // pattern. Picking one up stores 400 bytes of resource fork as the cover,
    // which the browser then cannot decode — a broken image where the chart
    // should be.
    const files = (await readdir(directory).catch(() => [] as string[])).filter(
      (name) => !name.startsWith('.'),
    );
    const match = files.find(
      (candidate) =>
        candidate.includes(date) &&
        /titelbild/i.test(candidate) &&
        /\.(jpe?g|png|webp)$/i.test(candidate),
    );
    if (!match) continue;

    const buffer = await readFile(join(directory, match));
    const extension = extname(match).toLowerCase();
    const mime =
      extension === '.png' ? 'image/png' : extension === '.webp' ? 'image/webp' : 'image/jpeg';
    const dataUrl = `data:${mime};base64,${buffer.toString('base64')}`;
    cache.set(date, dataUrl);
    return dataUrl;
  }

  cache.set(date, null);
  return null;
}

async function main(): Promise<void> {
  const databaseUrl = process.env.DATABASE_URL;
  if (!databaseUrl) throw new Error('DATABASE_URL is required.');

  const hubStat = await stat(hubDir).catch(() => null);
  if (!hubStat?.isDirectory()) throw new Error(`HUB_DIR is not a directory: ${hubDir}`);

  const pool = new Pool({
    connectionString: databaseUrl,
    ssl:
      process.env.DATABASE_SSL === 'true'
        ? { rejectUnauthorized: process.env.DATABASE_SSL_REJECT_UNAUTHORIZED !== 'false' }
        : false,
  });
  const db = drizzle(pool);

  try {
    let authorId: string | null = null;
    let authorName: string | null = null;
    if (authorEmail) {
      const [author] = await db
        .select({ id: users.id, displayName: users.displayName, email: users.email })
        .from(users)
        .where(eq(users.email, authorEmail))
        .limit(1);
      if (!author) throw new Error(`No account found for ${authorEmail}.`);
      authorId = author.id;
      authorName = author.displayName ?? author.email;
    }

    const counts = { created: 0, updated: 0, unchanged: 0, channels: 0, skipped: 0 };

    const folders = (await readdir(hubDir, { withFileTypes: true }))
      .filter((entry) => entry.isDirectory())
      .map((entry) => entry.name);

    /**
     * Where finished work lives. `beitraege/<kanal>/` is the filed archive; the
     * day currently being worked on is still in the hub's parent `output/`
     * folder, so the newest CK Daily would otherwise never reach the portal.
     * Only CK_DAILY documents are taken from there — the same folder also holds
     * internal briefings that are not customer material.
     */
    const sources: Array<{ folder: string; directory: string; filter: (name: string) => boolean }> =
      folders
        .filter((folder) => CHANNEL_MAP[folder])
        .map((folder) => ({
          folder,
          directory: join(hubDir, folder),
          filter: (name: string) => /\.html?$/i.test(name),
        }));

    const outputRoot = dirname(hubDir);
    sources.push({
      folder: 'ck-daily',
      directory: outputRoot,
      filter: (name: string) => /^CK_DAILY(_KURZ)?_\d{4}-\d{2}-\d{2}\.html?$/i.test(name),
    });

    // Covers are shared by every post of the same date — the long and the short
    // read of one analysis are meant to look like one publication.
    const coverDirectories = [
      ...folders.map((folder) => join(hubDir, folder)),
      outputRoot,
      join(outputRoot, 'archiv'),
    ];
    const coverCache = new Map<string, string | null>();

    for (const source of sources) {
      const mapping = CHANNEL_MAP[source.folder];
      if (!mapping) continue;

      const files = (await readdir(source.directory).catch(() => [] as string[])).filter(
        (name) => !name.startsWith('.'),
      );
      const documents = files.filter(source.filter).sort();
      if (!documents.length) continue;

      const [existingChannel] = await db
        .select({ id: communityChannels.id })
        .from(communityChannels)
        .where(eq(communityChannels.id, mapping.id))
        .limit(1);
      if (!existingChannel) {
        counts.channels += 1;
        if (!dryRun) {
          await db
            .insert(communityChannels)
            .values({
              id: mapping.id,
              name: mapping.name,
              icon: mapping.icon,
              description: mapping.description,
              visibility: 'MEMBERS',
              position: Object.keys(CHANNEL_MAP).indexOf(source.folder),
            })
            .onConflictDoNothing({ target: communityChannels.id });
        }
      }

      for (const fileName of documents) {
        const sourcePath = `${source.folder}/${fileName}`;
        const html = await readFile(join(source.directory, fileName), 'utf8');
        const hash = createHash('sha256').update(html).digest('hex');

        const [existing] = await db
          .select({ id: communityPosts.id, sourceHash: communityPosts.sourceHash })
          .from(communityPosts)
          .where(eq(communityPosts.sourcePath, sourcePath))
          .limit(1);

        // FORCE_METADATA re-derives title, teaser, date, variant and cover for
        // posts whose file has not changed. Needed when the extraction rules
        // themselves improve — the file is identical, but what we make of it is
        // not.
        if (existing && existing.sourceHash === hash && !forceMetadata) {
          counts.unchanged += 1;
          continue;
        }

        const meta = extract(html, fileName);
        const variant = variantFromName(fileName);
        const cover = await findCover(coverDirectories, fileName, coverCache);

        if (existing) {
          counts.updated += 1;
          if (dryRun) continue;
          await db
            .update(communityPosts)
            .set({
              title: meta.title,
              excerpt: meta.excerpt,
              body: html,
              variant,
              publishedAt: meta.publishedAt ?? undefined,
              legacyDate: meta.displayDate,
              // Keep an existing cover when this revision ships without one:
              // losing the image would be a visible regression in the feed.
              ...(cover ? { cover } : {}),
              sourceHash: hash,
              updatedAt: new Date(),
            })
            .where(eq(communityPosts.id, existing.id));
          continue;
        }

        counts.created += 1;
        if (dryRun) continue;
        await db.insert(communityPosts).values({
          channelId: mapping.id,
          status: 'PUBLISHED',
          variant,
          title: meta.title,
          excerpt: meta.excerpt,
          body: html,
          cover,
          authorId,
          authorName,
          publishedAt: meta.publishedAt ?? new Date(),
          legacyDate: meta.displayDate,
          sourcePath,
          sourceHash: hash,
        });
      }
    }

    console.log(
      `${dryRun ? 'Would publish' : 'Published'} from ${hubDir}:\n` +
        `  ${counts.created} new, ${counts.updated} revised, ${counts.unchanged} unchanged` +
        `${counts.channels ? `, ${counts.channels} channel(s) created` : ''}.`,
    );
    if (dryRun) console.log('DRY_RUN=1 — nothing was written.');
  } finally {
    await pool.end();
  }
}

main().catch((error: unknown) => {
  console.error(error instanceof Error ? error.message : error);
  process.exit(1);
});