'use strict';
const Database = require('better-sqlite3');
const { readFileSync, readdirSync, existsSync } = require('fs');
const { join } = require('path');

const BUNDLE = 'C:/Users/d-chrka/Downloads/bundle-restore';
const DB_PATH = 'C:/work/chrka/myNote/ka-note/server/ka-note.db';
const b = (v) => (v ? 1 : 0);

const db = new Database(DB_PATH);
db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = OFF');

const userId = JSON.parse(readFileSync(join(BUNDLE, 'manifest.json'), 'utf8')).userId;
console.log('userId:', userId);

const ctxRows = JSON.parse(readFileSync(join(BUNDLE, 'contexts.json'), 'utf8'));
const ctxStmt = db.prepare(`INSERT INTO contexts (id,user_id,name,type,sort_order,meta,archived_at,is_favorite,updated_at,deleted_at,purged_at,version) VALUES (@id,@userId,@name,@type,@sortOrder,@meta,@archivedAt,@isFavorite,@updatedAt,@deletedAt,@purgedAt,@version) ON CONFLICT(id,user_id) DO UPDATE SET name=excluded.name,type=excluded.type,sort_order=excluded.sort_order,meta=excluded.meta,archived_at=excluded.archived_at,is_favorite=excluded.is_favorite,updated_at=excluded.updated_at,deleted_at=excluded.deleted_at,purged_at=excluded.purged_at,version=excluded.version`);
db.transaction(() => { for (const r of ctxRows) ctxStmt.run({...r, userId, meta: r.meta ? JSON.stringify(r.meta) : null, isFavorite: b(r.isFavorite)}); })();
console.log('contexts:', ctxRows.length);

const topRows = JSON.parse(readFileSync(join(BUNDLE, 'topics.json'), 'utf8'));
const topStmt = db.prepare(`INSERT INTO topics (id,user_id,context_id,title,status,snooze_until,sort_order,is_new,updated_at,deleted_at,purged_at,version) VALUES (@id,@userId,@contextId,@title,@status,@snoozeUntil,@sortOrder,@isNew,@updatedAt,@deletedAt,@purgedAt,@version) ON CONFLICT(id,user_id) DO UPDATE SET context_id=excluded.context_id,title=excluded.title,status=excluded.status,snooze_until=excluded.snooze_until,sort_order=excluded.sort_order,is_new=excluded.is_new,updated_at=excluded.updated_at,deleted_at=excluded.deleted_at,purged_at=excluded.purged_at,version=excluded.version`);
db.transaction(() => { for (const r of topRows) topStmt.run({...r, userId, isNew: b(r.isNew)}); })();
console.log('topics:', topRows.length);

const histDir = join(BUNDLE, 'history');
if (existsSync(histDir)) {
  const files = readdirSync(histDir).filter(f => f.endsWith('.meta.json'));
  const hStmt = db.prepare(`INSERT INTO history_entries (id,user_id,topic_id,date,text,sort_order,linked_context_id,done_at,wiedervorlage_date,wiedervorlage_resolved_at,is_private,updated_at,deleted_at,purged_at,version) VALUES (@id,@userId,@topicId,@date,@text,@sortOrder,@linkedContextId,@doneAt,@wiedervorlageDate,@wiedervorlageResolvedAt,@isPrivate,@updatedAt,@deletedAt,@purgedAt,@version) ON CONFLICT(id,user_id) DO UPDATE SET topic_id=excluded.topic_id,date=excluded.date,text=excluded.text,sort_order=excluded.sort_order,linked_context_id=excluded.linked_context_id,done_at=excluded.done_at,wiedervorlage_date=excluded.wiedervorlage_date,wiedervorlage_resolved_at=excluded.wiedervorlage_resolved_at,is_private=excluded.is_private,updated_at=excluded.updated_at,deleted_at=excluded.deleted_at,purged_at=excluded.purged_at,version=excluded.version`);
  db.transaction(() => {
    for (const f of files) {
      const meta = JSON.parse(readFileSync(join(histDir, f), 'utf8'));
      const mdFile = join(histDir, f.replace('.meta.json', '.md'));
      const text = existsSync(mdFile) ? readFileSync(mdFile, 'utf8') : '';
      hStmt.run({...meta, userId, text, isPrivate: b(meta.isPrivate)});
    }
  })();
  console.log('history:', files.length);
}

