/**
 * DB persistence layer — maps Facebook data structures to the cultguard PostgreSQL schema.
 */

import type pg from "pg";
import { randomUUID } from "node:crypto";
import {
  withTransaction,
  ensureInvestigation,
  now,
} from "../../db.js";
import type {
  FbPageData,
  FbPostData,
  FbCommentData,
  FbReactionData,
  FbAdData,
  FbTransparencyData,
} from "../../types.js";

// ─── Page → entities ────────────────────────────────────────────────────────

export async function upsertPage(
  page: FbPageData,
  investigationId: string,
): Promise<string> {
  return withTransaction(async (client) => {
    await ensureInvestigation(client, investigationId);

    const entityId = `fb_page_${page.page_id}`;
    const collectedAt = now();

    await client.query(
      `INSERT INTO entities (id, investigation_id, type, name, url, platform, country, collected_at, confidence, is_sock, raw_json)
       VALUES ($1, $2, 'page', $3, $4, 'facebook', NULL, $5, 100, false, $6)
       ON CONFLICT (id) DO UPDATE SET
         name = COALESCE(EXCLUDED.name, entities.name),
         url = COALESCE(EXCLUDED.url, entities.url),
         collected_at = EXCLUDED.collected_at,
         raw_json = COALESCE(EXCLUDED.raw_json, entities.raw_json)`,
      [
        entityId,
        investigationId,
        page.name,
        page.website ?? `https://facebook.com/${page.page_id}`,
        collectedAt,
        buildPageRaw(page),
      ],
    );

    // Store identifiers (phone, email) if present
    if (page.phone) {
      await upsertIdentifier(client, investigationId, entityId, "phone", page.phone, collectedAt);
    }
    if (page.email) {
      await upsertIdentifier(client, investigationId, entityId, "email", page.email, collectedAt);
    }

    return entityId;
  });
}

function buildPageRaw(page: FbPageData): Record<string, unknown> {
  return {
    fb_page_id: page.page_id,
    category: page.category,
    sub_category: page.sub_category,
    about: page.about,
    description: page.description,
    follower_count: page.follower_count,
    like_count: page.like_count,
    checkin_count: page.checkin_count,
    verified: page.verified,
    phone: page.phone,
    email: page.email,
    address: page.address,
    hours: page.hours,
    price_range: page.price_range,
    profile_photo_url: page.profile_photo_url,
    cover_photo_url: page.cover_photo_url,
    creation_date: page.creation_date,
    ...(page.raw ?? {}),
  };
}

// ─── Post → content ─────────────────────────────────────────────────────────

export async function upsertPost(
  post: FbPostData,
  pageEntityId: string,
  investigationId: string,
): Promise<string> {
  return withTransaction(async (client) => {
    await ensureInvestigation(client, investigationId);

    const contentId = `fb_post_${post.post_id}`;
    const collectedAt = now();

    await client.query(
      `INSERT INTO content (id, investigation_id, entity_id, type, text, published_at, collected_at, url, likes, shares, comments, reactions, raw_json)
       VALUES ($1, $2, $3, 'post', $4, $5, $6, $7, $8, $9, $10, $11, $12)
       ON CONFLICT (id) DO UPDATE SET
         text = COALESCE(EXCLUDED.text, content.text),
         published_at = COALESCE(EXCLUDED.published_at, content.published_at),
         collected_at = EXCLUDED.collected_at,
         likes = COALESCE(EXCLUDED.likes, content.likes),
         shares = COALESCE(EXCLUDED.shares, content.shares),
         comments = COALESCE(EXCLUDED.comments, content.comments),
         reactions = COALESCE(EXCLUDED.reactions, content.reactions),
         raw_json = COALESCE(EXCLUDED.raw_json, content.raw_json)`,
      [
        contentId,
        investigationId,
        pageEntityId,
        post.text ?? null,
        post.published_at ?? null,
        collectedAt,
        `https://facebook.com/${post.post_id}`,
        totalReactions(post.reaction_counts),
        post.share_count ?? null,
        post.comment_count ?? null,
        post.reaction_counts ? JSON.stringify(post.reaction_counts) : null,
        post.raw ? JSON.stringify(post.raw) : null,
      ],
    );

    return contentId;
  });
}

