/**
 * models/linkHealth.js
 *
 * CRUD over link_health_checks and monitored_urls. The link health
 * worker writes here; the dashboard + admin views read from here.
 */

'use strict';

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

const insertCheck = db.prepare(`
  INSERT INTO link_health_checks
    (takedown_id, url, status, http_status, reason, body_size, body_hash, response_ms, checked_at)
  VALUES
    (@takedown_id, @url, @status, @http_status, @reason, @body_size, @body_hash, @response_ms, @checked_at)
`);

const latestForTakedown = db.prepare(`
  SELECT * FROM link_health_checks
   WHERE takedown_id = ?
   ORDER BY checked_at DESC, id DESC
   LIMIT 1
`);

const historyForTakedown = db.prepare(`
  SELECT * FROM link_health_checks
   WHERE takedown_id = ?
   ORDER BY checked_at DESC, id DESC
   LIMIT 100
`);

// Aggregate per-domain so the dashboard can show "of the 6 fapedia
// takedowns, 4 are confirmed removed, 1 still live, 1 stale".
const statusByDomain = db.prepare(`
  SELECT
    t.id as takedown_id, t.domain, t.url, t.contact_email, t.status, t.sent_at, t.escalated_at,
    (SELECT status FROM link_health_checks WHERE takedown_id = t.id ORDER BY checked_at DESC LIMIT 1) as last_health_status,
    (SELECT checked_at FROM link_health_checks WHERE takedown_id = t.id ORDER BY checked_at DESC LIMIT 1) as last_health_check,
    (SELECT http_status FROM link_health_checks WHERE takedown_id = t.id ORDER BY checked_at DESC LIMIT 1) as last_http_status
  FROM takedowns t
  ORDER BY t.sent_at DESC
`);

const insertMonitored = db.prepare(`
  INSERT OR REPLACE INTO monitored_urls
    (id, url, domain, case_id, reason, added_by, next_check, check_count, last_status, last_checked, created_at)
  VALUES
    (@id, @url, @domain, @case_id, @reason, @added_by, @next_check, @check_count, @last_status, @last_checked, @created_at)
`);

const dueMonitors = db.prepare(`
  SELECT * FROM monitored_urls
   WHERE next_check <= ?
   ORDER BY next_check ASC
   LIMIT 50
`);

const updateMonitor = db.prepare(`
  UPDATE monitored_urls
     SET check_count = check_count + 1, last_status = ?, last_checked = ?,
         next_check = ?
   WHERE id = ?
`);

const getMonitor = db.prepare(`SELECT * FROM monitored_urls WHERE url = ?`);

function record({ takedown_id, url, status, http_status = null, reason = null,
                  body_size = 0, body_hash = null, response_ms = 0 }) {
  const info = insertCheck.run({
    takedown_id, url, status, http_status, reason, body_size, body_hash, response_ms,
    checked_at: Date.now(),
  });
  return db.prepare('SELECT * FROM link_health_checks WHERE id = ?').get(info.lastInsertRowid);
}

function latestFor(takedownId) { return latestForTakedown.get(takedownId) || null; }
function historyFor(takedownId) { return historyForTakedown.all(takedownId); }
function allTakedownsWithHealth() { return statusByDomain.all(); }

function addMonitor({ url, domain, case_id = null, reason = null, added_by = null,
                      check_every_ms = 24 * 3600 * 1000 }) {
  const existing = getMonitor.get(url);
  if (existing) return { ...existing, __existed: true };
  const info = insertMonitored.run({
    id: null, url, domain, case_id, reason, added_by,
    next_check: Date.now() + 1000,    // check soon
    check_count: 0, last_status: null, last_checked: null,
    created_at: Date.now(),
  });
  return db.prepare('SELECT * FROM monitored_urls WHERE id = ?').get(info.lastInsertRowid);
}

function listDueMonitors() { return dueMonitors.all(Date.now()); }
function recordMonitorCheck(id, status, nextCheckMs) {
  updateMonitor.run(status, Date.now(), Date.now() + nextCheckMs, id);
}

module.exports = {
  record, latestFor, historyFor, allTakedownsWithHealth,
  addMonitor, listDueMonitors, recordMonitorCheck,
};
