#!/usr/bin/env node
import crypto from 'node:crypto';
import fs from 'node:fs';
import path from 'node:path';
import { fileURLToPath } from 'node:url';
import { createDbPool, initSchema } from '../db.mjs';
import { createPlazaPostService } from '../plaza-posts.mjs';
import { initializeDefaultSpace } from '../mindspace.mjs';

const root = path.join(path.dirname(fileURLToPath(import.meta.url)), '..');

function loadEnvFile(filePath) {
  if (!fs.existsSync(filePath)) return;
  for (const line of fs.readFileSync(filePath, 'utf8').split('\n')) {
    const trimmed = line.trim();
    if (!trimmed || trimmed.startsWith('#')) continue;
    const eq = trimmed.indexOf('=');
    if (eq < 0) continue;
    const key = trimmed.slice(0, eq).trim();
    const value = trimmed.slice(eq + 1).trim();
    if (!process.env[key]) process.env[key] = value;
  }
}

loadEnvFile(path.join(root, '../../.env.local'));
loadEnvFile(path.join(root, '.env'));

const DEMO_POSTS = [
  {
    category: 'work-report',
    title: 'Q2 产品增长复盘：从 0 到 10 万 DAU',
    summary: '拆解增长漏斗、渠道投放 ROI 与留存曲线，附可复用的周报模板。',
    tags: ['增长', '复盘', '产品'],
    likes: 128,
    comments: 24,
    views: 3200,
    author: 'john',
  },
  {
    category: 'work-report',
    title: 'AI 客服落地 30 天执行报告',
    summary: '从需求澄清、知识库搭建到上线监控，记录真实踩坑与指标变化。',
    tags: ['AI', '客服', '落地'],
    likes: 86,
    comments: 11,
    views: 1840,
    author: 'john',
  },
  {
    category: 'work-report',
    title: '供应链数字化周报 · 第 18 期',
    summary: '库存周转、缺货预警与供应商协同进展，适合制造业团队参考。',
    tags: ['供应链', '周报'],
    likes: 45,
    comments: 6,
    views: 920,
    author: 'admin',
  },
  {
    category: 'study-notes',
    title: 'Rust 所有权模型 15 分钟图解',
    summary: '用生活类比讲清 borrow / move / lifetime，附练习题与答案。',
    tags: ['Rust', '编程', '笔记'],
    likes: 203,
    comments: 37,
    views: 5100,
    author: 'john',
  },
  {
    category: 'study-notes',
    title: 'LLM Prompt 工程速查表',
    summary: '角色设定、Few-shot、CoT、结构化输出，一张表搞定常见场景。',
    tags: ['Prompt', 'LLM'],
    likes: 312,
    comments: 58,
    views: 8900,
    author: 'john',
  },
  {
    category: 'creative',
    title: '赛博水墨城市概念海报',
    summary: 'MindSpace 生成的东方未来主义视觉，适合活动主 KV 参考。',
    tags: ['设计', '海报', 'AI绘画'],
    likes: 167,
    comments: 19,
    views: 4200,
    author: 'john',
  },
  {
    category: 'creative',
    title: '独立音乐人首张 EP 视觉方案',
    summary: '封面、社媒九宫格与演出海报的统一视觉语言。',
    tags: ['音乐', '品牌'],
    likes: 74,
    comments: 9,
    views: 1600,
    author: 'admin',
  },
  {
    category: 'business',
    title: '社区咖啡店开业 7 天运营手记',
    summary: '选址理由、爆款 SKU、会员拉新与坪效测算一页看懂。',
    tags: ['咖啡', '开店'],
    likes: 96,
    comments: 15,
    views: 2400,
    author: 'john',
  },
  {
    category: 'business',
    title: '普拉提工作室 618 活动页',
    summary: '课程包组合、限时优惠与预约转化话术，可直接复用结构。',
    tags: ['健身', '618'],
    likes: 58,
    comments: 7,
    views: 1300,
    author: 'john',
  },
  {
    category: 'travel',
    title: '马来西亚 5 日轻量旅行攻略',
    summary: '吉隆坡 + 槟城路线、美食清单与预算表，适合周末出发。',
    tags: ['马来西亚', '攻略'],
    likes: 241,
    comments: 42,
    views: 7600,
    author: 'john',
  },
  {
    category: 'travel',
    title: '京都红叶季小众散步地图',
    summary: '避开人潮的 8 条街道与 3 家深夜食堂，附交通换乘。',
    tags: ['日本', '红叶'],
    likes: 189,
    comments: 31,
    views: 5400,
    author: 'admin',
  },
  {
    category: 'data-analysis',
    title: '电商大促 GMV 归因看板',
    summary: '渠道、品类、客单三层下钻，附 SQL 与可视化截图说明。',
    tags: ['电商', 'BI'],
    likes: 112,
    comments: 18,
    views: 2900,
    author: 'john',
  },
  {
    category: 'data-analysis',
    title: '用户留存 Cohort 分析模板',
    summary: '按注册周看 D1/D7/D30 留存，含异常波动排查 checklist。',
    tags: ['留存', '分析'],
    likes: 77,
    comments: 12,
    views: 2100,
    author: 'john',
  },
  {
    category: 'lifestyle',
    title: '极简桌面改造：提升专注的 12 个细节',
    summary: '线缆收纳、光照、显示器高度与数字断舍离清单。',
    tags: ['桌面', '效率'],
    likes: 134,
    comments: 21,
    views: 3600,
    author: 'john',
  },
  {
    category: 'lifestyle',
    title: '晨间写作 21 天挑战记录',
    summary: '每天 20 分钟自由书写，记录情绪变化与可执行习惯。',
    tags: ['写作', '习惯'],
    likes: 63,
    comments: 14,
    views: 1500,
    author: 'admin',
  },
  {
    category: 'other',
    title: '给未来的自己：2026 愿望清单',
    summary: '学习、健康、关系与创作四个维度的年度目标墙。',
    tags: ['年度规划'],
    likes: 39,
    comments: 8,
    views: 980,
    author: 'john',
  },
  {
    category: 'other',
    title: '开源副业项目冷启动笔记',
    summary: '从 0 Star 到首批付费用户的 6 个关键动作。',
    tags: ['开源', '副业'],
    likes: 91,
    comments: 16,
    views: 2200,
    author: 'john',
  },
];