function totalReactions(
  counts: Record<string, number> | undefined,
): number | null {
  if (!counts) return null;
  return Object.values(counts).reduce((a, b) => a + b, 0);
}

// ─── Comment → content + entities + relationships ───────────────────────────

export async function upsertComment(
  comment: FbCommentData,
  investigationId: string,
): Promise<void> {
  await withTransaction(async (client) => {
    await ensureInvestigation(client, investigationId);
    await upsertSingleComment(client, comment, investigationId);
  });
}

async function upsertSingleComment(
  client: pg.PoolClient,
  comment: FbCommentData,
  investigationId: string,
): Promise<void> {
  const collectedAt = now();

  // Upsert commenter as a profile entity
  const commenterEntityId = `fb_profile_${comment.author_id}`;
  await client.query(
    `INSERT INTO entities (id, investigation_id, type, name, url, platform, collected_at, confidence, is_sock)
     VALUES ($1, $2, 'profile', $3, $4, 'facebook', $5, 50, false)
     ON CONFLICT (id) DO UPDATE SET
       name = COALESCE(EXCLUDED.name, entities.name),
       url = COALESCE(EXCLUDED.url, entities.url),
       collected_at = EXCLUDED.collected_at`,
    [
      commenterEntityId,
      investigationId,
      comment.author_name,
      comment.author_url ?? `https://facebook.com/${comment.author_id}`,
      collectedAt,
    ],
  );

  // Upsert the comment as content
  const contentId = `fb_comment_${comment.comment_id}`;
  await client.query(
    `INSERT INTO content (id, investigation_id, entity_id, type, text, published_at, collected_at, likes, raw_json)
     VALUES ($1, $2, $3, 'comment', $4, $5, $6, $7, $8)
     ON CONFLICT (id) DO UPDATE SET
       text = COALESCE(EXCLUDED.text, content.text),
       published_at = COALESCE(EXCLUDED.published_at, content.published_at),
       collected_at = EXCLUDED.collected_at,
       likes = COALESCE(EXCLUDED.likes, content.likes),
       raw_json = COALESCE(EXCLUDED.raw_json, content.raw_json)`,
    [
      contentId,
      investigationId,
      commenterEntityId,
      comment.text,
      comment.published_at ?? null,
      collectedAt,
      comment.reaction_count ?? null,
      comment.raw ? JSON.stringify(comment.raw) : null,
    ],
  );

  // Note: comment→post link is implicit via the comment content row's entity_id (commenter)
  // and the post_id stored in raw_json. We do not insert into relationships because
  // relationships.target_id must reference entities(id), not content IDs.

  // Recurse into replies
  if (comment.replies && comment.replies.length > 0) {
    for (const reply of comment.replies) {
      await upsertSingleComment(client, reply, investigationId);
    }
  }
}

// ─── Reactions → content.reactions JSONB ────────────────────────────────────

export async function storeReactions(
  reactions: FbReactionData[],
  contentId: string,
  investigationId: string,
): Promise<void> {
  if (reactions.length === 0) return;

  // Aggregate counts by type
  const counts: Record<string, number> = {};
  for (const r of reactions) {
    counts[r.reaction_type] = (counts[r.reaction_type] ?? 0) + 1;
  }

  await withTransaction(async (client) => {
    await ensureInvestigation(client, investigationId);

    // Update the content row's reactions JSONB
    await client.query(
      `UPDATE content
       SET reactions = $1::jsonb,
           collected_at = $2
       WHERE id = $3 AND investigation_id = $4`,
      [JSON.stringify(counts), now(), contentId, investigationId],
    );

    // Optionally store individual reactor profiles as entities
    for (const r of reactions) {
      const entityId = `fb_profile_${r.user_id}`;
      await client.query(
        `INSERT INTO entities (id, investigation_id, type, name, url, platform, collected_at, confidence, is_sock)
         VALUES ($1, $2, 'profile', $3, $4, 'facebook', $5, 30, false)
         ON CONFLICT (id) DO UPDATE SET
           name = COALESCE(EXCLUDED.name, entities.name),
           url = COALESCE(EXCLUDED.url, entities.url)`,
        [
          entityId,
          investigationId,
          r.user_name,
          r.user_url ?? `https://facebook.com/${r.user_id}`,
          now(),
        ],
      );
    }
  });
}

