/**
 * db/tuneops-tickets.js — tuneops_tickets table queries.
 *
 * Owns: CRUD for tuneops_tickets, ticket number generation, canonical key lookup.
 * Does NOT own: ticket lifecycle business logic (services/tuneops-ticket-engine.js),
 *               API routing (routes/tuneops.js), or health check execution.
 */

'use strict';

const pool = require('./index');

// ── Ticket number generation ──────────────────────────────────────────────────

/**
 * Generates the next TO-XXXX number for a company.
 * Scoped per company_domain or connection owner.
 * Format: TO-0001 through TO-9999 then TO-10000+.
 */
async function nextTicketNumber() {
  // Scan globally so ticket numbers are unique across companies (constraint is global).
  const { rows } = await pool.query(
    `SELECT ticket_number FROM tuneops_tickets ORDER BY id DESC FETCH FIRST 1 ROWS ONLY`
  );

  let seq = 1;
  if (rows.length > 0) {
    const last = rows[0].ticket_number; // e.g. "TO-0042"
    const num = parseInt(last.replace('TO-', ''), 10);
    if (!isNaN(num)) seq = num + 1;
  }

  const padded = seq <= 9999
    ? String(seq).padStart(4, '0')
    : String(seq);
  return `TO-${padded}`;
}

// ── Create ────────────────────────────────────────────────────────────────────

/**
 * Creates a new ticket. Returns the created row.
 */
async function createTicket({
  companyId,
  connectionId,
  title,
  description,
  severity,
  status = 'OPEN',
  source = 'health_check',
  sourceReference = {},
  canonicalKey = null,
  recommendedFix = null,
  fixType = null,
  requiresApproval = false,
  assignedTo = null,
}) {
  for (let attempt = 0; attempt < 5; attempt++) {
    const ticketNumber = await nextTicketNumber();
    try {
      const ins = await pool.query(
        `INSERT INTO tuneops_tickets
           (company_id, connection_id, ticket_number, canonical_key, title, description,
            severity, status, source, source_reference, assigned_to,
            recommended_fix, fix_type, requires_approval, created_at, updated_at)
         VALUES (:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13,:p14,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)
         RETURNING id INTO :out_id`,
        {
          p1: companyId, p2: connectionId, p3: ticketNumber, p4: canonicalKey,
          p5: title, p6: description, p7: severity, p8: status, p9: source,
          p10: JSON.stringify(sourceReference), p11: assignedTo,
          p12: recommendedFix, p13: fixType, p14: pool.bool(requiresApproval),
          out_id: pool.outNumber(),
        }
      );
      const { rows } = await pool.query(`SELECT * FROM tuneops_tickets WHERE id = :p1`, { p1: ins.outBinds.out_id[0] });
      return rows[0];
    } catch (err) {
      if (err.errorNum === 1) {   // ORA-00001: duplicate ticket_number — retry
        continue;
      }
      throw err;
    }
  }
  throw new Error('Could not generate a unique ticket number after 5 attempts');
}

// ── Read ──────────────────────────────────────────────────────────────────────

/**
 * Find tickets by canonical key that are in OPEN or ACKNOWLEDGED status.
 * Used for dedup during health check processing.
 */
async function findOpenByCanonicalKey(canonicalKey) {
  const { rows } = await pool.query(
    `SELECT * FROM tuneops_tickets
     WHERE canonical_key = :p1
       AND status IN ('OPEN','ACKNOWLEDGED','DEFERRED','EXECUTING','REOPENED')
     ORDER BY created_at DESC FETCH FIRST 1 ROWS ONLY`,
    { p1: canonicalKey }
  );
  return rows[0] || null;
}

/**
 * Find the most recent RESOLVED ticket with this canonical key.
 */
async function findResolvedByCanonicalKey(canonicalKey) {
  const { rows } = await pool.query(
    `SELECT * FROM tuneops_tickets
     WHERE canonical_key = :p1
       AND status = 'RESOLVED'
     ORDER BY resolved_at DESC FETCH FIRST 1 ROWS ONLY`,
    { p1: canonicalKey }
  );
  return rows[0] || null;
}