async function loadUser(pool, username) {
  const [rows] = await pool.query(
    `SELECT id, username, slug, display_name FROM h5_users WHERE username = ? LIMIT 1`,
    [username],
  );
  return rows[0] ?? null;
}

async function ensureUserSpace(pool, userId) {
  const [[space]] = await pool.query(
    `SELECT id FROM h5_user_spaces WHERE user_id = ? LIMIT 1`,
    [userId],
  );
  if (space?.id) return;
  await initializeDefaultSpace(pool, userId);
}

async function createDemoPublication(pool, userId, demo, index) {
  const now = Date.now() - index * 3_600_000;
  const publicationId = crypto.randomUUID();
  const pageId = crypto.randomUUID();
  const pageVersionId = crypto.randomUUID();
  const contentAssetId = crypto.randomUUID();
  const assetVersionId = crypto.randomUUID();
  const scanId = crypto.randomUUID();
  const slug = `demo-${demo.category}-${index}-${publicationId.slice(0, 6)}`;

  const [[space]] = await pool.query(
    `SELECT id FROM h5_user_spaces WHERE user_id = ? LIMIT 1`,
    [userId],
  );
  const [[category]] = await pool.query(
    `SELECT id FROM h5_space_categories WHERE user_id = ? AND category_code = 'draft' LIMIT 1`,
    [userId],
  );
  if (!space?.id || !category?.id) throw new Error(`用户 ${userId} 缺少空间或分类`);

  const [[user]] = await pool.query(`SELECT slug, username FROM h5_users WHERE id = ? LIMIT 1`, [userId]);
  const ownerSlug = user?.slug || user?.username || 'user';
  const publicUrl = `/u/${ownerSlug}/pages/${slug}`;

  await pool.query(
    `INSERT INTO h5_assets
     (id, user_id, space_id, category_id, asset_type, mime_type, original_filename, display_name,
      current_version_id, size_bytes, checksum, visibility, status, source_type, created_at, updated_at)
     VALUES (?, ?, ?, ?, 'content', 'text/markdown', 'page.md', ?, ?, 128, ?, 'private', 'ready', 'upload', ?, ?)`,
    [contentAssetId, userId, space.id, category.id, demo.title, assetVersionId, 'demo-seed', now, now],
  );
  await pool.query(
    `INSERT INTO h5_asset_versions
     (id, asset_id, version_no, storage_key, size_bytes, checksum, mime_type, created_by, created_at)
     VALUES (?, ?, 1, ?, 128, ?, 'text/markdown', ?, ?)`,
    [assetVersionId, contentAssetId, `users/${userId}/assets/${contentAssetId}/v1`, 'demo-seed', userId, now],
  );
  await pool.query(
    `INSERT INTO h5_page_records
     (id, user_id, space_id, category_id, title, summary, page_type, template_id, status, visibility, created_at, updated_at)
     VALUES (?, ?, ?, ?, ?, ?, 'article', 'editorial', 'published', 'public', ?, ?)`,
    [pageId, userId, space.id, category.id, demo.title, demo.summary, now, now],
  );
  await pool.query(
    `INSERT INTO h5_security_scans
     (id, user_id, target_type, target_id, scanner_version, status, risk_level, findings_count, started_at, completed_at)
     VALUES (?, ?, 'page_version', ?, 'seed', 'passed', 'none', 0, ?, ?)`,
    [scanId, userId, pageVersionId, now, now],
  );
  await pool.query(
    `INSERT INTO h5_page_versions
     (id, page_id, version_no, content_asset_id, created_by, immutable, created_at)
     VALUES (?, ?, 1, ?, ?, 1, ?)`,
    [pageVersionId, pageId, contentAssetId, userId, now],
  );
  await pool.query(
    `UPDATE h5_page_records SET current_version_id = ?, updated_at = ? WHERE id = ?`,
    [pageVersionId, now, pageId],
  );
  await pool.query(
    `INSERT INTO h5_publish_records
     (id, user_id, page_id, page_version_id, url_slug, public_url, access_mode, published_at, status,
      view_count, security_scan_id, created_at, updated_at)
     VALUES (?, ?, ?, ?, ?, ?, 'public', ?, 'online', 0, ?, ?, ?)`,
    [publicationId, userId, pageId, pageVersionId, slug, publicUrl, now, scanId, now, now],
  );

  return { publicationId, publicUrl };
}

