/**
 * models/takedown.js
 */

'use strict';

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

const insert = db.prepare(`
  INSERT INTO takedowns
    (case_id, finding_id, user_id, url, domain, contact_email, email_subject, email_body,
     draft_method, status, sent_at, created_at)
  VALUES
    (@case_id, @finding_id, @user_id, @url, @domain, @contact_email, @email_subject, @email_body,
     @draft_method, @status, @sent_at, @created_at)
`);

const findById = db.prepare('SELECT * FROM takedowns WHERE id = ?');
// DEDUP: a (case_id, domain, contact_email) triple should produce at
// most one takedown row. The auto-learner is supposed to prefer
// already-discovered contacts — but on the first run for a new
// domain, multiple scrape sources can all surface the same WHOIS
// abuse address, producing 2-3 duplicate rows. Without this check
// we'd send the same DMCA letter 2-3 times to the same recipient.
const findByCaseDomainContact = db.prepare(`
  SELECT * FROM takedowns
   WHERE case_id = ? AND domain = ? AND contact_email = ?
   ORDER BY id ASC LIMIT 1
`);
const listByCase = db.prepare('SELECT * FROM takedowns WHERE case_id = ? ORDER BY created_at DESC');
const listByUser = db.prepare('SELECT * FROM takedowns WHERE user_id = ? ORDER BY created_at DESC LIMIT ? OFFSET ?');
const listAll = db.prepare('SELECT * FROM takedowns ORDER BY created_at DESC LIMIT ? OFFSET ?');

const markSent = db.prepare(`
  UPDATE takedowns SET status = 'sent', sent_at = ?, updated_at = ? WHERE id = ?
`);

const markStatus = db.prepare(`
  UPDATE takedowns SET status = ?, error = COALESCE(?, error),
                        opened_at = COALESCE(?, opened_at),
                        responded_at = COALESCE(?, responded_at),
                        resolved_at = COALESCE(?, resolved_at)
   WHERE id = ?
`);

const stats = db.prepare(`
  SELECT status, COUNT(*) AS n FROM takedowns GROUP BY status
`);
const countsByDay = db.prepare(`
  SELECT DATE(created_at/1000, 'unixepoch') AS day, COUNT(*) AS n
    FROM takedowns WHERE created_at > ?
   GROUP BY day ORDER BY day ASC
`);
const resolutionRate = db.prepare(`
  SELECT
    SUM(CASE WHEN status IN ('responded','removed') THEN 1 ELSE 0 END) AS resolved,
    COUNT(*) AS total
    FROM takedowns WHERE status != 'failed'
`);
const topDomains = db.prepare(`
  SELECT domain, COUNT(*) AS n FROM takedowns GROUP BY domain ORDER BY n DESC LIMIT ?
`);

function create({ case_id, finding_id = null, user_id, url, domain, contact_email = null,
                  email_subject = null, email_body = null, draft_method = 'template',
                  status = 'queued' }) {
  // DEDUP: if a takedown for this (case, domain, contact_email)
  // already exists, return it with __deduped: true instead of
  // creating a duplicate. The caller can then decide to:
  //   - log "merged into takedown #X" and move on (the deep
  //     discovery worker does this), or
  //   - update the existing row's url/finding_id to point at
  //     the newer evidence (an operator re-sending with a
  //     better URL would do this).
  //
  // If contact_email is null (chain-only takedown), dedup on
  // (case_id, domain) instead — we don't want two "no contact
  // found" rows for the same domain.
  if (contact_email) {
    const existing = findByCaseDomainContact.get(case_id, domain, contact_email);
    if (existing) return { ...existing, __deduped: true };
  } else {
    const existing = db.prepare(`
      SELECT * FROM takedowns
       WHERE case_id = ? AND domain = ? AND (contact_email IS NULL OR contact_email = '')
       ORDER BY id ASC LIMIT 1
    `).get(case_id, domain);
    if (existing) return { ...existing, __deduped: true };
  }
  const now = Date.now();
  const info = insert.run({
    case_id, finding_id, user_id, url, domain,
    contact_email, email_subject, email_body, draft_method, status,
    sent_at: status === 'sent' ? now : null,
    created_at: now,
  });
  return findById.get(info.lastInsertRowid);
}

