import 'dotenv/config';
import { createHash } from 'node:crypto';
import { and, eq, sql } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import {
  appState,
  communityChannels,
  communityChatMessages,
  communityChats,
  communityPostComments,
  communityPosts,
  communitySettings,
  users,
} from '../src/database/schema';

/**
 * One-time import of Community & Research from the per-user `app_state` blob
 * into the shared community tables.
 *
 * Until now every account carried its own private copy of the portal, so the
 * content that matters is whatever the administrator's account holds. This
 * script reads exactly that blob and replays it into tables everyone can see.
 *
 *   OWNER_EMAIL=office@ckinvest.at npm run db:import-community
 *   OWNER_EMAIL=office@ckinvest.at DRY_RUN=1 npm run db:import-community
 *
 * Repeatable by design: every row it writes carries the id it had inside the
 * blob (`legacy_id`), and a second run recognises those instead of inserting
 * duplicates. It never deletes and never overwrites content that was edited
 * after the first import — the blob is treated as a source, not as the truth.
 */

const STATE_KEY = 'ck_community_research_v10';

function requiredEnvironmentValue(name: string): string {
  const value = process.env[name]?.trim();
  if (!value) throw new Error(`${name} is required.`);
  return value;
}

const databaseUrl = requiredEnvironmentValue('DATABASE_URL');
const ownerEmail = requiredEnvironmentValue('OWNER_EMAIL').toLowerCase();
const dryRun = process.env.DRY_RUN === '1';

interface LegacyComment {
  id?: string;
  name?: string;
  text?: string;
}

interface LegacyPost {
  id?: string;
  channel?: string;
  status?: string;
  title?: string;
  excerpt?: string;
  date?: string;
  author?: string;
  cover?: string;
  body?: string;
  views?: number;
  comments?: LegacyComment[];
}

interface LegacyState {
  settings?: Record<string, unknown>;
  channels?: Array<{ id?: string; name?: string; icon?: string; desc?: string; access?: string }>;
  members?: Array<{ name?: string; email?: string }>;
  posts?: LegacyPost[];
  chats?: Array<{
    id?: string;
    name?: string;
    desc?: string;
    createdBy?: string;
    messages?: Array<{ name?: string; text?: string; date?: string }>;
  }>;
}

/**
 * The legacy store writes `JSON.stringify(state)` as the value, so the jsonb
 * column holds a JSON *string*, not an object. Older rows may hold the object
 * directly — accept both rather than guessing which one this installation has.
 */
function parseState(value: unknown): LegacyState {
  if (value == null) throw new Error('The community state is empty.');
  if (typeof value === 'string') return JSON.parse(value) as LegacyState;
  return value as LegacyState;
}

const GERMAN_MONTHS = [
  'januar',
  'februar',
  'märz',
  'april',
  'mai',
  'juni',
  'juli',
  'august',
  'september',
  'oktober',
  'november',
  'dezember',
];

/**
 * The legacy portal stored dates as display strings ("11. Mai 2026"). Taking
 * the import time instead would stamp every old post as published today and
 * push years of archive above the current analysis in the feed.
 */
function parseGermanDate(value: string | undefined): Date | null {
  if (!value) return null;
  const match = /(\d{1,2})\.\s*([A-Za-zäöüÄÖÜ]+)\s*(\d{4})/.exec(value);
  if (!match) return null;
  const month = GERMAN_MONTHS.indexOf(match[2].toLowerCase());
  if (month < 0) return null;
  // Midday UTC, so the date cannot slip a day in either direction.
  const date = new Date(Date.UTC(Number(match[3]), month, Number(match[1]), 12));
  return Number.isNaN(date.getTime()) ? null : date;
}

/** Stable synthetic id for legacy rows that never had one (chat messages). */
function syntheticId(prefix: string, ...parts: string[]): string {
  const digest = createHash('sha256').update(parts.join(' ')).digest('hex');
  return `${prefix}-${digest.slice(0, 24)}`;
}