async function bumpPostStats(pool, postId, demo) {
  await pool.query(
    `UPDATE plaza_posts
     SET view_count = ?, like_count = ?, comment_count = ?, hot_score = ?, hot_updated_at = ?, updated_at = ?
     WHERE id = ?`,
    [demo.views, demo.likes, demo.comments, demo.likes * 3 + demo.comments * 5, Date.now(), Date.now(), postId],
  );
}

const pool = createDbPool();
await initSchema(pool);
await createPlazaPostService(pool).ensureCategories();

const users = {};
for (const name of ['john', 'admin']) {
  const user = await loadUser(pool, name);
  if (!user) {
    console.warn(`跳过：用户 ${name} 不存在`);
    continue;
  }
  await ensureUserSpace(pool, user.id);
  users[name] = user;
}

const plazaPosts = createPlazaPostService(pool);
let created = 0;
let skipped = 0;

for (const [index, demo] of DEMO_POSTS.entries()) {
  const user = users[demo.author] ?? users.john;
  if (!user) continue;

  const { publicationId } = await createDemoPublication(pool, user.id, demo, index);
  try {
    const post = await plazaPosts.createPost(user.id, {
      publication_id: publicationId,
      category_slug: demo.category,
      tags: demo.tags,
      allow_comment: true,
    });
    await plazaPosts.reviewPost(post.id, 'approve');
    await bumpPostStats(pool, post.id, demo);
    created += 1;
    console.log(`✓ [${demo.category}] ${demo.title}`);
  } catch (error) {
    if (error?.code === 'ALREADY_PUBLISHED') {
      skipped += 1;
      continue;
    }
    throw error;
  }
}

const [pendingRows] = await pool.query(
  `SELECT id FROM plaza_posts WHERE status = 'pending_review'`,
);
if (pendingRows.length > 0) {
  for (const row of pendingRows) {
    await plazaPosts.reviewPost(row.id, 'approve');
  }
  console.log(`✓ 已通过 ${pendingRows.length} 条待审核帖子`);
}

await pool.end();
console.log(`\n完成：新增 ${created} 条演示帖，跳过 ${skipped} 条。`);
console.log('刷新查看：http://localhost:3001/plaza');
console.log('职场报告：http://localhost:3001/plaza/cat/work-report');