/**
 * Social Comment Responder — persistence and idempotency, on this machine.
 *
 * A port of the server's `services/social-comments.js` with the multi-tenant
 * layer removed. Production needed `user_id` on every row and a guard on every
 * write because one Postgres held every merchant's comments; here the database
 * is a file in one person's home directory and there is nobody else in it. That
 * deletion is most of the diff — and it removes the class of bug the server had
 * to defend against, where one tenant's external_id overwrote another's row.
 *
 * What did NOT change is the reason this module exists: idempotency. Scraping is
 * re-entrant by nature — every sweep re-reads the same inbox, a retry replays the
 * batch — so without a stable key the same shopper gets answered twice in public.
 * `UNIQUE(platform, external_id)` plus upserts (never check-then-insert, which
 * races) is the whole correctness story, and it survives the port intact.
 *
 * The agent still never sees a row id. Every call is keyed by
 * (platform, external_id) — the platform's own comment id, read off the page — so
 * a crash between two calls can never strand a row the agent has no way to name
 * again.
 *
 * SQLite rather than a JSON file because OpenClaw's storage rule is explicit:
 * runtime state lives in SQLite, never in sidecar files. `node:sqlite` is in the
 * Node that Electron ships (24.18), so this stays dependency-free like its
 * sibling `mcp-facebook` — which matters, because the gateway spawns it as a
 * child process outside the app's asar where a bundled `node_modules` would not
 * resolve.
 */
import { DatabaseSync } from 'node:sqlite'
import { mkdirSync } from 'node:fs'
import { dirname } from 'node:path'

const DB_PATH = process.env.FANGBOT_SOCIAL_DB
if (!DB_PATH) throw new Error('FANGBOT_SOCIAL_DB is not set')

mkdirSync(dirname(DB_PATH), { recursive: true, mode: 0o700 })

const db = new DatabaseSync(DB_PATH)

// WAL so a long-running sweep does not block a read from the Skills screen, and
// a crash mid-write rolls back rather than truncating the file.
db.exec('PRAGMA journal_mode = WAL')
db.exec('PRAGMA foreign_keys = ON')

db.exec(`
CREATE TABLE IF NOT EXISTS social_comments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  platform TEXT NOT NULL,
  external_id TEXT NOT NULL,               -- platform's own comment id, scraped
  source_type TEXT NOT NULL DEFAULT 'page_post',
  source_ref TEXT,
  permalink TEXT,
  author_name TEXT,
  author_ref TEXT,
  body TEXT NOT NULL DEFAULT '',
  posted_at TEXT,
  -- The agent's reading of the comment, in its OWN words and the shopper's own
  -- language. No fixed vocabulary: a closed list pushes real variety into an
  -- "other" bucket that tells the merchant nothing. Normalised on write only so
  -- GROUP BY does not split one intent across spellings.
  intent TEXT,
  sentiment TEXT,
  -- Lifecycle. The agent has full authority, so there is no approval state: a
  -- comment is waiting, answered, deliberately passed over, or attempted and
  -- failed. Nothing here holds a draft someone must release.
  status TEXT NOT NULL DEFAULT 'new',      -- new|auto_replied|skipped|failed
  reply_body TEXT,
  reply_external_id TEXT,
  replied_at TEXT,
  attempt_count INTEGER NOT NULL DEFAULT 0,
  last_error TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE(platform, external_id)
);
CREATE INDEX IF NOT EXISTS idx_social_comments_queue ON social_comments(status, posted_at DESC);
CREATE INDEX IF NOT EXISTS idx_social_comments_intent ON social_comments(intent, sentiment);

-- Engagement accrues over hours, so an outcome is measured later than the reply
-- it describes. Its own table so a re-measure is an upsert here and never
-- rewrites the comment row's history.
CREATE TABLE IF NOT EXISTS social_reply_outcomes (
  comment_id INTEGER PRIMARY KEY REFERENCES social_comments(id) ON DELETE CASCADE,
  likes INTEGER NOT NULL DEFAULT 0,
  author_replied INTEGER NOT NULL DEFAULT 0,
  hidden_or_deleted INTEGER NOT NULL DEFAULT 0,
  measured_at TEXT NOT NULL DEFAULT (datetime('now'))
);
`)

