import type { CurrentUser, Env, SiteSettings } from '../env';
import { all, first } from '../db/queries';
import { renderContentHtml } from '../utils/html';
import { mediaUrl } from '../utils/image';
import { nowSeconds } from '../utils/time';
import { ulid } from '../utils/id';

export interface PostAuthor {
  id: string;
  username: string;
  displayName: string;
  avatarKey: string | null;
  avatarUrl: string;
}

export interface PostImage {
  key: string;
  url: string;
  width: number | null;
  height: number | null;
}

export interface FeedPost {
  id: string;
  author: PostAuthor;
  type: 'moment' | 'article';
  visibility: 'public' | 'private';
  status: 'published' | 'draft' | 'trash';
  isTop: boolean;
  isAd: boolean;
  title: string;
  content: string;
  contentHtml: string;
  images: PostImage[];
  videoKey: string | null;
  videoUrl: string | null;
  position: string | null;
  positionLat: number | null;
  positionLng: number | null;
  shareUrl: string | null;
  shareThumb: string | null;
  likeCount: number;
  commentCount: number;
  viewerLiked: boolean;
  createdAt: number;
  updatedAt: number;
}

interface PostJoinedRow {
  id: string;
  author_id: string;
  username: string;
  display_name: string;
  avatar_key: string | null;
  type: 'moment' | 'article';
  visibility: 'public' | 'private';
  status: 'published' | 'draft' | 'trash';
  is_top: number;
  is_ad: number;
  title: string | null;
  content: string;
  content_html: string;
  video_key: string | null;
  position: string | null;
  position_lat: number | null;
  position_lng: number | null;
  share_url: string | null;
  share_thumb: string | null;
  like_count: number;
  comment_count: number;
  created_at: number;
  updated_at: number;
  viewer_liked: number | null;
}

function mapPost(row: PostJoinedRow, images: PostImage[], settings: SiteSettings): FeedPost {
  return {
    id: row.id,
    author: {
      id: row.author_id,
      username: row.username,
      displayName: row.display_name,
      avatarKey: row.avatar_key,
      avatarUrl: mediaUrl(settings.cdn_base_url, row.avatar_key)
    },
    type: row.type,
    visibility: row.visibility,
    status: row.status,
    isTop: row.is_top === 1,
    isAd: row.is_ad === 1,
    title: row.title || '',
    content: row.content,
    contentHtml: row.content_html,
    images,
    videoKey: row.video_key,
    videoUrl: row.video_key ? mediaUrl(settings.cdn_base_url, row.video_key, '') : null,
    position: row.position,
    positionLat: row.position_lat,
    positionLng: row.position_lng,
    shareUrl: row.share_url,
    shareThumb: row.share_thumb,
    likeCount: row.like_count,
    commentCount: row.comment_count,
    viewerLiked: row.viewer_liked === 1,
    createdAt: row.created_at,
    updatedAt: row.updated_at
  };
}

async function loadImages(env: Env['Bindings'], postIds: string[], settings: SiteSettings): Promise<Map<string, PostImage[]>> {
  const out = new Map<string, PostImage[]>();
  for (const id of postIds) out.set(id, []);
  if (postIds.length === 0) return out;
  const placeholders = postIds.map(() => '?').join(',');
  const rows = await all<{ post_id: string; r2_key: string; width: number | null; height: number | null }>(
    env.DB.prepare(`SELECT post_id, r2_key, width, height FROM wm_post_images WHERE post_id IN (${placeholders}) ORDER BY position ASC`).bind(
      ...postIds
    )
  );
  for (const row of rows) {
    out.get(row.post_id)?.push({
      key: row.r2_key,
      url: mediaUrl(settings.cdn_base_url, row.r2_key, ''),
      width: row.width,
      height: row.height
    });
  }
  return out;
}

function baseSelect(viewerId?: string | null): string {
  return `
    SELECT p.*, u.username, u.display_name, u.avatar_key,
      ${viewerId ? `CASE WHEN l.user_id IS NULL THEN 0 ELSE 1 END` : `0`} AS viewer_liked
    FROM wm_posts p
    JOIN wm_users u ON u.id = p.author_id
    ${viewerId ? `LEFT JOIN wm_likes l ON l.post_id = p.id AND l.user_id = ?` : ``}
  `;
}

