/**
 * 数据导入脚本
 * 运行: node server/database/seed.js
 *
 * 把 data/*.json 的内容导入 SQLite 数据库
 */
import Database from 'better-sqlite3'
import { readFileSync } from 'fs'
import { join, dirname } from 'path'
import { fileURLToPath } from 'url'
import { createHash } from 'crypto'

const __dirname = dirname(fileURLToPath(import.meta.url))
const DB_PATH = join(__dirname, 'hulai.db')
const DATA_DIR = join(__dirname, '..', '..', 'data')

const db = new Database(DB_PATH)

function readJSON(filename) {
  return JSON.parse(readFileSync(join(DATA_DIR, filename), 'utf-8'))
}

// 简单密码 hash（生产环境建议用 bcrypt）
function hashPassword(password) {
  return createHash('sha256').update(password).digest('hex')
}

// ── 创建默认管理员 ────────────────────────────────────
const insertAdmin = db.prepare(
  'INSERT OR IGNORE INTO admin_users (username, password_hash) VALUES (?, ?)'
)
insertAdmin.run('admin', hashPassword('hulai2026'))
console.log('✅ 管理员账号: admin / hulai2026')

// ── 导入新闻 ──────────────────────────────────────────
const newsData = readJSON('news.json')
const insertArticle = db.prepare(`
  INSERT OR IGNORE INTO articles (slug, title, summary, content, category, type, author, created_at, updated_at)
  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
`)
for (const item of newsData.articles) {
  insertArticle.run(
    item.id, item.title, item.summary, item.content,
    item.category, 'news', '呼籁旅行',
    item.date, item.date
  )
}
console.log(`✅ 新闻导入: ${newsData.articles.length} 条`)

// ── 导入博客 ──────────────────────────────────────────
const blogData = readJSON('blog.json')
const insertBlog = db.prepare(`
  INSERT OR IGNORE INTO articles (slug, title, summary, content, category, type, cover_image, author, tags, seo_title, seo_description, seo_keywords, created_at, updated_at)
  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
`)
for (const item of blogData.articles) {
  insertBlog.run(
    item.id, item.title, item.summary, item.content,
    item.category, 'blog', item.coverImage || null,
    item.author, JSON.stringify(item.tags || []),
    item.seo?.title || null, item.seo?.description || null, item.seo?.keywords || null,
    item.date, item.date
  )
}
console.log(`✅ 博客导入: ${blogData.articles.length} 条`)

// ── 导入评价 ──────────────────────────────────────────
const reviewsData = readJSON('reviews.json')
const insertReview = db.prepare(`
  INSERT OR IGNORE INTO reviews (author, rating, title, content, date, verified, sort_order)
  VALUES (?, ?, ?, ?, ?, ?, ?)
`)
for (let i = 0; i < reviewsData.items.length; i++) {
  const item = reviewsData.items[i]
  insertReview.run(
    item.nickname, 5, item.productVersion || '',
    item.content, item.travelDate, 1, i
  )
}
console.log(`✅ 评价导入: ${reviewsData.items.length} 条`)

// ── 导入 FAQ ─────────────────────────────────────────
const faqData = readJSON('faq.json')
const insertFaq = db.prepare(
  'INSERT OR IGNORE INTO faqs (question, answer, sort_order) VALUES (?, ?, ?)'
)
// faq.json 结构: { categories: [{ questions: [{ question, answer }] }] }
// 或者是扁平结构
let faqCount = 0
if (faqData.categories) {
  for (const cat of faqData.categories) {
    for (const q of (cat.questions || [])) {
      insertFaq.run(q.question || q.q, q.answer || q.a, faqCount)
      faqCount++
    }
  }
} else if (Array.isArray(faqData)) {
  for (const q of faqData) {
    insertFaq.run(q.question || q.q, q.answer || q.a, faqCount)
    faqCount++
  }
}
console.log(`✅ FAQ导入: ${faqCount} 条`)

// ── 导入相册 ─────────────────────────────────────────
const galleryData = readJSON('gallery.json')
const insertGallery = db.prepare(`
  INSERT OR IGNORE INTO gallery_items (title, category, location, description, image, orientation, sort_order)
  VALUES (?, ?, ?, ?, ?, ?, ?)
`)
const works = galleryData.works || galleryData
if (Array.isArray(works)) {
  for (let i = 0; i < works.length; i++) {
    const item = works[i]
    insertGallery.run(
      item.title, item.category, item.location || '',
      item.description || '', item.image, item.orientation || 'landscape', i
    )
  }
  console.log(`✅ 相册导入: ${works.length} 条`)
}