const PLATFORMS = new Set(['facebook', 'instagram'])

/**
 * Case and inner whitespace ARE normalised, because `intent` is a GROUP BY key:
 * left raw, "Hỏi giá", "hỏi giá" and "hỏi  giá" become three rows of n=1 each
 * and every aggregate over them is noise.
 */
const label = (v, max = 32) => {
  const s = String(v ?? '')
    .trim()
    .toLowerCase()
    .replace(/\s+/g, ' ')
  return s ? s.slice(0, max) : null
}

/** Shape/validate one agent-supplied comment. Returns null when unusable. */
function normalize(c) {
  const platform = String(c?.platform ?? '')
    .trim()
    .toLowerCase()
  const externalId = String(c?.external_id ?? c?.externalId ?? '').trim()
  if (!PLATFORMS.has(platform) || !externalId) return null
  return {
    platform,
    externalId: externalId.slice(0, 512),
    sourceType: String(c.source_type || c.sourceType || 'page_post').slice(0, 32),
    sourceRef: c.source_ref || c.sourceRef || null,
    permalink: c.permalink || null,
    authorName: (c.author_name || c.authorName || null)?.toString().slice(0, 255) || null,
    authorRef: c.author_ref || c.authorRef || null,
    body: String(c.body ?? c.text ?? '').trim(),
    postedAt: c.posted_at || c.postedAt || null,
  }
}

/**
 * How much of the backlog is worth answering right now.
 *
 * This has no counterpart on the server, and the reason is the whole point of
 * the port: production runs in a container that never sleeps, so a sweep every
 * five minutes only ever sees minutes-old comments. A laptop is closed at night,
 * taken on holiday, and opened a week later to a full inbox — at which point an
 * unguarded sweep would answer a hundred stale threads at once, in public, which
 * reads as a bot having a breakdown rather than a merchant being helpful.
 *
 * Two limits, because they fail in different directions and neither covers the
 * other:
 *
 * - **Age.** Past two days a public reply is worse than silence: the shopper has
 *   already bought elsewhere, and the reply's only visible effect is to timestamp
 *   how long the shop ignored them. Comments with no `posted_at` are treated as
 *   RECENT, not stale — Meta does not always render a time, and the alternative
 *   reading would silently drop live comments on the shops whose inbox happens to
 *   show relative dates.
 * - **Volume.** Age alone does not help a shop with a post going viral, where
 *   three hundred comments can be minutes old and all eligible. The cap spreads
 *   them across sweeps instead of one burst, which is both calmer to read and far
 *   less like a bot to Meta's own automation heuristics.
 *
 * Oldest first, so the shopper who has waited longest is answered first and
 * nobody starves at the back of a busy queue.
 *
 * Everything filtered out here is still RECORDED — the merchant's reporting
 * should reflect what arrived, not only what we answered. It is simply not
 * offered as work, and stays `new`, so raising the limits (or a merchant asking
 * for the backlog explicitly) picks it up on the next sweep rather than losing it.
 *
 * @param {{externalId: string, postedAt: string|null, body: string}[]} comments
 *   Fresh comments, already recorded, that no earlier sweep has handled.
 * @param {number} now  Epoch ms, injected so this is testable.
 * @returns {{externalId: string, postedAt: string|null, body: string}[]}
 *   The subset worth answering now, oldest first.
 */
const MAX_AGE_MS = 48 * 60 * 60 * 1000
const MAX_PER_SWEEP = 10

