function createQAService({ db }) {
  const defaultChecks = [
    ['Mobile & Browser QA', 'standard'],
    ['Forms, Links, and Tracking', 'critical'],
    ['Performance & Core Web Vitals', 'critical'],
    ['SEO, Accessibility, and Final Polish', 'standard']
  ];

  const defaultGates = [
    'Zero-Bug Checklist Cleared',
    'Client Approval Package Ready',
    'Ready For Public / Success Handoff'
  ];

  function ensureQaCoverage(divisionId = 5) {
    const items = db.prepare("SELECT id FROM items WHERE division_id = ? AND title != '⚡ Executive Orders'").all(divisionId);
    const insert = db.prepare(`
      INSERT INTO qa_checks (division_id, item_id, check_name, status, severity, notes, created_at, updated_at)
      VALUES (?, ?, ?, 'pending', ?, '', ?, ?)
    `);
    const now = new Date().toISOString();
    items.forEach((item) => {
      const count = db.prepare('SELECT COUNT(*) as c FROM qa_checks WHERE division_id = ? AND item_id = ?').get(divisionId, item.id).c;
      if (count > 0) return;
      defaultChecks.forEach(([checkName, severity]) => insert.run(divisionId, item.id, checkName, severity, now, now));
    });
  }

  function ensureReleaseGates(divisionId = 5) {
    const items = db.prepare("SELECT id FROM items WHERE division_id = ? AND title != '⚡ Executive Orders'").all(divisionId);
    const insert = db.prepare(`
      INSERT INTO release_gates (division_id, item_id, gate_name, status, notes, created_at, updated_at)
      VALUES (?, ?, ?, 'pending', '', ?, ?)
    `);
    const now = new Date().toISOString();
    items.forEach((item) => {
      const count = db.prepare('SELECT COUNT(*) as c FROM release_gates WHERE division_id = ? AND item_id = ?').get(divisionId, item.id).c;
      if (count > 0) return;
      defaultGates.forEach((gateName) => insert.run(divisionId, item.id, gateName, now, now));
    });
  }

  function listQaChecks(divisionId = 5) {
    ensureQaCoverage(divisionId);
    return db.prepare(`
      SELECT qc.*, item.title AS item_title
      FROM qa_checks qc
      LEFT JOIN items item ON item.id = qc.item_id
      WHERE qc.division_id = ?
      ORDER BY COALESCE(qc.updated_at, qc.created_at) DESC, qc.id DESC
    `).all(divisionId);
  }

  function listReleaseGates(divisionId = 5) {
    ensureReleaseGates(divisionId);
    return db.prepare(`
      SELECT rg.*, item.title AS item_title
      FROM release_gates rg
      LEFT JOIN items item ON item.id = rg.item_id
      WHERE rg.division_id = ?
      ORDER BY COALESCE(rg.updated_at, rg.created_at) DESC, rg.id DESC
    `).all(divisionId);
  }

  function insertQaCheck(payload) {
    const now = new Date().toISOString();
    const result = db.prepare(`
      INSERT INTO qa_checks (division_id, item_id, check_name, status, severity, notes, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      Number(payload.division_id) || 5,
      payload.item_id ? Number(payload.item_id) : null,
      String(payload.check_name || '').trim(),
      String(payload.status || 'pending').trim(),
      String(payload.severity || 'standard').trim(),
      String(payload.notes || '').trim(),
      now,
      now
    );
    return result.lastInsertRowid;
  }

  function updateQaCheck(id, payload) {
    const existing = db.prepare('SELECT * FROM qa_checks WHERE id = ?').get(id);
    if (!existing) return false;
    db.prepare(`
      UPDATE qa_checks
      SET item_id = ?,
          check_name = ?,
          status = ?,
          severity = ?,
          notes = ?,
          updated_at = ?
      WHERE id = ?
    `).run(
      payload.item_id ? Number(payload.item_id) : null,
      String(payload.check_name || existing.check_name || '').trim(),
      String(payload.status || existing.status || 'pending').trim(),
      String(payload.severity || existing.severity || 'standard').trim(),
      String(payload.notes || existing.notes || '').trim(),
      new Date().toISOString(),
      id
    );
    return true;
  }

  function deleteQaCheck(id) {
    db.prepare('DELETE FROM qa_checks WHERE id = ?').run(id);
  }

  function insertReleaseGate(payload) {
    const now = new Date().toISOString();
    const result = db.prepare(`
      INSERT INTO release_gates (division_id, item_id, gate_name, status, notes, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `).run(
      Number(payload.division_id) || 5,
      payload.item_id ? Number(payload.item_id) : null,
      String(payload.gate_name || '').trim(),
      String(payload.status || 'pending').trim(),
      String(payload.notes || '').trim(),
      now,
      now
    );
    return result.lastInsertRowid;
  }

  function updateReleaseGate(id, payload) {
    const existing = db.prepare('SELECT * FROM release_gates WHERE id = ?').get(id);
    if (!existing) return false;
    db.prepare(`
      UPDATE release_gates
      SET item_id = ?,
          gate_name = ?,
          status = ?,
          notes = ?,
          updated_at = ?
      WHERE id = ?
    `).run(
      payload.item_id ? Number(payload.item_id) : null,
      String(payload.gate_name || existing.gate_name || '').trim(),
      String(payload.status || existing.status || 'pending').trim(),
      String(payload.notes || existing.notes || '').trim(),
      new Date().toISOString(),
      id
    );
    return true;
  }

  function deleteReleaseGate(id) {
    db.prepare('DELETE FROM release_gates WHERE id = ?').run(id);
  }

  return {
    listQaChecks,
    insertQaCheck,
    updateQaCheck,
    deleteQaCheck,
    listReleaseGates,
    insertReleaseGate,
    updateReleaseGate,
    deleteReleaseGate
  };
}

module.exports = { createQAService };