// ─── Ad → content ───────────────────────────────────────────────────────────

export async function storeAd(
  ad: FbAdData,
  investigationId: string,
): Promise<void> {
  await withTransaction(async (client) => {
    await ensureInvestigation(client, investigationId);

    const contentId = `fb_ad_${ad.ad_id}`;
    const pageEntityId = ad.page_id ? `fb_page_${ad.page_id}` : null;
    const collectedAt = now();

    await client.query(
      `INSERT INTO content (
         id, investigation_id, entity_id, type, text,
         collected_at, ad_spend_min, ad_spend_max, ad_impressions,
         ad_region_dist, ad_start_at, ad_end_at, raw_json
       ) VALUES ($1, $2, $3, 'ad', $4, $5, $6, $7, $8, $9, $10, $11, $12)
       ON CONFLICT (id) DO UPDATE SET
         text = COALESCE(EXCLUDED.text, content.text),
         collected_at = EXCLUDED.collected_at,
         ad_spend_min = COALESCE(EXCLUDED.ad_spend_min, content.ad_spend_min),
         ad_spend_max = COALESCE(EXCLUDED.ad_spend_max, content.ad_spend_max),
         ad_impressions = COALESCE(EXCLUDED.ad_impressions, content.ad_impressions),
         ad_region_dist = COALESCE(EXCLUDED.ad_region_dist, content.ad_region_dist),
         ad_start_at = COALESCE(EXCLUDED.ad_start_at, content.ad_start_at),
         ad_end_at = COALESCE(EXCLUDED.ad_end_at, content.ad_end_at),
         raw_json = COALESCE(EXCLUDED.raw_json, content.raw_json)`,
      [
        contentId,
        investigationId,
        pageEntityId,
        ad.creative_text ?? null,
        collectedAt,
        ad.spend_min ?? null,
        ad.spend_max ?? null,
        ad.impressions_max ?? null,
        ad.region_distribution ? JSON.stringify(ad.region_distribution) : null,
        ad.start_date ?? null,
        ad.end_date ?? null,
        ad.raw ? JSON.stringify(ad.raw) : null,
      ],
    );
  });
}

// ─── Transparency → entities.raw_json sub-object ────────────────────────────

export async function storeTransparency(
  data: FbTransparencyData,
  pageEntityId: string,
  investigationId: string,
): Promise<void> {
  await withTransaction(async (client) => {
    await ensureInvestigation(client, investigationId);

    // Merge transparency data into the entity's raw_json
    await client.query(
      `UPDATE entities
       SET raw_json = COALESCE(raw_json, '{}'::jsonb) || jsonb_build_object('transparency', $1::jsonb),
           collected_at = $2
       WHERE id = $3 AND investigation_id = $4`,
      [
        JSON.stringify(data),
        now(),
        pageEntityId,
        investigationId,
      ],
    );
  });
}

// ─── Helpers ────────────────────────────────────────────────────────────────

async function upsertIdentifier(
  client: pg.PoolClient,
  investigationId: string,
  entityId: string,
  type: "phone" | "email",
  value: string,
  collectedAt: string,
): Promise<void> {
  await client.query(
    `INSERT INTO identifiers (investigation_id, entity_id, type, value, collected_at)
     VALUES ($1, $2, $3, $4, $5)
     ON CONFLICT (entity_id, type, value) DO NOTHING`,
    [investigationId, entityId, type, value, collectedAt],
  );
}