async function main(): Promise<void> {
  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 {
    const [owner] = await db
      .select({ id: users.id, email: users.email })
      .from(users)
      .where(eq(users.email, ownerEmail))
      .limit(1);
    if (!owner) throw new Error(`No account found for ${ownerEmail}.`);

    const [row] = await db
      .select({ value: appState.value })
      .from(appState)
      .where(and(eq(appState.ownerId, owner.id), eq(appState.key, STATE_KEY)))
      .limit(1);
    if (!row) throw new Error(`${ownerEmail} has no ${STATE_KEY} state to import.`);

    const state = parseState(row.value);

    // Legacy content records an author *name*; accounts are keyed by email.
    // The blob's own member list is the only bridge between the two — and it is
    // not a clean one: the same person can appear several times under different
    // addresses (an admin account plus a pending reader invitation). Prefer the
    // address that actually has an account, otherwise the byline of a real
    // author silently resolves to nobody.
    const accounts = await db.select({ id: users.id, email: users.email }).from(users);
    const idByEmail = new Map(accounts.map((account) => [account.email.toLowerCase(), account.id]));

    const emailsByName = new Map<string, string[]>();
    for (const member of state.members ?? []) {
      if (!member.name || !member.email) continue;
      const list = emailsByName.get(member.name) ?? [];
      list.push(member.email.toLowerCase());
      emailsByName.set(member.name, list);
    }
    const resolveAuthor = (name?: string): string | null => {
      if (!name) return null;
      for (const email of emailsByName.get(name) ?? []) {
        const id = idByEmail.get(email);
        if (id) return id;
      }
      return null;
    };

    const counts = { channels: 0, posts: 0, comments: 0, chats: 0, messages: 0, settings: 0 };

    // ── Channels ────────────────────────────────────────────────────────────
    const channels = state.channels ?? [];
    for (const [position, channel] of channels.entries()) {
      if (!channel.id || !channel.name) continue;
      counts.channels += 1;
      if (dryRun) continue;
      await db
        .insert(communityChannels)
        .values({
          id: channel.id,
          name: channel.name,
          icon: channel.icon ?? null,
          description: channel.desc ?? null,
          visibility: channel.access === 'admin' ? 'ADMINS' : 'MEMBERS',
          position,
        })
        .onConflictDoNothing({ target: communityChannels.id });
    }

    // ── Posts and their comments ────────────────────────────────────────────
    for (const post of state.posts ?? []) {
      if (!post.id || !post.title) continue;
      const channelId = post.channel && channels.some((c) => c.id === post.channel)
        ? post.channel
        : channels[0]?.id;
      if (!channelId) continue;

      counts.posts += 1;
      if (dryRun) {
        counts.comments += (post.comments ?? []).length;
        continue;
      }

      const [inserted] = await db
        .insert(communityPosts)
        .values({
          legacyId: post.id,
          channelId,
          status: post.status === 'published' ? 'PUBLISHED' : 'DRAFT',
          title: post.title,
          excerpt: post.excerpt ?? null,
          body: post.body ?? '',
          cover: post.cover ?? null,
          authorId: resolveAuthor(post.author),
          authorName: post.author ?? null,
          // The legacy date is a display string ("11. Mai 2026") with no time
          // and no timezone; parsing it would invent precision. It is kept
          // verbatim and publishedAt is only set to order the feed.
          legacyDate: post.date ?? null,
          publishedAt:
            post.status === 'published' ? (parseGermanDate(post.date) ?? new Date()) : null,
          views: Number(post.views ?? 0),
        })
        // On a re-run this fills in an author link that could not be resolved
        // the first time — because the account did not exist yet — without
        // touching anything that was edited since. DO UPDATE also returns the
        // existing row, so a repeat run still finds the post for its comments.
        .onConflictDoUpdate({
          target: communityPosts.legacyId,
          set: { authorId: sql`coalesce(${communityPosts.authorId}, excluded.author_id)` },
        })
        .returning({ id: communityPosts.id });

      const postId = inserted?.id;
      if (!postId) continue;

      for (const [index, comment] of (post.comments ?? []).entries()) {
        if (!comment.text) continue;
        counts.comments += 1;
        await db
          .insert(communityPostComments)
          .values({
            legacyId: comment.id ?? syntheticId('c', post.id, String(index), comment.text),
            postId,
            authorId: resolveAuthor(comment.name),
            authorName: comment.name ?? null,
            body: comment.text,
          })
          .onConflictDoUpdate({
            target: communityPostComments.legacyId,
            set: {
              authorId: sql`coalesce(${communityPostComments.authorId}, excluded.author_id)`,
            },
          });
      }
    }

    // ── Chats and messages ──────────────────────────────────────────────────
    for (const chat of state.chats ?? []) {
      if (!chat.id || !chat.name) continue;
      counts.chats += 1;
      if (!dryRun) {
        const createdBy = chat.createdBy ? (idByEmail.get(chat.createdBy.toLowerCase()) ?? null) : null;
        await db
          .insert(communityChats)
          .values({
            id: chat.id,
            name: chat.name,
            description: chat.desc ?? null,
            createdById: createdBy,
          })
          .onConflictDoNothing({ target: communityChats.id });
      }

      for (const [index, message] of (chat.messages ?? []).entries()) {
        if (!message.text) continue;
        counts.messages += 1;
        if (dryRun) continue;
        await db
          .insert(communityChatMessages)
          .values({
            // Legacy messages carry no id at all, so one is derived from the
            // content. Identical text posted twice collapses into one row —
            // acceptable, and far better than duplicating the whole history on
            // every re-run.
            legacyId: syntheticId('m', chat.id, String(index), message.text),
            chatId: chat.id,
            authorId: resolveAuthor(message.name),
            authorName: message.name ?? null,
            body: message.text,
          })
          .onConflictDoUpdate({
            target: communityChatMessages.legacyId,
            set: {
              authorId: sql`coalesce(${communityChatMessages.authorId}, excluded.author_id)`,
            },
          });
      }
    }

    // ── Settings ────────────────────────────────────────────────────────────
    for (const [key, value] of Object.entries(state.settings ?? {})) {
      counts.settings += 1;
      if (dryRun) continue;
      await db
        .insert(communitySettings)
        .values({ key, value: value as never })
        .onConflictDoNothing({ target: communitySettings.key });
    }

    console.log(
      `${dryRun ? 'Would import' : 'Imported'} from ${ownerEmail}: ` +
        `${counts.channels} channels, ${counts.posts} posts, ${counts.comments} comments, ` +
        `${counts.chats} chats, ${counts.messages} messages, ${counts.settings} settings.`,
    );
    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);
});