function getById(id) { return findById.get(id); }
function getByCaseDomainContact(caseId, domain, contact) {
  return findByCaseDomainContact.get(caseId, domain, contact) || null;
}
function listForCase(caseId) { return listByCase.all(caseId); }
function listForUser(userId, limit = 50, offset = 0) { return listByUser.all(userId, limit, offset); }
function listAllTakedowns(limit = 50, offset = 0)    { return listAll.all(limit, offset); }
function markAsSent(id) { const now = Date.now(); markSent.run(now, now, id); }
function updateStatus(id, status, extras = {}) {
  markStatus.run(
    status, extras.error || null, extras.opened_at || null,
    extras.responded_at || null, extras.resolved_at || null, id
  );
}
function statistics() { return stats.all(); }
function countsLast30Days() { return countsByDay.all(Date.now() - 30 * 86400000); }
function resolution() { return resolutionRate.get(); }
function domainLeaderboard(limit = 20) { return topDomains.all(limit); }

module.exports = {
  create, getById, getByCaseDomainContact, listForCase, listForUser, listAllTakedowns,
  markAsSent, updateStatus,
  statistics, countsLast30Days, resolution, domainLeaderboard,

  // ----- job queue helpers (used by routes/takedown.js + jobs/takedownWorker.js) -----
  enqueueJob({ case_id, user_id, url, domain, finding_id = null, draft_method = 'auto', delay_ms = 0 }) {
    const now = Date.now();
    const info = db.prepare(`
      INSERT INTO takedown_jobs
        (case_id, user_id, url, domain, finding_id, draft_method, status, next_run_at, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, 'queued', ?, ?, ?)
    `).run(case_id, user_id, url, domain, finding_id, draft_method, now + delay_ms, now, now);
    return info.lastInsertRowid;
  },
  getJob(id) { return db.prepare('SELECT * FROM takedown_jobs WHERE id = ?').get(id); },
  listJobsForUser(userId, limit = 50) {
    return db.prepare(`
      SELECT * FROM takedown_jobs
       WHERE user_id = ? ORDER BY created_at DESC LIMIT ?
    `).all(userId, limit);
  },
  setJobStatus(id, status, extras = {}) {
    db.prepare(`
      UPDATE takedown_jobs
         SET status = ?, progress = COALESCE(?, progress),
             last_error = COALESCE(?, last_error),
             takedown_id = COALESCE(?, takedown_id),
             discovery_json = COALESCE(?, discovery_json),
             escalation_json = COALESCE(?, escalation_json),
             result_json = COALESCE(?, result_json),
             updated_at = ?
       WHERE id = ?
    `).run(
      status,
      extras.progress ?? null,
      extras.last_error ?? null,
      extras.takedown_id ?? null,
      extras.discovery_json ? JSON.stringify(extras.discovery_json) : null,
      extras.escalation_json ? JSON.stringify(extras.escalation_json) : null,
      extras.result_json ? JSON.stringify(extras.result_json) : null,
      Date.now(),
      id
    );
  },
  claimNextJob() {
    // SQLite has no SKIP LOCKED in WAL — but with one worker process this is fine.
    const row = db.prepare(`
      SELECT * FROM takedown_jobs
       WHERE status IN ('queued','failed') AND next_run_at <= ?
       ORDER BY next_run_at ASC
       LIMIT 1
    `).get(Date.now());
    if (!row) return null;
    // Mark as discovering atomically (CAS on status)
    const r = db.prepare(`
      UPDATE takedown_jobs SET status = 'discovering', attempts = attempts + 1, updated_at = ?
       WHERE id = ? AND status IN ('queued','failed')
    `).run(Date.now(), row.id);
    if (r.changes === 0) return null; // someone else got it
    return db.prepare('SELECT * FROM takedown_jobs WHERE id = ?').get(row.id);
  },
  reschedule(id, delay_ms, status = 'failed', lastError = null) {
    db.prepare(`
      UPDATE takedown_jobs
         SET status = ?, next_run_at = ?, last_error = ?, updated_at = ?
       WHERE id = ?
    `).run(status, Date.now() + delay_ms, lastError, Date.now(), id);
  },
};