/**
 * Get a single ticket by ticket_number.
 */
async function getByTicketNumber(ticketNumber) {
  const { rows } = await pool.query(
    `SELECT t.*,
            u_assigned.email AS assigned_to_email,
            u_approved.email AS approved_by_email,
            u_executed.email AS executed_by_email,
            u_resolved.email AS resolved_by_email
     FROM tuneops_tickets t
     LEFT JOIN users u_assigned ON u_assigned.id = t.assigned_to
     LEFT JOIN users u_approved ON u_approved.id = t.approved_by
     LEFT JOIN users u_executed ON u_executed.id = t.executed_by
     LEFT JOIN users u_resolved ON u_resolved.id = t.resolved_by
     WHERE t.ticket_number = :p1`,
    { p1: ticketNumber }
  );
  return rows[0] || null;
}

/**
 * List tickets with optional filters.
 * Returns paginated results (50 per page).
 */
async function listTickets({ companyId, connectionId, status, severity, assignedTo, page = 1 } = {}) {
  const conditions = ['1=1'];
  const binds = {};
  if (companyId) { conditions.push('t.company_id = :b_company'); binds.b_company = companyId; }
  if (connectionId) { conditions.push('t.connection_id = :b_conn'); binds.b_conn = connectionId; }
  if (status) { conditions.push('t.status = :b_status'); binds.b_status = status; }
  if (severity) { conditions.push('t.severity = :b_severity'); binds.b_severity = severity; }
  if (assignedTo) { conditions.push('t.assigned_to = :b_assigned'); binds.b_assigned = assignedTo; }

  binds.b_offset = (page - 1) * 50;
  binds.b_limit = 50;

  const { rows } = await pool.query(
    `SELECT t.*,
            oc.name AS connection_name,
            oc.server_type AS server_type,
            u_assigned.email AS assigned_to_email
     FROM tuneops_tickets t
     LEFT JOIN oracle_connections oc ON oc.id = t.connection_id
     LEFT JOIN users u_assigned ON u_assigned.id = t.assigned_to
     WHERE ${conditions.join(' AND ')}
     ORDER BY
       CASE t.severity WHEN 'critical' THEN 1 WHEN 'warning' THEN 2 ELSE 3 END,
       t.created_at DESC
     OFFSET :b_offset ROWS FETCH NEXT :b_limit ROWS ONLY`,
    binds
  );
  return rows;
}

/**
 * Count tickets matching filters (for pagination).
 */
async function countTickets({ companyId, connectionId, status, severity, assignedTo } = {}) {
  const conditions = ['1=1'];
  const binds = {};
  if (companyId) { conditions.push('company_id = :b_company'); binds.b_company = companyId; }
  if (connectionId) { conditions.push('connection_id = :b_conn'); binds.b_conn = connectionId; }
  if (status) { conditions.push('status = :b_status'); binds.b_status = status; }
  if (severity) { conditions.push('severity = :b_severity'); binds.b_severity = severity; }
  if (assignedTo) { conditions.push('assigned_to = :b_assigned'); binds.b_assigned = assignedTo; }

  const { rows } = await pool.query(
    `SELECT COUNT(*) AS total FROM tuneops_tickets WHERE ${conditions.join(' AND ')}`,
    binds
  );
  return rows[0].total;
}

// ── Dashboard stats ───────────────────────────────────────────────────────────

/**
 * Returns stats for the TuneOps dashboard widget.
 */
