-- Future hierarchy upgrade for existing installs

ALTER TABLE users MODIFY role ENUM('admin_international','admin_national','admin_city','admin','coordinateur','agent','user') NOT NULL DEFAULT 'user';
ALTER TABLE users ADD COLUMN country VARCHAR(120) NULL AFTER role;
ALTER TABLE users ADD COLUMN city VARCHAR(120) NULL AFTER country;

ALTER TABLE portable_toilets ADD COLUMN country VARCHAR(120) NULL DEFAULT 'Canada' AFTER arrondissement;
ALTER TABLE portable_toilets ADD COLUMN city VARCHAR(120) NULL DEFAULT 'Montreal' AFTER country;
ALTER TABLE field_resources ADD COLUMN country VARCHAR(120) NULL DEFAULT 'Canada' AFTER arrondissement;
ALTER TABLE field_resources ADD COLUMN city VARCHAR(120) NULL DEFAULT 'Montreal' AFTER country;
ALTER TABLE incidents ADD COLUMN country VARCHAR(120) NULL DEFAULT 'Canada' AFTER arrondissement;
ALTER TABLE incidents ADD COLUMN city VARCHAR(120) NULL DEFAULT 'Montreal' AFTER country;
ALTER TABLE mobile_interventions ADD COLUMN country VARCHAR(120) NULL DEFAULT 'Canada' AFTER arrondissement;
ALTER TABLE mobile_interventions ADD COLUMN city VARCHAR(120) NULL DEFAULT 'Montreal' AFTER country;

ALTER TABLE messages MODIFY receiver_role ENUM('admin_international','admin_national','admin_city','coordinateur','agent','all') DEFAULT NULL;

-- Example role conversion:
-- UPDATE users SET role='admin_city', country='Canada', city='Montreal' WHERE role IN ('admin','coordinateur');
-- UPDATE users SET role='admin_national', country='Canada' WHERE email='national@example.org';
-- UPDATE users SET role='admin_international' WHERE email='global@example.org';
