'use strict';

const express = require('express');
const initSqlJs = require('sql.js');
const multer = require('multer');
const cors = require('cors');
const path = require('path');
const fs = require('fs');
const { randomUUID } = require('crypto');

const PORT = 3000;
const DB_PATH = path.join(__dirname, 'jaryo.db');
const UPLOADS_DIR = path.join(__dirname, 'uploads');
const ADMIN_PASSWORD = process.env.ADMIN_PASSWORD || 'admin1234';
const sessions = new Set();

if (!fs.existsSync(UPLOADS_DIR)) fs.mkdirSync(UPLOADS_DIR);

// ── DB 헬퍼 ───────────────────────────────────────────────────
let db;

function saveDb() {
  const data = db.export();
  fs.writeFileSync(DB_PATH, Buffer.from(data));
}

function queryAll(sql, params = {}) {
  const stmt = db.prepare(sql);
  stmt.bind(params);
  const rows = [];
  while (stmt.step()) rows.push(stmt.getAsObject());
  stmt.free();
  return rows;
}

function queryGet(sql, params = {}) {
  return queryAll(sql, params)[0] ?? null;
}

function run(sql, params = {}) {
  db.run(sql, params);
  saveDb();
}

// ── DB 초기화 ─────────────────────────────────────────────────
async function initDb() {
  const SQL = await initSqlJs();
  if (fs.existsSync(DB_PATH)) {
    db = new SQL.Database(fs.readFileSync(DB_PATH));
  } else {
    db = new SQL.Database();
  }

  db.run(`
    CREATE TABLE IF NOT EXISTS files (
      id          TEXT PRIMARY KEY,
      name        TEXT NOT NULL,
      type        TEXT NOT NULL,
      ext         TEXT NOT NULL,
      size_bytes  INTEGER DEFAULT 0,
      size_label  TEXT NOT NULL,
      modified_at TEXT NOT NULL,
      author      TEXT NOT NULL,
      category    TEXT NOT NULL,
      visibility  TEXT DEFAULT 'team',
      downloads   INTEGER DEFAULT 0,
      starred     INTEGER DEFAULT 0,
      thumb       TEXT NOT NULL,
      file_path   TEXT,
      created_at  TEXT DEFAULT (datetime('now'))
    );
  `);
  db.run(`
    CREATE TABLE IF NOT EXISTS categories (
      id         TEXT PRIMARY KEY,
      label      TEXT NOT NULL,
      icon       TEXT DEFAULT 'folder',
      sort_order INTEGER DEFAULT 0,
      created_at TEXT DEFAULT (datetime('now'))
    );
  `);
  db.run(`
    CREATE TABLE IF NOT EXISTS users (
      id         TEXT PRIMARY KEY,
      name       TEXT NOT NULL UNIQUE,
      email      TEXT,
      role       TEXT DEFAULT 'member',
      created_at TEXT DEFAULT (datetime('now'))
    );
  `);
  db.run(`
    CREATE TABLE IF NOT EXISTS attachments (
      id          TEXT PRIMARY KEY,
      file_id     TEXT NOT NULL,
      name        TEXT NOT NULL,
      type        TEXT NOT NULL,
      ext         TEXT NOT NULL,
      size_bytes  INTEGER DEFAULT 0,
      size_label  TEXT NOT NULL,
      file_path   TEXT NOT NULL,
      downloads   INTEGER DEFAULT 0,
      sort_order  INTEGER DEFAULT 0,
      created_at  TEXT DEFAULT (datetime('now'))
    );
  `);
  saveDb();
  seedCategories();
}