async function getStats(companyId) {
  const { rows } = await pool.query(
    `SELECT
       COUNT(CASE WHEN status NOT IN ('RESOLVED','DEFERRED') THEN 1 END)                  AS open_total,
       COUNT(CASE WHEN severity='critical' AND status NOT IN ('RESOLVED','DEFERRED') THEN 1 END) AS open_critical,
       COUNT(CASE WHEN severity='warning'  AND status NOT IN ('RESOLVED','DEFERRED') THEN 1 END) AS open_warning,
       COUNT(CASE WHEN severity='info'     AND status NOT IN ('RESOLVED','DEFERRED') THEN 1 END) AS open_info,
       COUNT(CASE WHEN status='RESOLVED'
                   AND resolved_at >= CURRENT_TIMESTAMP - INTERVAL '7' DAY THEN 1 END)     AS resolved_this_week,
       COUNT(CASE WHEN reopened_count > 0 THEN 1 END)                                      AS total_reopened,
       ROUND(AVG(CASE WHEN status='RESOLVED' AND resolved_at IS NOT NULL THEN
         (EXTRACT(DAY    FROM (resolved_at - created_at)) * 86400 +
          EXTRACT(HOUR   FROM (resolved_at - created_at)) * 3600 +
          EXTRACT(MINUTE FROM (resolved_at - created_at)) * 60 +
          EXTRACT(SECOND FROM (resolved_at - created_at))) / 3600 END))                    AS avg_resolution_hours
     FROM tuneops_tickets
     WHERE company_id = :p1`,
    { p1: companyId }
  );
  return rows[0];
}

// ── Update ────────────────────────────────────────────────────────────────────

/**
 * Transition ticket to ACKNOWLEDGED status (active — reviewed, will execute).
 */
async function acknowledgeTicket(ticketNumber) {
  const upd = await pool.query(
    `UPDATE tuneops_tickets
     SET status = 'ACKNOWLEDGED', acknowledged_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
     WHERE ticket_number = :p1 AND status IN ('OPEN','REOPENED')`,
    { p1: ticketNumber }
  );
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(`SELECT * FROM tuneops_tickets WHERE ticket_number = :p1`, { p1: ticketNumber });
  return rows[0] || null;
}

/**
 * Transition ticket to EXECUTING status.
 * Blocked if requires_approval=true and approved_by is null (stub: always passes for Individual).
 */
async function executeTicket(ticketNumber, executedBy) {
  const upd = await pool.query(
    `UPDATE tuneops_tickets
     SET status = 'EXECUTING', executed_by = :p2, executed_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
     WHERE ticket_number = :p1 AND status = 'ACKNOWLEDGED'`,
    { p1: ticketNumber, p2: executedBy }
  );
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(`SELECT * FROM tuneops_tickets WHERE ticket_number = :p1`, { p1: ticketNumber });
  return rows[0] || null;
}

/**
 * Transition ticket to RESOLVED with optional notes.
 */
async function resolveTicket(ticketNumber, resolvedBy, resolutionNotes = null, executionResult = null) {
  const setParts = [
    "status = 'RESOLVED'", 'resolved_by = :p2', 'resolved_at = CURRENT_TIMESTAMP',
    'resolution_notes = :p3', 'updated_at = CURRENT_TIMESTAMP',
  ];
  const binds = { p1: ticketNumber, p2: resolvedBy, p3: resolutionNotes };
  if (executionResult) { setParts.push('execution_result = :p4'); binds.p4 = JSON.stringify(executionResult); }
  const upd = await pool.query(
    `UPDATE tuneops_tickets SET ${setParts.join(', ')}
     WHERE ticket_number = :p1 AND status IN ('EXECUTING','ACKNOWLEDGED','OPEN','REOPENED')`,
    binds
  );
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(`SELECT * FROM tuneops_tickets WHERE ticket_number = :p1`, { p1: ticketNumber });
  return rows[0] || null;
}

/**
 * Transition ticket to DEFERRED status (parked — seen, not acting now).
 */
async function deferTicket(ticketNumber, userId) {
  const upd = await pool.query(
    `UPDATE tuneops_tickets
     SET status = 'DEFERRED', assigned_to = :p2, updated_at = CURRENT_TIMESTAMP
     WHERE ticket_number = :p1 AND status IN ('OPEN','REOPENED')`,
    { p1: ticketNumber, p2: userId }
  );
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(`SELECT * FROM tuneops_tickets WHERE ticket_number = :p1`, { p1: ticketNumber });
  return rows[0] || null;
}

/**
 * Reopen a RESOLVED ticket (increments reopened_count).
 */