// ── 导入客户故事 ──────────────────────────────────────
const storiesData = readJSON('stories.json')
const insertStory = db.prepare(`
  INSERT OR IGNORE INTO stories (slug, title, subtitle, family_type, children_age, from_city, trip_product, travel_date, sections, key_moments, seo_title, seo_description)
  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
`)
for (const item of storiesData.stories) {
  insertStory.run(
    item.id, item.title, item.subtitle || '',
    item.familyType || '', item.childrenAge || '',
    item.fromCity || '', item.tripProduct || '', item.travelDate || '',
    JSON.stringify(item.sections || []),
    JSON.stringify(item.keyMoments || []),
    item.seo?.title || null, item.seo?.description || null
  )
}
console.log(`✅ 客户故事导入: ${storiesData.stories.length} 条`)

// ── 导入站点配置（复杂 JSON 直接存）──────────────────
const siteConfigs = [
  { key: 'product-lines', label: '产品线列表', file: 'product-lines.json' },
  { key: 'products', label: '产品管理', file: 'products.json' },
  { key: 'autumn-products', label: '秋季产品', file: 'autumn-products.json' },
  { key: 'winter-products', label: '冬季产品', file: 'winter-products.json' },
  { key: 'winter-camp', label: '冬令营', file: 'winter-camp.json' },
  { key: 'destinations', label: '目的地对比', file: 'destinations.json' },
  { key: 'destinations-detail', label: '目的地详情', file: 'destinations-detail.json' },
  { key: 'courses', label: '研学课程', file: 'courses.json' },
  { key: 'calendar', label: '出行日历', file: 'calendar.json' },
  { key: 'guides', label: '出行指南', file: 'guides.json' },
  { key: 'guide-custom', label: '定制游怎么选 (文章)', file: 'guide-custom.json' },
  { key: 'section-titles', label: '栏目标题', file: 'section-titles.json' },
  { key: 'navigation', label: '导航配置', file: 'navigation.json' },
  { key: 'brand', label: '品牌信息', file: 'brand.json' },
  { key: 'contact', label: '联系方式', file: 'contact.json' },
  { key: 'about', label: '关于我们', file: 'about.json' },
  { key: 'seo', label: 'SEO配置', file: 'seo.json' },
  { key: 'pricing', label: '价格配置', file: 'pricing.json' },
  { key: 'customize', label: '定制配置', file: 'customize.json' },
  { key: 'selector', label: '产品选择器', file: 'selector.json' },
  { key: 'qualifications', label: '资质证书', file: 'qualifications.json' },
  { key: 'partners', label: '合作伙伴', file: 'partners.json' },
  { key: 'xiaohongshu-wall', label: '小红书墙', file: 'xiaohongshu-wall.json' },
  { key: 'youji', label: '游记', file: 'youji.json' },
  { key: 'images', label: '图片配置', file: 'images.json' },
  { key: 'versions', label: '版本历史', file: 'versions.json' },
  { key: 'brand-wiki', label: '呼籁旅行百科', file: 'brand-wiki.json' },
  { key: 'company-wiki', label: '呼籁文旅集团百科', file: 'company-wiki.json' },
]

const insertSiteContent = db.prepare(`
  INSERT OR IGNORE INTO site_content (key, label, data) VALUES (?, ?, ?)
`)

for (const config of siteConfigs) {
  try {
    const data = readJSON(config.file)
    insertSiteContent.run(config.key, config.label, JSON.stringify(data))
  } catch (e) {
    console.log(`⚠️ 跳过 ${config.file}: ${e.message}`)
  }
}
console.log(`✅ 站点配置导入: ${siteConfigs.length} 项`)

// ── 导入已有表单提交记录 ──────────────────────────────
try {
  const contactRecords = JSON.parse(
    readFileSync(join(__dirname, '..', 'storage', 'contact.json'), 'utf-8')
  )
  const insertSubmission = db.prepare(
    'INSERT OR IGNORE INTO submissions (type, data, ip, created_at) VALUES (?, ?, ?, ?)'
  )
  if (Array.isArray(contactRecords)) {
    for (const r of contactRecords) {
      insertSubmission.run('contact', JSON.stringify(r), r.ip || '', r.createdAt || new Date().toISOString())
    }
    console.log(`✅ 联系表单记录导入: ${contactRecords.length} 条`)
  }
} catch {
  console.log('ℹ️ 无历史表单记录，跳过')
}

try {
  const customizeRecords = JSON.parse(
    readFileSync(join(__dirname, '..', 'storage', 'customize.json'), 'utf-8')
  )
  const insertSubmission = db.prepare(
    'INSERT OR IGNORE INTO submissions (type, data, ip, created_at) VALUES (?, ?, ?, ?)'
  )
  if (Array.isArray(customizeRecords)) {
    for (const r of customizeRecords) {
      insertSubmission.run('customize', JSON.stringify(r), r.ip || '', r.createdAt || new Date().toISOString())
    }
    console.log(`✅ 定制表单记录导入: ${customizeRecords.length} 条`)
  }
} catch {
  console.log('ℹ️ 无定制表单记录，跳过')
}

db.close()
console.log('\n🎉 数据导入完成！')