import { createReadStream, existsSync, mkdirSync, readFileSync, writeFileSync } from 'fs';
import { createGunzip } from 'zlib';
import { basename, join } from 'path';
import { createHash, randomUUID } from 'crypto';
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();
const DUMP = process.env.OA_DUMP || '/opt/backup/cloud-20260830/oa.sql.gz';
const UPLOAD_ROOT = process.env.OA_UPLOAD || '/opt/backup/cloud-20260830/oa-uploadPath';
const SKIP_TITLE = /已更新至|发现新版本|推荐更新|桌面端|移动端|^App /;

function stableId(key: string) {
  const hex = createHash('sha1').update(key).digest('hex');
  return `${hex.slice(0, 8)}-${hex.slice(8, 12)}-${hex.slice(12, 16)}-${hex.slice(16, 20)}-${hex.slice(20, 32)}`;
}

function parseMysqlTuples(valuesSql: string): unknown[][] {
  const rows: unknown[][] = [];
  let i = 0;
  const s = valuesSql;
  while (i < s.length) {
    while (i < s.length && ' \n\r\t,'.includes(s[i])) i += 1;
    if (i >= s.length) break;
    if (s[i] !== '(') throw new Error(`expected ( at ${i}`);
    i += 1;
    const row: unknown[] = [];
    while (true) {
      while (i < s.length && ' \n\r\t'.includes(s[i])) i += 1;
      if (s.startsWith('_binary ', i)) i += 8;
      if (s.startsWith('NULL', i) && (i + 4 === s.length || ',) \n\r\t'.includes(s[i + 4]))) {
        row.push(null);
        i += 4;
      } else if (s[i] === "'" || s[i] === '"') {
        const q = s[i];
        i += 1;
        let out = '';
        while (i < s.length) {
          const ch = s[i];
          if (ch === '\\') {
            const n = s[i + 1];
            out += n === 'n' ? '\n' : n === 'r' ? '\r' : n === 't' ? '\t' : n === '0' ? '\0' : n;
            i += 2;
            continue;
          }
          if (ch === q) {
            if (s[i + 1] === q) {
              out += q;
              i += 2;
              continue;
            }
            i += 1;
            break;
          }
          out += ch;
          i += 1;
        }
        row.push(out);
      } else {
        const start = i;
        while (i < s.length && s[i] !== ',' && s[i] !== ')') i += 1;
        const raw = s.slice(start, i).trim();
        row.push(raw === '' ? null : /^-?\d+(\.\d+)?$/.test(raw) ? Number(raw) : raw);
      }
      while (i < s.length && ' \n\r\t'.includes(s[i])) i += 1;
      if (s[i] === ',') {
        i += 1;
        continue;
      }
      if (s[i] === ')') {
        i += 1;
        rows.push(row);
        break;
      }
      throw new Error(`bad token at ${i}: ${s.slice(i, i + 20)}`);
    }
  }
  return rows;
}

async function readDump(): Promise<string> {
  return new Promise((resolve, reject) => {
    const chunks: Buffer[] = [];
    createReadStream(DUMP)
      .pipe(createGunzip())
      .on('data', (c: Buffer) => chunks.push(c))
      .on('end', () => resolve(Buffer.concat(chunks).toString('utf8')))
      .on('error', reject);
  });
}

function insertsFor(dump: string, table: string) {
  const rows: unknown[][] = [];
  const needle = `INSERT INTO \`${table}\` VALUES `;
  let from = 0;
  while (true) {
    const start = dump.indexOf(needle, from);
    if (start < 0) break;
    let i = start + needle.length;
    const s = dump;
    while (i < s.length) {
      while (i < s.length && ' \n\r\t'.includes(s[i])) i += 1;
      if (s[i] === ';') {
        i += 1;
        break;
      }
      if (s[i] === ',') {
        i += 1;
        continue;
      }
      if (s[i] !== '(') break;
      const sliceStart = i;
      let depth = 0;
      let inStr = false;
      let q = '';
      while (i < s.length) {
        const ch = s[i];
        if (inStr) {
          if (ch === '\\') {
            i += 2;
            continue;
          }
          if (ch === q) {
            inStr = false;
          }
          i += 1;
          continue;
        }
        if (ch === "'" || ch === '"') {
          inStr = true;
          q = ch;
          i += 1;
          continue;
        }
        if (ch === '(') depth += 1;
        if (ch === ')') {
          depth -= 1;
          if (depth === 0) {
            i += 1;
            break;
          }
        }
        i += 1;
      }
      rows.push(...parseMysqlTuples(s.slice(sliceStart, i)));
    }
    from = i;
  }
  return rows;
}

function mimeOf(name: string) {
  const lower = name.toLowerCase();
  if (lower.endsWith('.pdf')) return 'application/pdf';
  if (lower.endsWith('.docx')) return 'application/vnd.openxmlformats-officedocument.wordprocessingml.document';
  if (lower.endsWith('.doc')) return 'application/msword';
  if (lower.endsWith('.xlsx')) return 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
  if (lower.endsWith('.xls')) return 'application/vnd.ms-excel';
  if (lower.endsWith('.png')) return 'image/png';
  if (lower.endsWith('.jpg') || lower.endsWith('.jpeg')) return 'image/jpeg';
  return 'application/octet-stream';
}