async function reopenTicket(ticketId, { severity, title, description } = {}) {
  const setClauses = [
    'status = \'REOPENED\'',
    'reopened_count = reopened_count + 1',
    'resolved_at = NULL',
    'resolved_by = NULL',
    'resolution_notes = NULL',
    'updated_at = CURRENT_TIMESTAMP',
  ];
  const binds = { p1: ticketId };
  if (severity) { setClauses.push('severity = :b_sev'); binds.b_sev = severity; }
  if (title) { setClauses.push('title = :b_title'); binds.b_title = title; }
  if (description) { setClauses.push('description = :b_desc'); binds.b_desc = description; }

  const upd = await pool.query(
    `UPDATE tuneops_tickets SET ${setClauses.join(', ')} WHERE id = :p1`,
    binds
  );
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(`SELECT * FROM tuneops_tickets WHERE id = :p1`, { p1: ticketId });
  return rows[0] || null;
}

/**
 * Auto-resolve a ticket by canonical key (health check cleared the finding).
 */
async function autoResolveByCanonicalKey(canonicalKey, healthCheckId) {
  const notes = `Auto-resolved — finding cleared on health check run #${healthCheckId}`;
  const upd = await pool.query(
    `UPDATE tuneops_tickets
     SET status = 'RESOLVED',
         resolved_at = CURRENT_TIMESTAMP,
         resolution_notes = :p2,
         updated_at = CURRENT_TIMESTAMP
     WHERE canonical_key = :p1
       AND status IN ('OPEN','ACKNOWLEDGED','DEFERRED','REOPENED')
     RETURNING id INTO :out_ids`,
    { p1: canonicalKey, p2: notes, out_ids: pool.outNumber() }
  );
  const ids = (upd.outBinds && upd.outBinds.out_ids) || [];
  if (!ids.length) return [];
  const inb = {}; const names = ids.map((v, i) => { inb['i' + i] = v; return ':i' + i; });
  const { rows } = await pool.query(`SELECT * FROM tuneops_tickets WHERE id IN (${names.join(', ')})`, inb);
  return rows;
}

/**
 * Update severity (worsening only) on an open ticket.
 */
async function worsenSeverity(ticketId, newSeverity, description) {
  const SEV_RANK = { info: 1, warning: 2, critical: 3 };

  // Only update if new severity is actually worse
  const upd = await pool.query(
    `UPDATE tuneops_tickets
     SET severity = :p2,
         description = :p3,
         updated_at = CURRENT_TIMESTAMP
     WHERE id = :p1
       AND (CASE severity WHEN 'info' THEN 1 WHEN 'warning' THEN 2 ELSE 3 END)
           < (CASE :p2 WHEN 'info' THEN 1 WHEN 'warning' THEN 2 ELSE 3 END)`,
    { p1: ticketId, p2: newSeverity, p3: description }
  );
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(`SELECT * FROM tuneops_tickets WHERE id = :p1`, { p1: ticketId });
  return rows[0] || null;
}

/**
 * A matching finding recurred on an already-open ticket: always bump
 * occurrence_count + last_seen_at, and worsen severity/description only when the
 * new severity is strictly higher than the current one.
 */
async function recordOccurrence(ticketId, newSeverity, description) {
  const upd = await pool.query(
    `UPDATE tuneops_tickets
        SET occurrence_count = COALESCE(occurrence_count, 1) + 1,
            last_seen_at     = CURRENT_TIMESTAMP,
            updated_at       = CURRENT_TIMESTAMP,
            severity = CASE
              WHEN (CASE severity WHEN 'info' THEN 1 WHEN 'warning' THEN 2 ELSE 3 END)
                 < (CASE :p2 WHEN 'info' THEN 1 WHEN 'warning' THEN 2 ELSE 3 END)
              THEN :p2 ELSE severity END,
            description = CASE
              WHEN (CASE severity WHEN 'info' THEN 1 WHEN 'warning' THEN 2 ELSE 3 END)
                 < (CASE :p2 WHEN 'info' THEN 1 WHEN 'warning' THEN 2 ELSE 3 END)
              THEN :p3 ELSE description END
      WHERE id = :p1`,
    { p1: ticketId, p2: newSeverity, p3: description }
  );
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(`SELECT * FROM tuneops_tickets WHERE id = :p1`, { p1: ticketId });
  return rows[0] || null;
}

