-- Strict location-token scope migration for AwardSpace / MariaDB.
-- Run this once in phpMyAdmin after uploading the patched PHP files.
-- It is safe to re-run: missing columns are added dynamically.

SET @db_name := DATABASE();

-- Helper pattern: add column only when missing (compatible with MariaDB versions
-- that reject ALTER TABLE ... ADD COLUMN IF NOT EXISTS).
SET @sql := IF(
  EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='users' AND COLUMN_NAME='country'),
  'SELECT 1',
  'ALTER TABLE users ADD COLUMN country VARCHAR(120) NULL AFTER role'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
  EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='users' AND COLUMN_NAME='city'),
  'SELECT 1',
  'ALTER TABLE users ADD COLUMN city VARCHAR(120) NULL AFTER country'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
  EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='users' AND COLUMN_NAME='administered_legislation'),
  'SELECT 1',
  'ALTER TABLE users ADD COLUMN administered_legislation VARCHAR(190) NULL AFTER city'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Add city/country location tokens to all operational tables.
SET @sql := IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='portable_toilets' AND COLUMN_NAME='country'), 'SELECT 1', 'ALTER TABLE portable_toilets ADD COLUMN country VARCHAR(120) NULL DEFAULT ''Canada'' AFTER arrondissement');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='portable_toilets' AND COLUMN_NAME='city'), 'SELECT 1', 'ALTER TABLE portable_toilets ADD COLUMN city VARCHAR(120) NULL AFTER country');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='field_resources' AND COLUMN_NAME='country'), 'SELECT 1', 'ALTER TABLE field_resources ADD COLUMN country VARCHAR(120) NULL DEFAULT ''Canada'' AFTER arrondissement');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='field_resources' AND COLUMN_NAME='city'), 'SELECT 1', 'ALTER TABLE field_resources ADD COLUMN city VARCHAR(120) NULL AFTER country');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='incidents' AND COLUMN_NAME='country'), 'SELECT 1', 'ALTER TABLE incidents ADD COLUMN country VARCHAR(120) NULL DEFAULT ''Canada'' AFTER arrondissement');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='incidents' AND COLUMN_NAME='city'), 'SELECT 1', 'ALTER TABLE incidents ADD COLUMN city VARCHAR(120) NULL AFTER country');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='mobile_interventions' AND COLUMN_NAME='country'), 'SELECT 1', 'ALTER TABLE mobile_interventions ADD COLUMN country VARCHAR(120) NULL DEFAULT ''Canada'' AFTER arrondissement');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='mobile_interventions' AND COLUMN_NAME='city'), 'SELECT 1', 'ALTER TABLE mobile_interventions ADD COLUMN city VARCHAR(120) NULL AFTER country');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- National map token: the imported operational data in this project is Canadian.
UPDATE portable_toilets SET country='Canada' WHERE country IS NULL OR TRIM(country)='';
UPDATE field_resources SET country='Canada' WHERE country IS NULL OR TRIM(country)='';
UPDATE incidents SET country='Canada' WHERE country IS NULL OR TRIM(country)='';
UPDATE mobile_interventions SET country='Canada' WHERE country IS NULL OR TRIM(country)='';

-- Montréal city token: explicitly label rows located inside the Montréal metro box.
-- The PHP application no longer uses this box as a display fallback; this is a
-- one-time migration that writes the token into the database.
UPDATE portable_toilets
SET city='Montreal'
WHERE (city IS NULL OR TRIM(city)='')
  AND latitude BETWEEN 45.40 AND 45.75
  AND longitude BETWEEN -74.05 AND -73.35;

UPDATE field_resources
SET city='Montreal'
WHERE (city IS NULL OR TRIM(city)='')
  AND latitude BETWEEN 45.40 AND 45.75
  AND longitude BETWEEN -74.05 AND -73.35;

UPDATE incidents
SET city='Montreal'
WHERE (city IS NULL OR TRIM(city)='')
  AND latitude BETWEEN 45.40 AND 45.75
  AND longitude BETWEEN -74.05 AND -73.35;

-- mobile_interventions has no latitude/longitude columns. Existing rows created
-- by this project normally already use the Montreal default. If old rows are blank
-- and they are known to be Montréal interventions, run this manually:
-- UPDATE mobile_interventions SET city='Montreal' WHERE city IS NULL OR TRIM(city)='';

-- Configure admin users. Adapt emails and tokens to your actual users.
-- City admin: only rows where city matches this token are visible.
-- UPDATE users SET country='Canada', city='Montreal', administered_legislation='Montreal' WHERE email='city-admin@example.com';
-- National admin: only rows where country matches this token are visible.
-- UPDATE users SET country='Canada', city=NULL, administered_legislation='Canada' WHERE email='national-admin@example.com';

-- Verification queries.
SELECT id, full_name, email, role, country, city, administered_legislation
FROM users
ORDER BY role, id;

SELECT country, city, COUNT(*) AS resource_count
FROM field_resources
GROUP BY country, city
ORDER BY country, city;
