import { randomUUID } from "crypto";
import type { RowDataPacket } from "mysql2/promise";
import type { GeneratedPost, GeneratedPostStatus } from "@/types/workflow";
import { fromMysqlDateTime, toMysqlDateTime } from "@/lib/db/datetime";
import { execute, jsonString, parseJson, queryRows, type SqlParam } from "@/lib/db/pool";

interface GeneratedPostRow extends RowDataPacket {
  id: string;
  user_id: string | null;
  project_id: string;
  project_name: string | null;
  context_name: string | null;
  sequence_post_id: string | null;
  sequence_index: number | null;
  parent_post_id: string | null;
  version: number;
  title: string | null;
  caption: string;
  image_url: string | null;
  image_prompt: string | null;
  image_approved: number;
  image_provider: string | null;
  image_provider_chain: string | null;
  image_warning: string | null;
  video_url: string | null;
  video_prompt: string | null;
  video_storyboard: string | null;
  video_status: string | null;
  video_duration_seconds: number | null;
  video_resolution: string | null;
  video_warning: string | null;
  platforms_json: string | null;
  status: GeneratedPostStatus;
  provider: string | null;
  model: string | null;
  context_snapshot: string | null;
  engagement_score: number | null;
  ai_cost_usd: string | number | null;
  ai_usage_json: string | null;
  quality_report_json?: string | null;
  media_asset_id?: string | null;
  banner_layout_id?: string | null;
  regeneration_count?: number | null;
  platform_permalinks_json: string | null;
  published_at: string | null;
  created_at: string;
}

function mapRow(row: GeneratedPostRow): GeneratedPost {
  return {
    id: row.id,
    userId: row.user_id ?? undefined,
    projectId: row.project_id,
    projectName: row.project_name ?? "",
    contextName: row.context_name ?? "",
    sequencePostId: row.sequence_post_id ?? "",
    sequenceIndex: row.sequence_index ?? 0,
    parentPostId: row.parent_post_id,
    version: row.version ?? 1,
    title: row.title ?? "",
    caption: row.caption,
    imageUrl: row.image_url,
    imagePrompt: row.image_prompt ?? "",
    imageApproved: Boolean(row.image_approved),
    imageProvider: row.image_provider ?? undefined,
    imageProviderChain: row.image_provider_chain ?? undefined,
    imageWarning: row.image_warning,
    videoUrl: row.video_url,
    videoPrompt: row.video_prompt ?? undefined,
    videoStoryboard: row.video_storyboard ?? undefined,
    videoStatus: (row.video_status as GeneratedPost["videoStatus"]) ?? undefined,
    videoDurationSeconds: row.video_duration_seconds ?? undefined,
    videoResolution: row.video_resolution ?? undefined,
    videoWarning: row.video_warning,
    platforms: parseJson<string[]>(row.platforms_json, []),
    status: row.status,
    provider: row.provider ?? "",
    model: row.model ?? "",
    contextSnapshot: row.context_snapshot ?? "",
    engagementScore: row.engagement_score,
    aiCostUsd: row.ai_cost_usd == null ? undefined : Number(row.ai_cost_usd),
    aiUsage: parseJson(row.ai_usage_json, undefined),
    qualityReport: parseJson(row.quality_report_json ?? null, undefined),
    mediaAssetId: row.media_asset_id ?? undefined,
    bannerLayoutId: (row.banner_layout_id as GeneratedPost["bannerLayoutId"]) ?? undefined,
    regenerationCount: row.regeneration_count ?? undefined,
    createdAt: fromMysqlDateTime(row.created_at) ?? new Date().toISOString(),
    publishedAt: fromMysqlDateTime(row.published_at) ?? undefined,
    platformPermalinks: parseJson(row.platform_permalinks_json, undefined),
  };
}

export async function listGeneratedPostsFromDb(input: {
  projectId?: string;
  projectName?: string;
  userId?: string;
}): Promise<GeneratedPost[]> {
  const clauses: string[] = [];
  const params: SqlParam[] = [];

  if (input.userId) {
    clauses.push("(user_id IS NULL OR user_id = ?)");
    params.push(input.userId);
  }
  if (input.projectId) {
    clauses.push("(project_id = ? OR (project_name = ? AND ? IS NOT NULL))");
    params.push(input.projectId, input.projectName ?? null, input.projectName ?? null);
  } else if (input.projectName) {
    clauses.push("project_name = ?");
    params.push(input.projectName);
  }

  const where = clauses.length ? `WHERE ${clauses.join(" AND ")}` : "";
  const rows = await queryRows<GeneratedPostRow[]>(
    `SELECT * FROM generated_posts ${where} ORDER BY created_at DESC`,
    params,
  );
  return rows.map(mapRow);
}