const ratingRows = JSON.parse(readFileSync(join(BUNDLE, 'ratings.json'), 'utf8'));
console.log('ratings:', ratingRows.length);

const wikiDir = join(BUNDLE, 'wiki');
if (existsSync(wikiDir)) {
  const wFiles = readdirSync(wikiDir).filter(f => f.endsWith('.meta.json'));
  const pStmt = db.prepare(`INSERT INTO pages (id,user_id,title,body,is_private,is_favorite,sort_order,updated_at,deleted_at,purged_at,version) VALUES (@id,@userId,@title,@body,@isPrivate,@isFavorite,@sortOrder,@updatedAt,@deletedAt,@purgedAt,@version) ON CONFLICT(id,user_id) DO UPDATE SET title=excluded.title,body=excluded.body,is_private=excluded.is_private,is_favorite=excluded.is_favorite,sort_order=excluded.sort_order,updated_at=excluded.updated_at,deleted_at=excluded.deleted_at,purged_at=excluded.purged_at,version=excluded.version`);
  db.transaction(() => {
    for (const f of wFiles) {
      const meta = JSON.parse(readFileSync(join(wikiDir, f), 'utf8'));
      const mdFile = join(wikiDir, f.replace('.meta.json', '.md'));
      const body = existsSync(mdFile) ? readFileSync(mdFile, 'utf8') : '';
      pStmt.run({...meta, userId, body, isPrivate: b(meta.isPrivate), isFavorite: b(meta.isFavorite)});
    }
  })();
  console.log('pages:', wFiles.length);
}

if (existsSync(join(BUNDLE, 'notebooks.json'))) {
  const nbRows = JSON.parse(readFileSync(join(BUNDLE, 'notebooks.json'), 'utf8'));
  if (nbRows.length) {
    const nbStmt = db.prepare(`INSERT INTO notebooks (id,user_id,name,context_id,is_private,is_favorite,sort_order,updated_at,deleted_at,purged_at,version) VALUES (@id,@userId,@name,@contextId,@isPrivate,@isFavorite,@sortOrder,@updatedAt,@deletedAt,@purgedAt,@version) ON CONFLICT(id,user_id) DO UPDATE SET name=excluded.name,context_id=excluded.context_id,is_private=excluded.is_private,is_favorite=excluded.is_favorite,sort_order=excluded.sort_order,updated_at=excluded.updated_at,deleted_at=excluded.deleted_at,purged_at=excluded.purged_at,version=excluded.version`);
    db.transaction(() => { for (const r of nbRows) nbStmt.run({...r, userId, isPrivate: b(r.isPrivate), isFavorite: b(r.isFavorite)}); })();
    console.log('notebooks:', nbRows.length);
  }
}

if (existsSync(join(BUNDLE, 'page_notebooks.json'))) {
  const pnRows = JSON.parse(readFileSync(join(BUNDLE, 'page_notebooks.json'), 'utf8'));
  if (pnRows.length) {
    const pnStmt = db.prepare(`INSERT INTO page_notebooks (id,user_id,page_id,notebook_id,sort_order,updated_at,deleted_at,purged_at,version) VALUES (@id,@userId,@pageId,@notebookId,@sortOrder,@updatedAt,@deletedAt,@purgedAt,@version) ON CONFLICT(id,user_id) DO UPDATE SET sort_order=excluded.sort_order,updated_at=excluded.updated_at,deleted_at=excluded.deleted_at,purged_at=excluded.purged_at,version=excluded.version`);
    db.transaction(() => { for (const r of pnRows) pnStmt.run({...r, userId}); })();
    console.log('page_notebooks:', pnRows.length);
  }
}

const counts = db.prepare(`SELECT (SELECT COUNT(*) FROM contexts WHERE user_id=?) as c,(SELECT COUNT(*) FROM topics WHERE user_id=?) as t,(SELECT COUNT(*) FROM history_entries WHERE user_id=?) as h,(SELECT COUNT(*) FROM pages WHERE user_id=?) as p`).get(userId,userId,userId,userId);
console.log('Final counts:', counts);
console.log('integrity_check:', db.pragma('integrity_check')[0].integrity_check);
db.close();
console.log('Done.');