/**
 * models/takedownNote.js
 *
 * Operator notes attached to a takedown. Distinct from the audit_log:
 * audit_log = system events ("email sent at 14:32"); takedown_notes =
 * operator commentary ("called the platform rep, they promised
 * takedown by Friday").
 *
 * Superadmin-only writes (model enforces at API layer). Anyone with
 * read access to the takedown (owner / superadmin) can read.
 */

'use strict';

const { db } = require('../config/db');

const insertStmt = db.prepare(`
  INSERT INTO takedown_notes (takedown_id, user_id, body, created_at, updated_at)
  VALUES (?, ?, ?, ?, ?)
`);

const updateStmt = db.prepare(`
  UPDATE takedown_notes SET body = ?, updated_at = ? WHERE id = ?
`);

const deleteStmt = db.prepare(`
  DELETE FROM takedown_notes WHERE id = ?
`);

const getByIdStmt = db.prepare(`
  SELECT n.*, u.name AS author_name, u.email AS author_email
    FROM takedown_notes n
    LEFT JOIN users u ON u.id = n.user_id
   WHERE n.id = ?
`);

const listForTakedownStmt = db.prepare(`
  SELECT n.*, u.name AS author_name, u.email AS author_email
    FROM takedown_notes n
    LEFT JOIN users u ON u.id = n.user_id
   WHERE n.takedown_id = ?
   ORDER BY n.created_at DESC, n.id DESC
`);

const countForTakedownStmt = db.prepare(`
  SELECT COUNT(*) AS n FROM takedown_notes WHERE takedown_id = ?
`);

function create({ takedown_id, user_id, body }) {
  const now = Date.now();
  const info = insertStmt.run(takedown_id, user_id, body, now, now);
  return getByIdStmt.get(info.lastInsertRowid);
}

function update(id, body) {
  updateStmt.run(body, Date.now(), id);
  return getByIdStmt.get(id);
}

function remove(id) {
  return deleteStmt.run(id);
}

function getById(id) {
  return getByIdStmt.get(id) || null;
}

function listForTakedown(takedownId) {
  return listForTakedownStmt.all(takedownId);
}

function countForTakedown(takedownId) {
  return countForTakedownStmt.get(takedownId).n;
}

module.exports = { create, update, remove, getById, listForTakedown, countForTakedown };