/**
 * models/case.js
 */

'use strict';

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

const insert = db.prepare(`
  INSERT INTO cases (user_id, org_id, victim_name, reference_url, context,
                     status, billing_cycle_start, created_at, updated_at)
  VALUES (@user_id, @org_id, @victim_name, @reference_url, @context,
          'open', @billing_cycle_start, @created_at, @updated_at)
`);

const update = db.prepare(`
  UPDATE cases SET victim_name = COALESCE(?, victim_name),
                   reference_url = COALESCE(?, reference_url),
                   context = COALESCE(?, context),
                   status = COALESCE(?, status),
                   updated_at = ?
  WHERE id = ?
`);

const close = db.prepare(`
  UPDATE cases SET status = 'closed', closed_at = ?, updated_at = ? WHERE id = ?
`);

const remove = db.prepare('DELETE FROM cases WHERE id = ?');
const findById = db.prepare('SELECT * FROM cases WHERE id = ?');

const listByUser = db.prepare(`
  SELECT * FROM cases WHERE user_id = ? ORDER BY created_at DESC LIMIT ? OFFSET ?
`);
const countByUser = db.prepare(`
  SELECT COUNT(*) AS n FROM cases WHERE user_id = ? AND status != 'closed'
`);
const listByOrg = db.prepare(`
  SELECT c.*, u.email AS owner_email
    FROM cases c JOIN users u ON u.id = c.user_id
   WHERE c.org_id = ?
   ORDER BY c.created_at DESC LIMIT ? OFFSET ?
`);
const listAll = db.prepare(`
  SELECT c.*, u.email AS owner_email
    FROM cases c JOIN users u ON u.id = c.user_id
   ORDER BY c.created_at DESC LIMIT ? OFFSET ?
`);

const statsByStatus = db.prepare(`
  SELECT status, COUNT(*) AS n FROM cases GROUP BY status
`);

function create({ user_id, org_id = null, victim_name, reference_url = null, context = null }) {
  const now = Date.now();
  const info = insert.run({
    user_id,
    org_id,
    victim_name: String(victim_name).trim(),
    reference_url: reference_url ? String(reference_url).trim() : null,
    context: context ? String(context).trim() : null,
    billing_cycle_start: now,
    created_at: now,
    updated_at: now,
  });
  return findById.get(info.lastInsertRowid);
}

function getById(id) { return findById.get(id); }
function listForUser(userId, limit = 50, offset = 0) { return listByUser.all(userId, limit, offset); }
function listForOrg(orgId, limit = 50, offset = 0)   { return listByOrg.all(orgId, limit, offset); }
function listAllCases(limit = 50, offset = 0)         { return listAll.all(limit, offset); }
function activeCountForUser(userId) { return countByUser.get(userId).n; }

function update_(id, fields) {
  update.run(
    fields.victim_name ?? null,
    fields.reference_url ?? null,
    fields.context ?? null,
    fields.status ?? null,
    Date.now(),
    id
  );
}

function closeCase(id) {
  const now = Date.now();
  close.run(now, now, id);
}
function removeCase(id) { remove.run(id); }

function stats() { return statsByStatus.all(); }

module.exports = {
  create, getById, listForUser, listForOrg, listAllCases, activeCountForUser,
  update: update_, closeCase, removeCase, stats,
};