export async function listPosts(
  env: Env['Bindings'],
  settings: SiteSettings,
  options: {
    viewer?: CurrentUser | null;
    cursor?: string | null;
    limit?: number;
    authorUsername?: string | null;
    type?: 'moment' | 'article' | null;
    q?: string | null;
    includeTrash?: boolean;
    admin?: boolean;
  } = {}
): Promise<{ posts: FeedPost[]; nextCursor: string | null }> {
  const limit = Math.min(Math.max(options.limit || 20, 1), 50);
  const binds: unknown[] = [];
  if (options.viewer) binds.push(options.viewer.id);

  const where: string[] = [];
  if (!options.includeTrash) where.push(`p.status = 'published'`);
  if (!options.admin) {
    if (options.viewer) {
      where.push(`(p.visibility = 'public' OR p.author_id = ?)`);
      binds.push(options.viewer.id);
    } else {
      where.push(`p.visibility = 'public'`);
    }
  }
  if (options.authorUsername) {
    where.push(`u.username = ?`);
    binds.push(options.authorUsername);
  }
  if (options.type) {
    where.push(`p.type = ?`);
    binds.push(options.type);
  }
  if (options.q) {
    where.push(`(p.content LIKE ? OR p.title LIKE ?)`);
    binds.push(`%${options.q}%`, `%${options.q}%`);
  }
  if (options.cursor) {
    const [createdAt, id] = options.cursor.split('_');
    const cursorTime = Number(createdAt);
    if (Number.isFinite(cursorTime) && id) {
      where.push(`(p.created_at < ? OR (p.created_at = ? AND p.id < ?))`);
      binds.push(cursorTime, cursorTime, id);
    }
  }

  const sql = `${baseSelect(options.viewer?.id)}
    ${where.length ? `WHERE ${where.join(' AND ')}` : ''}
    ORDER BY p.is_top DESC, p.created_at DESC, p.id DESC
    LIMIT ?`;
  binds.push(limit + 1);
  const rows = await all<PostJoinedRow>(env.DB.prepare(sql).bind(...binds));
  const pageRows = rows.slice(0, limit);
  const images = await loadImages(env, pageRows.map((row) => row.id), settings);
  const posts = pageRows.map((row) => mapPost(row, images.get(row.id) || [], settings));
  const last = posts[posts.length - 1];
  return {
    posts,
    nextCursor: rows.length > limit && last ? `${last.createdAt}_${last.id}` : null
  };
}

export async function getPost(
  env: Env['Bindings'],
  settings: SiteSettings,
  id: string,
  viewer?: CurrentUser | null,
  admin = false
): Promise<FeedPost | null> {
  const binds: unknown[] = [];
  if (viewer) binds.push(viewer.id);
  binds.push(id);
  const visibility = admin ? '' : viewer ? `AND (p.visibility = 'public' OR p.author_id = ?)` : `AND p.visibility = 'public'`;
  if (!admin && viewer) binds.push(viewer.id);
  const row = await first<PostJoinedRow>(
    env.DB.prepare(`${baseSelect(viewer?.id)} WHERE p.id = ? AND p.status != 'trash' ${visibility}`).bind(...binds)
  );
  if (!row) return null;
  const images = await loadImages(env, [row.id], settings);
  return mapPost(row, images.get(row.id) || [], settings);
}