function resolveUpload(rel: string) {
  const cleaned = rel.replace(/^\/profile\//, '').replace(/^\//, '');
  const candidates = [join(UPLOAD_ROOT, cleaned), join(UPLOAD_ROOT, 'upload', cleaned.replace(/^upload\//, ''))];
  return candidates.find((p) => existsSync(p));
}

async function attachFile(noticeId: string, rel: string, authorId: string) {
  if (!rel?.trim()) return;
  const src = resolveUpload(rel);
  if (!src) {
    console.warn('missing attachment', rel);
    return;
  }
  const fileName = basename(src);
  const buf = readFileSync(src);
  const id = stableId(`oa-file:${rel}`);
  const ext = (fileName.split('.').pop() || 'bin').replace(/[^a-zA-Z0-9]/g, '').slice(0, 8);
  const storageKey = `NOTICE/${noticeId}/${id}.${ext || 'bin'}`;
  const full = join(process.cwd(), 'uploads', storageKey);
  mkdirSync(join(full, '..'), { recursive: true });
  writeFileSync(full, buf);
  await prisma.fileAsset.upsert({
    where: { id },
    update: { fileName, size: buf.length, mimeType: mimeOf(fileName), storageKey, bizId: noticeId },
    create: {
      id,
      tenantId: '1',
      bizType: 'NOTICE',
      bizId: noticeId,
      fileName,
      mimeType: mimeOf(fileName),
      size: buf.length,
      storageKey,
      uploadedById: authorId,
    },
  });
}

async function upsertNotice(opts: {
  key: string;
  title: string;
  content: string;
  kind: 'ANNOUNCEMENT' | 'POLICY';
  createdAt?: Date | null;
  authorId: string;
  attachment?: string | null;
}) {
  const title = opts.title.trim();
  if (!title) return;
  const id = stableId(opts.key);
  const content = opts.content || '';
  await prisma.notice.upsert({
    where: { id },
    update: { title, content, kind: opts.kind, status: 'PUBLISHED' },
    create: {
      id,
      tenantId: '1',
      title,
      content,
      kind: opts.kind,
      status: 'PUBLISHED',
      createdById: opts.authorId,
      createdAt: opts.createdAt || undefined,
    },
  });
  if (opts.attachment) await attachFile(id, opts.attachment, opts.authorId);
}

function asDate(v: unknown) {
  if (!v) return null;
  const d = new Date(String(v));
  return Number.isNaN(d.getTime()) ? null : d;
}

async function main() {
  const admin = await prisma.user.findFirst({ where: { username: '18115604840' } });
  if (!admin) throw new Error('缺少管理员账号');
  await prisma.notice.deleteMany({ where: { title: '未命名', status: 'DRAFT' } });

  const dump = await readDump();
  let n = 0;

  for (const row of insertsFor(dump, 'oa_announcement')) {
    const title = String(row[1] || '');
    const status = String(row[4] || '');
    if (status && status !== '正常') continue;
    await upsertNotice({
      key: `oa-announcement:${row[0]}`,
      title,
      content: String(row[15] || ''),
      kind: 'ANNOUNCEMENT',
      createdAt: asDate(row[17]) || asDate(row[11]),
      authorId: admin.id,
      attachment: row[14] ? String(row[14]) : null,
    });
    n += 1;
  }

  for (const row of insertsFor(dump, 'oa_regulation')) {
    const title = String(row[1] || '');
    const status = String(row[16] || '');
    if (status && status !== '正常') continue;
    await upsertNotice({
      key: `oa-regulation:${row[0]}`,
      title,
      content: String(row[19] || ''),
      kind: 'POLICY',
      createdAt: asDate(row[15]) || asDate(row[21]),
      authorId: admin.id,
      attachment: row[18] ? String(row[18]) : null,
    });
    n += 1;
  }

  for (const row of insertsFor(dump, 'sys_notice')) {
    const type = String(row[2] || '');
    const title = String(row[1] || '');
    const status = String(row[4] || '0');
    if (type !== '2') continue;
    if (status === '1') continue;
    if (SKIP_TITLE.test(title)) continue;
    await upsertNotice({
      key: `sys-notice:${row[0]}`,
      title,
      content: String(row[3] || ''),
      kind: 'ANNOUNCEMENT',
      createdAt: asDate(row[7]),
      authorId: admin.id,
    });
    n += 1;
  }

  const total = await prisma.notice.count();
  const files = await prisma.fileAsset.count({ where: { bizType: 'NOTICE' } });
  console.log(`imported ${n} source rows; notices=${total} files=${files}`);
}

main()
  .then(async () => prisma.$disconnect())
  .catch(async (e) => {
    console.error(e);
    await prisma.$disconnect();
    process.exit(1);
  });