#!/usr/bin/env node
/**
 * 生产 runtime 安全：补建作业登记 Page Data（sqlite + workspace policy 文件 + policy index）。
 * 不依赖完整 dev 模块树（db.mjs / page-data-workspace-bind 等）。
 */
import crypto from 'node:crypto';
import fs from 'node:fs';
import path from 'node:path';
import { execFileSync } from 'node:child_process';
import { fileURLToPath } from 'node:url';
import mysql from 'mysql2/promise';

const repoRoot = 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(repoRoot, '.env'));

const USER_ID = '0a763620-c0d6-4e88-9f0a-6cc68840cf7a';
const DATASET = 'homework_records';
const TABLE = 'homework_records';
const REGISTRY = '__page_data_datasets';
const SQLITE_BIN = process.env.SQLITE_BIN?.trim() || 'sqlite3';
const ADMIN_PASSWORD = '88888888';

const PAGES = [
  {
    pageId: '553c3b47-01eb-4294-9ce8-dfb6b2576990',
    relativePath: 'public/homework.html',
    accessMode: 'public',
    policy: {
      accessMode: 'public',
      defaultVisitorRole: 'deny',
      datasets: {
        [DATASET]: {
          read: false,
          insert: true,
          columns: {
            insert: ['student_name', 'record_date', 'chinese', 'math', 'english', 'note'],
          },
        },
      },
    },
  },
  {
    pageId: 'f8720a88-e04d-4c40-938f-11b7fcd41d50',
    relativePath: 'public/homework-admin.html',
    accessMode: 'password',
    policy: {
      accessMode: 'password',
      defaultVisitorRole: 'deny',
      datasets: {
        [DATASET]: {
          read: true,
          insert: false,
          columns: {
            read: ['id', 'student_name', 'record_date', 'chinese', 'math', 'english', 'note', 'created_at'],
          },
        },
      },
    },
  },
];

function runSqlite(dbPath, sql) {
  execFileSync(SQLITE_BIN, ['-batch', dbPath, sql], { stdio: 'pipe' });
}

function sqlLiteral(value) {
  return `'${String(value).replace(/'/g, "''")}'`;
}

function buildScopeHash(policy) {
  const payload = {
    accessMode: policy.accessMode,
    datasets: Object.keys(policy.datasets).sort(),
  };
  return crypto.createHash('sha256').update(JSON.stringify(payload)).digest('hex').slice(0, 16);
}

function writePolicy(workspaceRoot, pageId, policyInput) {
  const policyDir = path.join(workspaceRoot, '.mindspace', 'page-data-policies');
  fs.mkdirSync(policyDir, { recursive: true });
  const policy = {
    pageId,
    ownerUserId: USER_ID,
    workspaceRef: null,
    ...policyInput,
    visitors: [],
    roles: {},
    updatedAt: new Date().toISOString(),
  };
  const policyPath = path.join(policyDir, `${pageId}.json`);
  fs.writeFileSync(policyPath, `${JSON.stringify(policy, null, 2)}\n`, 'utf8');
  return policy;
}

const workspaceRoot = path.join(repoRoot, 'MindSpace', USER_ID);
if (!fs.existsSync(workspaceRoot)) {
  console.error(`工作区不存在: ${workspaceRoot}`);
  process.exit(1);
}

const mindspaceDir = path.join(workspaceRoot, '.mindspace');
const dbPath = path.join(mindspaceDir, 'private-data.sqlite');
fs.mkdirSync(mindspaceDir, { recursive: true });

console.log('1/3 初始化 sqlite 表与 dataset 注册…');
runSqlite(
  dbPath,
  `PRAGMA journal_mode=WAL;
CREATE TABLE IF NOT EXISTS ${TABLE} (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  student_name TEXT NOT NULL,
  record_date TEXT NOT NULL,
  chinese TEXT NOT NULL,
  math TEXT NOT NULL,
  english TEXT NOT NULL,
  note TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL DEFAULT (datetime('now', '+8 hours'))
);
CREATE TABLE IF NOT EXISTS ${REGISTRY} (
  name TEXT PRIMARY KEY,
  table_name TEXT NOT NULL,
  config_json TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);`,
);

const datasetConfig = {
  name: DATASET,
  table: TABLE,
  description: '每日作业登记（语数外）',
  actions: ['read', 'insert'],
  columns: {
    read: ['id', 'student_name', 'record_date', 'chinese', 'math', 'english', 'note', 'created_at'],
    insert: ['student_name', 'record_date', 'chinese', 'math', 'english', 'note'],
  },
  limits: { maxRowsPerRead: 200, maxInsertBytes: 8192 },
};

runSqlite(
  dbPath,
  `INSERT INTO ${REGISTRY} (name, table_name, config_json, updated_at)
   VALUES (${sqlLiteral(DATASET)}, ${sqlLiteral(TABLE)}, ${sqlLiteral(JSON.stringify(datasetConfig))}, CURRENT_TIMESTAMP)
   ON CONFLICT(name) DO UPDATE SET
     table_name = excluded.table_name,
     config_json = excluded.config_json,
     updated_at = CURRENT_TIMESTAMP;`,
);

console.log('2/3 写入 workspace policy 文件…');
const writtenPolicies = [];
for (const page of PAGES) {
  const htmlPath = path.join(workspaceRoot, page.relativePath);
  if (!fs.existsSync(htmlPath)) {
    console.error(`缺少 HTML: ${page.relativePath}`);
    process.exit(1);
  }
  const policy = writePolicy(workspaceRoot, page.pageId, page.policy);
  writtenPolicies.push(policy);
  console.log(`  ✓ ${page.relativePath} → ${page.pageId}.json`);
}

if (!process.env.DATABASE_URL && !(process.env.MYSQL_HOST && process.env.MYSQL_DATABASE)) {
  console.warn('3/3 跳过 MySQL policy index（未配置数据库）');
  process.exit(0);
}

console.log('3/3 同步 MySQL policy index…');
const pool = process.env.DATABASE_URL
  ? mysql.createPool({ uri: process.env.DATABASE_URL, connectionLimit: 2 })
  : mysql.createPool({
      host: process.env.MYSQL_HOST ?? 'localhost',
      port: Number(process.env.MYSQL_PORT ?? 3306),
      user: process.env.MYSQL_USER ?? 'boot',
      password: process.env.MYSQL_PASSWORD ?? '',
      database: process.env.MYSQL_DATABASE ?? 'tkmind',
      connectionLimit: 2,
    });

const now = Date.now();
for (const policy of writtenPolicies) {
  const datasetCount = Object.keys(policy.datasets).length;
  await pool.query(
    `INSERT INTO h5_page_data_policy_index
      (page_id, owner_user_id, access_mode, dataset_count, scope_hash, updated_at)
     VALUES (?, ?, ?, ?, ?, ?)
     ON DUPLICATE KEY UPDATE
      owner_user_id = VALUES(owner_user_id),
      access_mode = VALUES(access_mode),
      dataset_count = VALUES(dataset_count),
      scope_hash = VALUES(scope_hash),
      updated_at = VALUES(updated_at)`,
    [policy.pageId, USER_ID, policy.accessMode, datasetCount, buildScopeHash(policy), now],
  );
}

await pool.end();

console.log('\n完成。');
console.log(`  登记页：https://m.tkmind.cn/MindSpace/${USER_ID}/public/homework.html`);
console.log(`  后台页：https://m.tkmind.cn/MindSpace/${USER_ID}/public/homework-admin.html`);
console.log(`  后台口令：${ADMIN_PASSWORD}（平台要求至少 8 位；「888」无效）`);