export async function getGeneratedPostFromDb(id: string): Promise<GeneratedPost | null> {
  const rows = await queryRows<GeneratedPostRow[]>(
    "SELECT * FROM generated_posts WHERE id = ? LIMIT 1",
    [id],
  );
  return rows[0] ? mapRow(rows[0]) : null;
}

export async function saveGeneratedPostToDb(post: GeneratedPost): Promise<GeneratedPost> {
  const coreParams: SqlParam[] = [
    post.id,
    post.userId ?? null,
    post.projectId,
    post.projectName || null,
    post.contextName || null,
    post.sequencePostId || null,
    post.sequenceIndex ?? null,
    post.parentPostId ?? null,
    post.version ?? 1,
    post.title || null,
    post.caption,
    post.imageUrl,
    post.imagePrompt || null,
    post.imageApproved ? 1 : 0,
    post.imageProvider ?? null,
    post.imageProviderChain ?? null,
    post.imageWarning ?? null,
    post.videoUrl ?? null,
    post.videoPrompt ?? null,
    post.videoStoryboard ?? null,
    post.videoStatus ?? null,
    post.videoDurationSeconds ?? null,
    post.videoResolution ?? null,
    post.videoWarning ?? null,
    jsonString(post.platforms ?? []),
    post.status,
    post.provider || null,
    post.model || null,
    post.contextSnapshot || null,
    post.engagementScore,
    post.aiCostUsd ?? null,
    jsonString(post.aiUsage ?? null),
  ];

  const withQaParams: SqlParam[] = [
    ...coreParams,
    jsonString(post.qualityReport ?? null),
    post.mediaAssetId ?? null,
    post.bannerLayoutId ?? null,
    post.regenerationCount ?? 0,
    jsonString(post.platformPermalinks ?? null),
    toMysqlDateTime(post.publishedAt),
    toMysqlDateTime(post.createdAt) ?? toMysqlDateTime(new Date()),
  ];

  try {
    await execute(
      `INSERT INTO generated_posts (
        id, user_id, project_id, project_name, context_name, sequence_post_id, sequence_index,
        parent_post_id, version, title, caption, image_url, image_prompt, image_approved,
        image_provider, image_provider_chain, image_warning, video_url, video_prompt, video_storyboard,
        video_status, video_duration_seconds, video_resolution, video_warning, platforms_json,
        status, provider, model, context_snapshot, engagement_score, ai_cost_usd, ai_usage_json,
        quality_report_json, media_asset_id, banner_layout_id, regeneration_count,
        platform_permalinks_json, published_at, created_at
      ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
      ON DUPLICATE KEY UPDATE
        user_id = VALUES(user_id),
        project_id = VALUES(project_id),
        project_name = VALUES(project_name),
        context_name = VALUES(context_name),
        sequence_post_id = VALUES(sequence_post_id),
        sequence_index = VALUES(sequence_index),
        parent_post_id = VALUES(parent_post_id),
        version = VALUES(version),
        title = VALUES(title),
        caption = VALUES(caption),
        image_url = VALUES(image_url),
        image_prompt = VALUES(image_prompt),
        image_approved = VALUES(image_approved),
        image_provider = VALUES(image_provider),
        image_provider_chain = VALUES(image_provider_chain),
        image_warning = VALUES(image_warning),
        video_url = VALUES(video_url),
        video_prompt = VALUES(video_prompt),
        video_storyboard = VALUES(video_storyboard),
        video_status = VALUES(video_status),
        video_duration_seconds = VALUES(video_duration_seconds),
        video_resolution = VALUES(video_resolution),
        video_warning = VALUES(video_warning),
        platforms_json = VALUES(platforms_json),
        status = VALUES(status),
        provider = VALUES(provider),
        model = VALUES(model),
        context_snapshot = VALUES(context_snapshot),
        engagement_score = VALUES(engagement_score),
        ai_cost_usd = VALUES(ai_cost_usd),
        ai_usage_json = VALUES(ai_usage_json),
        quality_report_json = VALUES(quality_report_json),
        media_asset_id = VALUES(media_asset_id),
        banner_layout_id = VALUES(banner_layout_id),
        regeneration_count = VALUES(regeneration_count),
        platform_permalinks_json = VALUES(platform_permalinks_json),
        published_at = VALUES(published_at)`,
      withQaParams,
    );
  } catch (err) {
    const msg = err instanceof Error ? err.message : String(err);
    // Migration 008 not applied — still must update image_url in MariaDB (never JSON-only).
    if (/Unknown column|quality_report_json|media_asset_id|banner_layout_id|regeneration_count/i.test(msg)) {
      console.warn(
        "[PostSync] QA columns missing — saving core post fields. Apply db/migrations/008_generation_qa.sql",
      );
      await execute(
        `INSERT INTO generated_posts (
          id, user_id, project_id, project_name, context_name, sequence_post_id, sequence_index,
          parent_post_id, version, title, caption, image_url, image_prompt, image_approved,
          image_provider, image_provider_chain, image_warning, video_url, video_prompt, video_storyboard,
          video_status, video_duration_seconds, video_resolution, video_warning, platforms_json,
          status, provider, model, context_snapshot, engagement_score, ai_cost_usd, ai_usage_json,
          platform_permalinks_json, published_at, created_at
        ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
        ON DUPLICATE KEY UPDATE
          title = VALUES(title),
          caption = VALUES(caption),
          image_url = VALUES(image_url),
          image_prompt = VALUES(image_prompt),
          image_provider = VALUES(image_provider),
          image_provider_chain = VALUES(image_provider_chain),
          image_warning = VALUES(image_warning),
          video_url = VALUES(video_url),
          video_prompt = VALUES(video_prompt),
          video_storyboard = VALUES(video_storyboard),
          video_status = VALUES(video_status),
          video_duration_seconds = VALUES(video_duration_seconds),
          video_resolution = VALUES(video_resolution),
          video_warning = VALUES(video_warning),
          platforms_json = VALUES(platforms_json),
          status = VALUES(status),
          provider = VALUES(provider),
          model = VALUES(model),
          context_snapshot = VALUES(context_snapshot),
          engagement_score = VALUES(engagement_score),
          ai_cost_usd = VALUES(ai_cost_usd),
          ai_usage_json = VALUES(ai_usage_json),
          platform_permalinks_json = VALUES(platform_permalinks_json),
          published_at = VALUES(published_at),
          version = VALUES(version)`,
        [
          ...coreParams,
          jsonString(post.platformPermalinks ?? null),
          toMysqlDateTime(post.publishedAt),
          toMysqlDateTime(post.createdAt) ?? toMysqlDateTime(new Date()),
        ],
      );
    } else {
      throw err;
    }
  }
  return (await getGeneratedPostFromDb(post.id)) ?? post;
}