/**
 * General-purpose patch for status, assignment, notes.
 */
async function patchTicket(ticketNumber, { assignedTo, resolutionNotes, description }) {
  const setClauses = ['updated_at = CURRENT_TIMESTAMP'];
  const binds = { p1: ticketNumber };
  if (assignedTo !== undefined) { setClauses.push('assigned_to = :b_assigned'); binds.b_assigned = assignedTo; }
  if (resolutionNotes !== undefined) { setClauses.push('resolution_notes = :b_notes'); binds.b_notes = resolutionNotes; }
  if (description !== undefined) { setClauses.push('description = :b_desc'); binds.b_desc = description; }

  if (setClauses.length === 1) return null; // nothing to update

  const upd = await pool.query(
    `UPDATE tuneops_tickets SET ${setClauses.join(', ')} WHERE ticket_number = :p1`,
    binds
  );
  if (!upd.rowCount) return null;
  const { rows } = await pool.query(`SELECT * FROM tuneops_tickets WHERE ticket_number = :p1`, { p1: ticketNumber });
  return rows[0] || null;
}

// ── Demo seeding ──────────────────────────────────────────────────────────────

/**
 * Returns true if demo tickets already exist for this company.
 */
async function demoTicketsExist(companyId) {
  const { rows } = await pool.query(
    `SELECT 1 FROM tuneops_tickets WHERE company_id = :p1 AND JSON_VALUE(source_reference, '$.is_demo') = 'true' FETCH FIRST 1 ROWS ONLY`,
    { p1: companyId }
  );
  return rows.length > 0;
}

/**
 * Bulk insert demo tickets (used during demo health check seeding).
 */
async function insertDemoTickets(tickets) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const created = [];
    for (const t of tickets) {
      try {
        await client.query(
          `INSERT INTO tuneops_tickets
             (company_id, connection_id, ticket_number, canonical_key, title, description,
              severity, status, source, source_reference, recommended_fix, fix_type,
              requires_approval, reopened_count,
              resolved_at, resolution_notes, created_at, updated_at)
           VALUES (:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13,:p14,:p15,:p16,
                   CURRENT_TIMESTAMP - NUMTODSINTERVAL(:p17, 'MINUTE'),
                   CURRENT_TIMESTAMP - NUMTODSINTERVAL(:p17, 'MINUTE'))`,
          {
            p1: t.companyId, p2: t.connectionId, p3: t.ticketNumber, p4: t.canonicalKey,
            p5: t.title, p6: t.description, p7: t.severity, p8: t.status,
            p9: t.source, p10: JSON.stringify(t.sourceReference),
            p11: t.recommendedFix, p12: t.fixType, p13: pool.bool(false), p14: t.reopenedCount || 0,
            p15: pool.tstz(t.resolvedAt || null), p16: t.resolutionNotes || null,
            p17: t.ageMinutes || 0,
          }
        );
      } catch (err) {
        if (err.errorNum === 1) continue;   // ORA-00001: ticket_number exists (DO NOTHING)
        throw err;
      }
      const { rows } = await client.query(
        `SELECT * FROM tuneops_tickets WHERE ticket_number = :p1`, { p1: t.ticketNumber });
      if (rows[0]) created.push(rows[0]);
    }
    await client.query('COMMIT');
    return created;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

module.exports = {
  createTicket,
  findOpenByCanonicalKey,
  findResolvedByCanonicalKey,
  getByTicketNumber,
  listTickets,
  countTickets,
  getStats,
  acknowledgeTicket,
  executeTicket,
  resolveTicket,
  deferTicket,
  reopenTicket,
  autoResolveByCanonicalKey,
  worsenSeverity,
  recordOccurrence,
  patchTicket,
  demoTicketsExist,
  insertDemoTickets,
  nextTicketNumber,
};