// ── 카테고리 시드 ─────────────────────────────────────────────
function seedCategories() {
  const newIds = ['doc', 'img', 'vid', 'zip', 'code'];
  const existing = queryAll('SELECT id FROM categories');
  const existingIds = existing.map(r => r.id);
  const needsMigration = existingIds.length === 0 || existingIds.some(id => !newIds.includes(id));
  if (!needsMigration) return;

  db.run('DELETE FROM categories');
  const defaults = [
    { id:'doc',  label:'문서',   icon:'folder', sort_order:0 },
    { id:'img',  label:'이미지', icon:'image',  sort_order:1 },
    { id:'vid',  label:'동영상', icon:'video',  sort_order:2 },
    { id:'zip',  label:'압축',   icon:'folder', sort_order:3 },
    { id:'code', label:'코드',   icon:'code',   sort_order:4 },
  ];
  for (const c of defaults) {
    db.run(
      `INSERT INTO categories (id,label,icon,sort_order) VALUES (:id,:label,:icon,:sort_order)`,
      { ':id':c.id, ':label':c.label, ':icon':c.icon, ':sort_order':c.sort_order }
    );
  }
  db.run('UPDATE files SET category = type');
  saveDb();
  console.log('카테고리 마이그레이션 완료: 파일 유형 기반으로 변경');
}

// ── 헬퍼 ─────────────────────────────────────────────────────
function guessType(mimetype, ext) {
  if (!mimetype) return 'doc';
  if (mimetype.startsWith('image/')) return 'img';
  if (mimetype.startsWith('video/')) return 'vid';
  const e = (ext || '').toLowerCase();
  if (['.zip','.tar','.gz','.7z','.rar'].includes(e)) return 'zip';
  if (['.ts','.js','.py','.go','.rs','.java','.c','.cpp','.json','.csv','.sh'].includes(e)) return 'code';
  return 'doc';
}

function formatSize(bytes) {
  if (bytes >= 1073741824) return `${(bytes / 1073741824).toFixed(1)} GB`;
  if (bytes >= 1048576)    return `${(bytes / 1048576).toFixed(1)} MB`;
  if (bytes >= 1024)       return `${Math.round(bytes / 1024)} KB`;
  return `${bytes} B`;
}

function rowToFile(row) {
  if (!row) return null;
  const attachments = queryAll('SELECT * FROM attachments WHERE file_id = :id ORDER BY sort_order ASC', { ':id': row.id });
  return { ...row, starred: row.starred === 1, attachments };
}

function authMiddleware(req, res, next) {
  const token = (req.headers.authorization || '').replace('Bearer ', '').trim();
  if (!token || !sessions.has(token)) return res.status(401).json({ error: '인증이 필요합니다' });
  next();
}

// ── Express ───────────────────────────────────────────────────
const app = express();
app.use(cors());
app.use(express.json());
app.use('/uploads', express.static(UPLOADS_DIR));
app.use(express.static(__dirname));

const storage = multer.diskStorage({
  destination: (req, file, cb) => cb(null, UPLOADS_DIR),
  filename: (req, file, cb) => {
    const ext = path.extname(file.originalname);
    cb(null, `${Date.now()}-${randomUUID()}${ext}`);
  },
});
const upload = multer({ storage, limits: { fileSize: 2 * 1024 * 1024 * 1024 } });

// ── API: 파일 목록 ────────────────────────────────────────────
app.get('/api/files', (req, res) => {
  const { folder, category, types, starred, sort } = req.query;

  let where = [];
  const params = {};

  if (folder === 'starred') {
    where.push('starred = 1');
  } else if (folder === 'trash') {
    return res.json([]);
  }

  if (category) {
    where.push('category = :category');
    params[':category'] = category;
  }

  if (starred === '1') {
    if (!where.includes('starred = 1')) where.push('starred = 1');
  }

  let typeList = [];
  if (types) typeList = types.split(',').filter(Boolean);
  if (typeList.length) {
    const placeholders = typeList.map((_, i) => `:t${i}`).join(',');
    where.push(`type IN (${placeholders})`);
    typeList.forEach((t, i) => { params[`:t${i}`] = t; });
  }

  const whereClause = where.length ? `WHERE ${where.join(' AND ')}` : '';

  let orderClause = 'ORDER BY rowid DESC';
  if (sort === 'name')      orderClause = 'ORDER BY name ASC';
  if (sort === 'downloads') orderClause = 'ORDER BY downloads DESC';
  if (sort === 'size')      orderClause = 'ORDER BY size_bytes DESC';

  let limitClause = '';
  if (folder === 'recent') { orderClause = 'ORDER BY rowid DESC'; limitClause = 'LIMIT 24'; }

  const sql = `SELECT * FROM files ${whereClause} ${orderClause} ${limitClause}`;
  const rows = queryAll(sql, params);
  res.json(rows.map(rowToFile));
});