export async function createPost(
  env: Env['Bindings'],
  author: CurrentUser,
  input: {
    type: 'moment' | 'article';
    visibility: 'public' | 'private';
    isTop: boolean;
    isAd: boolean;
    content: string;
    images: string[];
    videoKey?: string | null;
    position?: string | null;
    positionLat?: number | null;
    positionLng?: number | null;
    title?: string;
    shareUrl?: string | null;
    shareThumb?: string | null;
  }
): Promise<string> {
  const id = ulid();
  const now = nowSeconds();
  await env.DB.prepare(
    `INSERT INTO wm_posts(
      id, author_id, type, visibility, status, is_top, is_ad, title, content, content_html, video_key,
      position, position_lat, position_lng, share_url, share_thumb, created_at, updated_at
    ) VALUES (?, ?, ?, ?, 'published', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
  )
    .bind(
      id,
      author.id,
      input.type,
      input.visibility,
      input.isTop ? 1 : 0,
      input.isAd ? 1 : 0,
      input.title || '',
      input.content,
      renderContentHtml(input.content),
      input.videoKey || null,
      input.position || null,
      input.positionLat ?? null,
      input.positionLng ?? null,
      input.shareUrl || null,
      input.shareThumb || null,
      now,
      now
    )
    .run();
  const imageStatements = input.images.slice(0, 9).map((key, index) =>
    env.DB.prepare('INSERT INTO wm_post_images(post_id, position, r2_key) VALUES (?, ?, ?)').bind(id, index, key)
  );
  if (imageStatements.length > 0) await env.DB.batch(imageStatements);
  return id;
}

export async function updatePost(
  env: Env['Bindings'],
  id: string,
  actor: CurrentUser,
  input: Partial<{
    type: 'moment' | 'article';
    visibility: 'public' | 'private';
    isTop: boolean;
    isAd: boolean;
    content: string;
    images: string[];
    videoKey: string | null;
    position: string | null;
    positionLat: number | null;
    positionLng: number | null;
    title: string;
    shareUrl: string | null;
    shareThumb: string | null;
  }>
): Promise<void> {
  const existing = await first<{
    author_id: string;
    video_key: string | null;
    position: string | null;
    position_lat: number | null;
    position_lng: number | null;
    share_url: string | null;
    share_thumb: string | null;
  }>(env.DB.prepare('SELECT author_id, video_key, position, position_lat, position_lng, share_url, share_thumb FROM wm_posts WHERE id = ?').bind(id));
  if (!existing) throw new Error('内容不存在');
  if (existing.author_id !== actor.id && actor.role !== 'admin') throw new Error('无权编辑此内容');
  const now = nowSeconds();
  await env.DB.prepare(
    `UPDATE wm_posts SET
      type = COALESCE(?, type),
      visibility = COALESCE(?, visibility),
      is_top = COALESCE(?, is_top),
      is_ad = COALESCE(?, is_ad),
      title = COALESCE(?, title),
      content = COALESCE(?, content),
      content_html = COALESCE(?, content_html),
      video_key = ?,
      position = ?,
      position_lat = ?,
      position_lng = ?,
      share_url = ?,
      share_thumb = ?,
      updated_at = ?
     WHERE id = ?`
  )
    .bind(
      input.type ?? null,
      input.visibility ?? null,
      input.isTop === undefined ? null : input.isTop ? 1 : 0,
      input.isAd === undefined ? null : input.isAd ? 1 : 0,
      input.title ?? null,
      input.content ?? null,
      input.content === undefined ? null : renderContentHtml(input.content),
      input.videoKey === undefined ? existing.video_key : input.videoKey,
      input.position === undefined ? existing.position : input.position,
      input.positionLat === undefined ? existing.position_lat : input.positionLat,
      input.positionLng === undefined ? existing.position_lng : input.positionLng,
      input.shareUrl === undefined ? existing.share_url : input.shareUrl,
      input.shareThumb === undefined ? existing.share_thumb : input.shareThumb,
      now,
      id
    )
    .run();
  if (input.images) {
    await env.DB.prepare('DELETE FROM wm_post_images WHERE post_id = ?').bind(id).run();
    const statements = input.images.slice(0, 9).map((key, index) =>
      env.DB.prepare('INSERT INTO wm_post_images(post_id, position, r2_key) VALUES (?, ?, ?)').bind(id, index, key)
    );
    if (statements.length > 0) await env.DB.batch(statements);
  }
}

export async function softDeletePost(env: Env['Bindings'], id: string, actor: CurrentUser): Promise<void> {
  const row = await first<{ author_id: string }>(env.DB.prepare('SELECT author_id FROM wm_posts WHERE id = ?').bind(id));
  if (!row) throw new Error('内容不存在');
  if (row.author_id !== actor.id && actor.role !== 'admin') throw new Error('无权删除此内容');
  await env.DB.prepare(`UPDATE wm_posts SET status = 'trash', updated_at = ? WHERE id = ?`).bind(nowSeconds(), id).run();
}