export function selectAnswerable(comments, now = Date.now()) {
  return comments
    .map((c) => {
      const parsed = c.postedAt ? Date.parse(c.postedAt) : Number.NaN
      // An unparseable timestamp is no better evidence than a missing one, so it
      // takes the same benefit of the doubt rather than silently aging the
      // comment out.
      return { comment: c, postedAt: Number.isFinite(parsed) ? parsed : null }
    })
    .filter(({ postedAt }) => postedAt === null || now - postedAt <= MAX_AGE_MS)
    .sort((a, b) => (a.postedAt ?? now) - (b.postedAt ?? now))
    .slice(0, MAX_PER_SWEEP)
    .map(({ comment }) => comment)
}

/**
 * Record what the agent just saw, and tell it which of those still need work.
 *
 * Called ONCE per sweep with everything visible in the inbox, before the agent
 * acts on any of it. Every comment is recorded whether or not it will be
 * answered — the merchant's reporting should reflect what arrived, not only what
 * we chose to reply to.
 *
 * Returns one entry per input: `handled: true` means some earlier sweep already
 * logged an outcome for it and the agent must leave it alone.
 */
export function markSeen(comments) {
  const selfReply = db.prepare(
    `SELECT 1 FROM social_comments
      WHERE reply_body IS NOT NULL AND trim(reply_body) = ? LIMIT 1`,
  )
  const upsert = db.prepare(
    `INSERT INTO social_comments
       (platform, external_id, source_type, source_ref, permalink,
        author_name, author_ref, body, posted_at)
     VALUES (?,?,?,?,?,?,?,?,?)
     ON CONFLICT (platform, external_id) DO UPDATE
       SET body = excluded.body,
           permalink = COALESCE(excluded.permalink, permalink),
           source_ref = COALESCE(excluded.source_ref, source_ref),
           updated_at = datetime('now')
     RETURNING external_id, status, posted_at, body`,
  )

  const results = []
  for (const [index, raw] of comments.entries()) {
    const c = normalize(raw)
    if (!c) {
      results.push({ index, error: 'platform (facebook|instagram) and external_id are required' })
      continue
    }

    // Never ingest our own reply as a new shopper comment. A reply we post shows
    // up in the same thread, so the next sweep reads it back; left alone the
    // agent answers itself, that answer is read back too, and the loop runs
    // forever on a public Page. Observed live on the server.
    //
    // Matching on TEXT rather than author is what makes this robust: the posting
    // identity varies with how the Page is configured, but the text is ours by
    // construction.
    if (c.body && selfReply.get(c.body)) {
      results.push({ index, external_id: c.externalId, self_reply: true, handled: true })
      continue
    }

    const row = upsert.get(
      c.platform,
      c.externalId,
      c.sourceType,
      c.sourceRef,
      c.permalink,
      c.authorName,
      c.authorRef,
      c.body,
      c.postedAt,
    )
    results.push({
      index,
      external_id: row.external_id,
      status: row.status,
      posted_at: row.posted_at,
      body: row.body,
      // 'new' and 'failed' are both still workable; anything else is done with.
      handled: !['new', 'failed'].includes(row.status),
    })
  }
  return results
}

/**
 * Log what the agent did with a comment, after it has already acted.
 *
 * `replied`  — it posted `replyBody` and verified it landed
 * `skipped`  — it decided not to reply (spam, troll, nothing useful to say)
 * `failed`   — it tried and could not; `error` says what it observed
 *
 * A single upsert, so this works whether or not markSeen ran first — an agent
 * that crashed mid-sweep and restarted can still report what it did. The status
 * guard in the WHERE clause is what makes a second call for the same comment a
 * no-op instead of a double count.
 *
 * Returns null when the comment was already logged.
 */
