NethVoice: reports scheduler fails on stale agent_extensions and phonebook_map race #8316

Steps to reproduce

1. On a NethVoice installation with queues and some queue activity, wait for the nightly reports-scheduler.service run, or start it manually: systemctl --user start reports-scheduler.service.
2. Check the unit status and logs: systemctl --user status reports-scheduler.service, journalctl --user -u reports-scheduler.service.

Expected behavior

reports-scheduler.service completes successfully: queue-miner.php rebuilds agent_extensions on every run and tasks views rebuilds all the view tables.

Actual behavior

The scheduler fails, so the report tables stop being updated. Two independent problems:

1. agent_extensions is never rebuilt. In queue-miner.php every statement of the first batch was run with execute([$now]) and then again with query(). DROP TABLE agent_extensions and CREATE TABLE agent_extensions have no placeholder, so with PHP 8 (where PDO throws by default) execute() fails with SQLSTATE[HY093] and the table is never rebuilt. On NS7 (PHP 7, silent PDO errors) the following query() used to rebuild it. The stale table can contain duplicated extensions, and tasks views then fails on data_summary_agent.sql with Error 1242: Subquery returns more than 1 row. The logs also show error 1064 from the second query() on the statements that use the placeholder.
2. Race on phonebook_map. tasks views runs the view files in parallel, four at a time. data_call.sql and data_caller.sql both DROP/CREATE TABLE phonebook_map and almost always run together: one of them fails with Error 1050: Table 'phonebook_map' already exists.

Components

NethVoice reports (reports-api image, queue-miner.php executed in the freepbx container), nethvoice-report up to nethesis/nethvoice-report@c8d1ecc.

Fix

• queue-miner: rebuild agent_extensions on every run nethesis/nethvoice-report#233: bind $now only to the statements that use it and run each statement once.
• views: create phonebook_map only in data_call.sql nethesis/nethvoice-report#234: create phonebook_map only in data_call.sql (the definitions were identical and data_caller.sql doesn't use it).

Test case

With the testing image that includes both fixes:

1. Run systemctl --user start reports-scheduler.service a few times in a row: it must complete without errors, and the journal must not contain HY093, 1064, 1242 or 1050.
2. queue-miner.php must rebuild agent_extensions: after adding a queue agent (or renaming one) and receiving at least one queue call, run the scheduler again and check that the agent appears in asteriskcdrdb.agent_extensions with the current name, with exactly one row per extension:
   SELECT extension, COUNT(*) FROM asteriskcdrdb.agent_extensions GROUP BY extension HAVING COUNT(*) > 1; → no rows.
3. Open the queue reports (agent summary, hourly distribution by agent) and the CDR caller/call data pages: they must show up-to-date data, with agent names resolved.
4. phonebook_map must still be created by data_call.sql after tasks views (SHOW TABLES LIKE 'phonebook_map';).