// ── API: 통계 ─────────────────────────────────────────────────
app.get('/api/stats', (req, res) => {
  const total    = queryGet('SELECT COUNT(*) as c FROM files')?.c ?? 0;
  const starred  = queryGet('SELECT COUNT(*) as c FROM files WHERE starred=1')?.c ?? 0;
  const recent   = queryGet("SELECT COUNT(*) as c FROM files WHERE created_at >= datetime('now','-7 days')")?.c ?? 0;

  const catRows = queryAll('SELECT category, COUNT(*) as c FROM files GROUP BY category');
  const categories = {};
  for (const r of catRows) categories[r.category] = r.c;

  const typeRows = queryAll('SELECT type, COUNT(*) as c FROM files GROUP BY type');
  const types = {};
  for (const r of typeRows) types[r.type] = r.c;

  const usedBytes = queryGet('SELECT COALESCE(SUM(size_bytes),0) as s FROM files')?.s ?? 0;
  const usedGB = parseFloat((usedBytes / 1073741824).toFixed(1));
  const totalGB = 100;

  res.json({
    folders: { all: total, starred, recent, shared: 0, trash: 0 },
    categories,
    types,
    storage: { used: usedGB, total: totalGB, remaining: parseFloat((totalGB - usedGB).toFixed(1)) },
  });
});

// ── API: 업로드 ───────────────────────────────────────────────
app.post('/api/files/upload', authMiddleware, upload.array('files', 3), (req, res) => {
  const uploadedFiles = req.files;
  if (!uploadedFiles || !uploadedFiles.length) return res.status(400).json({ error: '첨부파일이 필요합니다 (최대 3개)' });

  const title = (req.body.title || '').trim();
  if (!title) return res.status(400).json({ error: '제목을 입력해주세요' });

  const attachmentRows = uploadedFiles.map((f, i) => {
    const name = Buffer.from(f.originalname, 'latin1').toString('utf8');
    const ext = path.extname(name);
    return {
      id: randomUUID(), name, type: guessType(f.mimetype, ext),
      ext: ext.replace('.', '').toUpperCase() || 'FILE',
      size_bytes: f.size, size_label: formatSize(f.size),
      file_path: f.filename, sort_order: i,
    };
  });

  const id = randomUUID();
  const primary = attachmentRows[0];
  const totalBytes = attachmentRows.reduce((s, a) => s + a.size_bytes, 0);

  db.run(
    `INSERT INTO files (id,name,type,ext,size_bytes,size_label,modified_at,author,category,visibility,downloads,starred,thumb,file_path)
     VALUES (:id,:name,:type,:ext,:size_bytes,:size_label,:modified_at,:author,:category,:visibility,0,0,:thumb,:file_path)`,
    { ':id':id, ':name':title, ':type':primary.type, ':ext':primary.ext,
      ':size_bytes':totalBytes, ':size_label':formatSize(totalBytes), ':modified_at':'방금',
      ':author': req.body.author || '나', ':category': req.body.category || primary.type,
      ':visibility': req.body.visibility || 'team', ':thumb':primary.type, ':file_path':primary.file_path }
  );

  for (const a of attachmentRows) {
    db.run(
      `INSERT INTO attachments (id,file_id,name,type,ext,size_bytes,size_label,file_path,downloads,sort_order)
       VALUES (:id,:file_id,:name,:type,:ext,:size_bytes,:size_label,:file_path,0,:sort_order)`,
      { ':id':a.id, ':file_id':id, ':name':a.name, ':type':a.type, ':ext':a.ext,
        ':size_bytes':a.size_bytes, ':size_label':a.size_label, ':file_path':a.file_path, ':sort_order':a.sort_order }
    );
  }
  saveDb();

  const row = queryGet('SELECT * FROM files WHERE id = :id', { ':id': id });
  res.json(rowToFile(row));
});