export async function updateGeneratedPostFields(
  id: string,
  fields: Partial<{
    status: GeneratedPostStatus;
    caption: string;
    imageApproved: boolean;
    publishedAt: string | null;
    platformPermalinks: Record<string, string>;
  }>,
): Promise<GeneratedPost | null> {
  const sets: string[] = [];
  const params: SqlParam[] = [];

  if (fields.status !== undefined) {
    sets.push("status = ?");
    params.push(fields.status);
  }
  if (fields.caption !== undefined) {
    sets.push("caption = ?");
    params.push(fields.caption);
  }
  if (fields.imageApproved !== undefined) {
    sets.push("image_approved = ?");
    params.push(fields.imageApproved ? 1 : 0);
  }
  if (fields.publishedAt !== undefined) {
    sets.push("published_at = ?");
    params.push(toMysqlDateTime(fields.publishedAt));
  }
  if (fields.platformPermalinks !== undefined) {
    sets.push("platform_permalinks_json = ?");
    params.push(jsonString(fields.platformPermalinks));
  }

  if (!sets.length) return getGeneratedPostFromDb(id);

  params.push(id);
  await execute(`UPDATE generated_posts SET ${sets.join(", ")} WHERE id = ?`, params);
  return getGeneratedPostFromDb(id);
}

export async function getNextVersionForSequenceFromDb(
  projectId: string,
  sequencePostId: string,
  parentPostId?: string | null,
): Promise<number> {
  const rows = parentPostId
    ? await queryRows<RowDataPacket[]>(
        `SELECT MAX(version) AS max_version FROM generated_posts
         WHERE project_id = ? AND (id = ? OR parent_post_id = ?)`,
        [projectId, parentPostId, parentPostId],
      )
    : await queryRows<RowDataPacket[]>(
        `SELECT MAX(version) AS max_version FROM generated_posts
         WHERE project_id = ? AND sequence_post_id = ?`,
        [projectId, sequencePostId],
      );

  const max = Number(rows[0]?.max_version ?? 0);
  return max + 1 || 1;
}

export function newStatsId(): string {
  return randomUUID();
}