export function logOutcome({
  platform,
  externalId,
  outcome,
  intent,
  sentiment,
  replyBody,
  replyExternalId,
  error,
  body,
} = {}) {
  const status = { replied: 'auto_replied', skipped: 'skipped', failed: 'failed' }[outcome]
  if (!status) return { error: `unknown outcome '${outcome}' (expected replied|skipped|failed)` }

  const p = String(platform ?? '')
    .trim()
    .toLowerCase()
  const ext = String(externalId ?? '').trim()
  if (!PLATFORMS.has(p) || !ext) {
    return { error: 'platform (facebook|instagram) and external_id are required' }
  }

  const row = db
    .prepare(
      `INSERT INTO social_comments
         (platform, external_id, body, status, intent, sentiment,
          reply_body, reply_external_id, replied_at, attempt_count, last_error)
       VALUES (?,?,?,?,?,?,?,?,
               CASE WHEN ? = 'auto_replied' THEN datetime('now') END, 1, ?)
       ON CONFLICT (platform, external_id) DO UPDATE
         SET status = excluded.status,
             intent = COALESCE(excluded.intent, intent),
             sentiment = COALESCE(excluded.sentiment, sentiment),
             reply_body = COALESCE(excluded.reply_body, reply_body),
             reply_external_id = COALESCE(excluded.reply_external_id, reply_external_id),
             replied_at = CASE WHEN excluded.status = 'auto_replied'
                               THEN datetime('now') ELSE replied_at END,
             attempt_count = attempt_count + 1,
             last_error = excluded.last_error,
             updated_at = datetime('now')
         WHERE status IN ('new','failed')
       RETURNING external_id, status, intent, sentiment, reply_body`,
    )
    .get(
      p,
      ext,
      String(body ?? '').trim(),
      status,
      label(intent),
      label(sentiment, 16),
      replyBody || null,
      replyExternalId || null,
      status,
      error || null,
    )
  return row ?? null
}

/**
 * Engagement measured on a reply we posted, hours or days later.
 *
 * Keyed through the comment's external_id so the agent still never needs a row id.
 */
export function recordEngagement({ platform, externalId, likes, authorReplied, hiddenOrDeleted } = {}) {
  const p = String(platform ?? '')
    .trim()
    .toLowerCase()
  const ext = String(externalId ?? '').trim()
  if (!PLATFORMS.has(p) || !ext) {
    return { error: 'platform (facebook|instagram) and external_id are required' }
  }
  const row = db
    .prepare(
      `INSERT INTO social_reply_outcomes (comment_id, likes, author_replied, hidden_or_deleted, measured_at)
       SELECT id, ?, ?, ?, datetime('now') FROM social_comments
        WHERE platform = ? AND external_id = ?
       ON CONFLICT (comment_id) DO UPDATE
         SET likes = excluded.likes,
             author_replied = excluded.author_replied,
             hidden_or_deleted = excluded.hidden_or_deleted,
             measured_at = datetime('now')
       RETURNING comment_id`,
    )
    .get(parseInt(likes, 10) || 0, authorReplied ? 1 : 0, hiddenOrDeleted ? 1 : 0, p, ext)
  return row ?? null
}

/** Queue counters by status — the authoritative answer to "what happened this sweep". */
export function queueCounts() {
  const rows = db.prepare('SELECT status, COUNT(*) AS n FROM social_comments GROUP BY status').all()
  return Object.fromEntries(rows.map((r) => [r.status, r.n]))
}

/** Aggregate view for reporting and for the agent to learn from. */
export function intentStats({ days = 30 } = {}) {
  return db
    .prepare(
      `SELECT c.intent, c.sentiment,
              COUNT(*) AS total,
              SUM(c.status = 'auto_replied') AS replied,
              SUM(c.status = 'skipped') AS skipped,
              SUM(c.status = 'failed') AS failed,
              ROUND(COALESCE(AVG(o.likes), 0), 2) AS avg_reply_likes,
              SUM(COALESCE(o.author_replied, 0)) AS author_replied,
              SUM(COALESCE(o.hidden_or_deleted, 0)) AS backfired
         FROM social_comments c
         LEFT JOIN social_reply_outcomes o ON o.comment_id = c.id
        WHERE c.created_at > datetime('now', ?)
        GROUP BY c.intent, c.sentiment
        ORDER BY total DESC`,
    )
    .all(`-${Math.max(1, parseInt(days, 10) || 30)} days`)
}