// ── API: 즐겨찾기 토글 ────────────────────────────────────────
app.patch('/api/files/:id/star', (req, res) => {
  const row = queryGet('SELECT starred FROM files WHERE id = :id', { ':id': req.params.id });
  if (!row) return res.status(404).json({ error: 'not found' });
  const next = row.starred ? 0 : 1;
  run('UPDATE files SET starred = :v WHERE id = :id', { ':v': next, ':id': req.params.id });
  res.json({ starred: next === 1 });
});

// ── API: 다운로드 수 증가 (카운트 전용) ──────────────────────
app.post('/api/files/:id/download', (req, res) => {
  const row = queryGet('SELECT downloads FROM files WHERE id = :id', { ':id': req.params.id });
  if (!row) return res.status(404).json({ error: 'not found' });
  run('UPDATE files SET downloads = downloads + 1 WHERE id = :id', { ':id': req.params.id });
  res.json({ downloads: row.downloads + 1 });
});

// ── API: 실제 파일 다운로드 ───────────────────────────────────
app.get('/api/files/:id/download', (req, res) => {
  const row = queryGet('SELECT * FROM files WHERE id = :id', { ':id': req.params.id });
  if (!row) return res.status(404).json({ error: 'not found' });
  if (!row.file_path) return res.status(404).json({ error: 'no file' });
  const fp = path.join(UPLOADS_DIR, row.file_path);
  if (!fs.existsSync(fp)) return res.status(404).json({ error: 'file missing' });
  const primary = queryGet('SELECT name FROM attachments WHERE file_id = :id ORDER BY sort_order ASC LIMIT 1', { ':id': row.id });
  run('UPDATE files SET downloads = downloads + 1 WHERE id = :id', { ':id': req.params.id });
  res.download(fp, primary?.name || row.name);
});

// ── API: 개별 첨부파일 다운로드 ───────────────────────────────
app.get('/api/attachments/:id/download', (req, res) => {
  const row = queryGet('SELECT * FROM attachments WHERE id = :id', { ':id': req.params.id });
  if (!row) return res.status(404).json({ error: 'not found' });
  const fp = path.join(UPLOADS_DIR, row.file_path);
  if (!fs.existsSync(fp)) return res.status(404).json({ error: 'file missing' });
  db.run('UPDATE attachments SET downloads = downloads + 1 WHERE id = :id', { ':id': row.id });
  db.run('UPDATE files SET downloads = downloads + 1 WHERE id = :id', { ':id': row.file_id });
  saveDb();
  res.download(fp, row.name);
});

// ── API: 삭제 ─────────────────────────────────────────────────
app.delete('/api/files', authMiddleware, (req, res) => {
  const { ids } = req.body;
  if (!Array.isArray(ids) || !ids.length) return res.status(400).json({ error: 'ids required' });

  const placeholders = ids.map((_, i) => `:id${i}`).join(',');
  const params = {};
  ids.forEach((id, i) => { params[`:id${i}`] = id; });

  const rows = queryAll(`SELECT file_path FROM attachments WHERE file_id IN (${placeholders})`, params);
  db.run(`DELETE FROM attachments WHERE file_id IN (${placeholders})`, params);
  db.run(`DELETE FROM files WHERE id IN (${placeholders})`, params);
  saveDb();

  for (const r of rows) {
    if (r.file_path) {
      const fp = path.join(UPLOADS_DIR, r.file_path);
      if (fs.existsSync(fp)) fs.unlinkSync(fp);
    }
  }

  res.json({ deleted: ids.length });
});

// ── API: 관리자 인증 ──────────────────────────────────────────
app.post('/api/admin/login', (req, res) => {
  const { password } = req.body;
  if (password !== ADMIN_PASSWORD) return res.status(401).json({ error: '비밀번호가 올바르지 않습니다' });
  const token = randomUUID();
  sessions.add(token);
  res.json({ token });
});

