function createProductionService({ db }) {
  function listRecords(tableName, divisionId) {
    return db.prepare(`
      SELECT record.*, item.title AS item_title
      FROM ${tableName} record
      LEFT JOIN items item ON item.id = record.item_id
      WHERE record.division_id = ?
      ORDER BY COALESCE(record.updated_at, record.created_at) DESC, record.id DESC
    `).all(divisionId);
  }

  function insertProductionAsset(payload) {
    const now = new Date().toISOString();
    const result = db.prepare(`
      INSERT INTO production_assets (division_id, item_id, asset_type, label, status, source_url, notes, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      Number(payload.division_id) || 4,
      payload.item_id ? Number(payload.item_id) : null,
      String(payload.asset_type || 'brief').trim(),
      String(payload.label || '').trim(),
      String(payload.status || 'missing').trim(),
      String(payload.source_url || '').trim(),
      String(payload.notes || '').trim(),
      now,
      now
    );
    return result.lastInsertRowid;
  }

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

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

  function insertStagingRun(payload) {
    const now = new Date().toISOString();
    const result = db.prepare(`
      INSERT INTO staging_runs (division_id, item_id, environment_label, staging_url, status, last_checked_at, notes, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      Number(payload.division_id) || 4,
      payload.item_id ? Number(payload.item_id) : null,
      String(payload.environment_label || 'Staging').trim(),
      String(payload.staging_url || '').trim(),
      String(payload.status || 'pending').trim(),
      String(payload.last_checked_at || now).trim(),
      String(payload.notes || '').trim(),
      now,
      now
    );
    return result.lastInsertRowid;
  }

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

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

  function insertScopeControl(payload) {
    const now = new Date().toISOString();
    const result = db.prepare(`
      INSERT INTO scope_controls (division_id, item_id, title, status, impact_level, notes, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      Number(payload.division_id) || 4,
      payload.item_id ? Number(payload.item_id) : null,
      String(payload.title || '').trim(),
      String(payload.status || 'approved').trim(),
      String(payload.impact_level || 'medium').trim(),
      String(payload.notes || '').trim(),
      now,
      now
    );
    return result.lastInsertRowid;
  }

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

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

  return {
    listProductionAssets(divisionId = 4) {
      return listRecords('production_assets', divisionId);
    },
    insertProductionAsset,
    updateProductionAsset,
    deleteProductionAsset,
    listStagingRuns(divisionId = 4) {
      return listRecords('staging_runs', divisionId);
    },
    insertStagingRun,
    updateStagingRun,
    deleteStagingRun,
    listScopeControls(divisionId = 4) {
      return listRecords('scope_controls', divisionId);
    },
    insertScopeControl,
    updateScopeControl,
    deleteScopeControl
  };
}

module.exports = { createProductionService };
