-- sql/project_premises_resync.sql
--
-- Re-resolves projects.project_premises from the project boundaries, for every
-- project or for one, and reports what moved.
--
-- ============================================================================
-- THIS IS NOT A MIGRATION. Do not move it into db/ — migrate.php globs db/*.sql
-- and would apply it as one, against every database, once. Same reasoning as
-- sql/util_delete_dynamic_field.sql.
--
-- IT IS A DRY RUN BY DEFAULT. The work happens inside a transaction that ends
-- in ROLLBACK, so you see the report before anything is written. To actually
-- write, change the ROLLBACK at the foot to COMMIT.
-- ============================================================================
--
-- WHEN TO RUN IT.
--
--   - After sql/abp_sync.sql. The address refresh introduces new-build premises
--     inside existing boundaries with no boundary change, so no trigger fires
--     and nothing else catches them. This is the only routine reason.
--   - After any boundary written by manual SQL rather than through the app.
--   - After a project is deleted outside the app. The FK cascades its premise
--     rows away, but its ancestors is_valid for those UPRNs is then stale and
--     there is no trigger to catch it, because the rows are already gone.
--   - As the repair for anything unusual. A partial refresh leaving a stale
--     is_valid somewhere is silent; this is both the check and the fix.
--
-- It calls projects.refresh_project_premises() — the same function the triggers
-- and migration 057 seed call — so there is one definition of what is inside a
-- boundary and what is valid, and it cannot drift from what the app maintains.
--
-- Run it in psql, where the per-project NOTICE lines stream as it works. Under
-- PDO the notices are swallowed; the two result tables below are the report.
--
-- EXPECT ZERO. On a healthy database, run straight after the app has been
-- maintaining the table, every project reports 0 added and 0 removed and the
-- first result set comes back empty. That is the pass condition, not an error.

BEGIN;

-- ─── EDIT THIS ──────────────────────────────────────────────────────────────
-- NULL resyncs every project. Put a project_id there to resync just one.
-- ────────────────────────────────────────────────────────────────────────────

CREATE TEMP TABLE resync_result ON COMMIT DROP AS
SELECT * FROM projects.refresh_project_premises(NULL);

-- 1. What moved, per project. Empty is the healthy answer.
--    Drop the WHERE to list every project scanned, including the quiet ones.
SELECT r.project_id,
       p.project_name,
       r.uprn_added,
       r.uprn_removed
  FROM resync_result r
  LEFT JOIN projects.projects p ON p.project_id = r.project_id
 WHERE r.uprn_added > 0 OR r.uprn_removed > 0
 ORDER BY r.project_id;

-- 2. Totals.
SELECT count(*)                                              AS projects_scanned,
       count(*) FILTER (WHERE uprn_added > 0
                           OR uprn_removed > 0)              AS projects_changed,
       COALESCE(sum(uprn_added), 0)                          AS uprn_added,
       COALESCE(sum(uprn_removed), 0)                        AS uprn_removed
  FROM resync_result;

-- ─── Change to COMMIT to write the changes above ────────────────────────────
ROLLBACK;
-- COMMIT;