app.post('/api/admin/logout', (req, res) => {
  const token = (req.headers.authorization || '').replace('Bearer ', '').trim();
  sessions.delete(token);
  res.json({ ok: true });
});

app.get('/api/admin/verify', (req, res) => {
  const token = (req.headers.authorization || '').replace('Bearer ', '').trim();
  res.json({ ok: sessions.has(token) });
});

// ── API: 관리자 파일 목록 (검색·페이지네이션) ─────────────────
app.get('/api/admin/files', authMiddleware, (req, res) => {
  const { search, category, page = 1, limit = 20 } = req.query;
  const offset = (parseInt(page) - 1) * parseInt(limit);
  const where = [];
  const params = {};

  if (search) {
    where.push('name LIKE :search');
    params[':search'] = `%${search}%`;
  }
  if (category) {
    where.push('category = :category');
    params[':category'] = category;
  }

  const w = where.length ? `WHERE ${where.join(' AND ')}` : '';
  const total = queryGet(`SELECT COUNT(*) as c FROM files ${w}`, params)?.c ?? 0;
  const items = queryAll(`SELECT * FROM files ${w} ORDER BY rowid DESC LIMIT :limit OFFSET :offset`,
    { ...params, ':limit': parseInt(limit), ':offset': offset }
  ).map(rowToFile);

  res.json({ items, total, page: parseInt(page), limit: parseInt(limit) });
});

// ── API: 파일 메타데이터 수정 ─────────────────────────────────
app.patch('/api/files/:id', authMiddleware, (req, res) => {
  const row = queryGet('SELECT * FROM files WHERE id = :id', { ':id': req.params.id });
  if (!row) return res.status(404).json({ error: 'not found' });
  const { name, category, author, visibility, modified_at } = req.body;
  run(
    `UPDATE files SET
      name        = COALESCE(:name, name),
      category    = COALESCE(:category, category),
      author      = COALESCE(:author, author),
      visibility  = COALESCE(:visibility, visibility),
      modified_at = COALESCE(:modified_at, modified_at)
     WHERE id = :id`,
    {
      ':name': name ?? null, ':category': category ?? null,
      ':author': author ?? null, ':visibility': visibility ?? null,
      ':modified_at': modified_at ?? null, ':id': req.params.id,
    }
  );
  res.json(rowToFile(queryGet('SELECT * FROM files WHERE id = :id', { ':id': req.params.id })));
});

// ── API: 카테고리 CRUD ────────────────────────────────────────
app.get('/api/categories', (req, res) => {
  res.json(queryAll('SELECT * FROM categories ORDER BY sort_order ASC, id ASC'));
});

app.post('/api/categories', authMiddleware, (req, res) => {
  const { id, label, icon = 'folder', sort_order = 0 } = req.body;
  if (!id || !label) return res.status(400).json({ error: 'id와 label은 필수입니다' });
  const exists = queryGet('SELECT id FROM categories WHERE id = :id', { ':id': id });
  if (exists) return res.status(409).json({ error: '이미 존재하는 카테고리 ID입니다' });
  db.run(
    `INSERT INTO categories (id,label,icon,sort_order) VALUES (:id,:label,:icon,:sort_order)`,
    { ':id': id, ':label': label, ':icon': icon, ':sort_order': sort_order }
  );
  saveDb();
  res.status(201).json(queryGet('SELECT * FROM categories WHERE id = :id', { ':id': id }));
});

app.patch('/api/categories/:id', authMiddleware, (req, res) => {
  const row = queryGet('SELECT * FROM categories WHERE id = :id', { ':id': req.params.id });
  if (!row) return res.status(404).json({ error: 'not found' });
  const { label, icon, sort_order } = req.body;
  run(
    `UPDATE categories SET
      label      = COALESCE(:label, label),
      icon       = COALESCE(:icon, icon),
      sort_order = COALESCE(:sort_order, sort_order)
     WHERE id = :id`,
    { ':label': label ?? null, ':icon': icon ?? null, ':sort_order': sort_order ?? null, ':id': req.params.id }
  );
  res.json(queryGet('SELECT * FROM categories WHERE id = :id', { ':id': req.params.id }));
});

app.delete('/api/categories/:id', authMiddleware, (req, res) => {
  const row = queryGet('SELECT * FROM categories WHERE id = :id', { ':id': req.params.id });
  if (!row) return res.status(404).json({ error: 'not found' });
  const used = queryGet('SELECT COUNT(*) as c FROM files WHERE category = :id', { ':id': req.params.id });
  if (used && used.c > 0) return res.status(409).json({ error: `사용 중인 카테고리입니다 (${used.c}개 파일)` });
  run('DELETE FROM categories WHERE id = :id', { ':id': req.params.id });
  res.json({ deleted: 1 });
});

// ── API: 사용자 CRUD ──────────────────────────────────────────
app.get('/api/users', (req, res) => {
  const rows = queryAll('SELECT * FROM users ORDER BY name ASC');
  const withCount = rows.map(u => {
    const fc = queryGet('SELECT COUNT(*) as c FROM files WHERE author = :name', { ':name': u.name });
    return { ...u, file_count: fc?.c ?? 0 };
  });
  res.json(withCount);
});

app.post('/api/users', authMiddleware, (req, res) => {
  const { name, email = null, role = 'member' } = req.body;
  if (!name) return res.status(400).json({ error: 'name은 필수입니다' });
  const exists = queryGet('SELECT id FROM users WHERE name = :name', { ':name': name });
  if (exists) return res.status(409).json({ error: '이미 존재하는 사용자 이름입니다' });
  const id = randomUUID();
  db.run(
    `INSERT INTO users (id,name,email,role) VALUES (:id,:name,:email,:role)`,
    { ':id': id, ':name': name, ':email': email, ':role': role }
  );
  saveDb();
  res.status(201).json(queryGet('SELECT * FROM users WHERE id = :id', { ':id': id }));
});

app.patch('/api/users/:id', authMiddleware, (req, res) => {
  const row = queryGet('SELECT * FROM users WHERE id = :id', { ':id': req.params.id });
  if (!row) return res.status(404).json({ error: 'not found' });
  const { name, email, role } = req.body;
  if (name && name !== row.name) {
    const dup = queryGet('SELECT id FROM users WHERE name = :name AND id != :id', { ':name': name, ':id': req.params.id });
    if (dup) return res.status(409).json({ error: '이미 존재하는 사용자 이름입니다' });
    run('UPDATE files SET author = :name WHERE author = :old', { ':name': name, ':old': row.name });
  }
  run(
    `UPDATE users SET
      name  = COALESCE(:name, name),
      email = COALESCE(:email, email),
      role  = COALESCE(:role, role)
     WHERE id = :id`,
    { ':name': name ?? null, ':email': email ?? null, ':role': role ?? null, ':id': req.params.id }
  );
  res.json(queryGet('SELECT * FROM users WHERE id = :id', { ':id': req.params.id }));
});

app.delete('/api/users/:id', authMiddleware, (req, res) => {
  const row = queryGet('SELECT * FROM users WHERE id = :id', { ':id': req.params.id });
  if (!row) return res.status(404).json({ error: 'not found' });
  const fc = queryGet('SELECT COUNT(*) as c FROM files WHERE author = :name', { ':name': row.name });
  run('DELETE FROM users WHERE id = :id', { ':id': req.params.id });
  res.json({ deleted: 1, file_count: fc?.c ?? 0 });
});

// ── 시작 ──────────────────────────────────────────────────────
initDb().then(() => {
  app.listen(PORT, () => {
    console.log(`자료실 서버 실행 중 → http://localhost:${PORT}`);
    if (!process.env.ADMIN_PASSWORD) {
      console.warn('⚠️  관리자 비밀번호가 기본값(admin1234)입니다. ADMIN_PASSWORD 환경변수로 변경하세요.');
    }
  });
}).catch(err => { console.error('DB 초기화 실패:', err); process.exit(1); });