-- ============================================================
-- Migration 014 — Campos de endereço estruturado (CEP + número)
-- Adiciona CEP, número, cidade e estado nas tabelas:
--   demands, technical_visits, agenda_events
-- Citizens já possui todos estes campos.
-- Compatível com MySQL 5.7, 8.0+ e MariaDB — INFORMATION_SCHEMA.
-- ============================================================

SET @db = DATABASE();

-- ── demands ─────────────────────────────────────────────────

SET @q = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='demands' AND COLUMN_NAME='occur_cep');
SET @s = IF(@q=0,'ALTER TABLE demands ADD COLUMN occur_cep VARCHAR(9) NULL AFTER occur_address','SELECT ''ok'' AS r');
PREPARE p FROM @s; EXECUTE p; DEALLOCATE PREPARE p;

SET @q = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='demands' AND COLUMN_NAME='occur_number');
SET @s = IF(@q=0,'ALTER TABLE demands ADD COLUMN occur_number VARCHAR(20) NULL AFTER occur_cep','SELECT ''ok'' AS r');
PREPARE p FROM @s; EXECUTE p; DEALLOCATE PREPARE p;

SET @q = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='demands' AND COLUMN_NAME='occur_city');
SET @s = IF(@q=0,'ALTER TABLE demands ADD COLUMN occur_city VARCHAR(80) NULL AFTER occur_number','SELECT ''ok'' AS r');
PREPARE p FROM @s; EXECUTE p; DEALLOCATE PREPARE p;

SET @q = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='demands' AND COLUMN_NAME='occur_state');
SET @s = IF(@q=0,'ALTER TABLE demands ADD COLUMN occur_state VARCHAR(2) NULL AFTER occur_city','SELECT ''ok'' AS r');
PREPARE p FROM @s; EXECUTE p; DEALLOCATE PREPARE p;

-- ── technical_visits ────────────────────────────────────────

SET @q = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='technical_visits' AND COLUMN_NAME='cep');
SET @s = IF(@q=0,'ALTER TABLE technical_visits ADD COLUMN cep VARCHAR(9) NULL AFTER address','SELECT ''ok'' AS r');
PREPARE p FROM @s; EXECUTE p; DEALLOCATE PREPARE p;

SET @q = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='technical_visits' AND COLUMN_NAME='address_number');
SET @s = IF(@q=0,'ALTER TABLE technical_visits ADD COLUMN address_number VARCHAR(20) NULL AFTER cep','SELECT ''ok'' AS r');
PREPARE p FROM @s; EXECUTE p; DEALLOCATE PREPARE p;

SET @q = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='technical_visits' AND COLUMN_NAME='city');
SET @s = IF(@q=0,'ALTER TABLE technical_visits ADD COLUMN city VARCHAR(80) NULL AFTER address_number','SELECT ''ok'' AS r');
PREPARE p FROM @s; EXECUTE p; DEALLOCATE PREPARE p;

SET @q = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='technical_visits' AND COLUMN_NAME='state');
SET @s = IF(@q=0,'ALTER TABLE technical_visits ADD COLUMN state VARCHAR(2) NULL AFTER city','SELECT ''ok'' AS r');
PREPARE p FROM @s; EXECUTE p; DEALLOCATE PREPARE p;

-- ── agenda_events ────────────────────────────────────────────

SET @q = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='agenda_events' AND COLUMN_NAME='location_cep');
SET @s = IF(@q=0,'ALTER TABLE agenda_events ADD COLUMN location_cep VARCHAR(9) NULL AFTER location','SELECT ''ok'' AS r');
PREPARE p FROM @s; EXECUTE p; DEALLOCATE PREPARE p;

SET @q = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='agenda_events' AND COLUMN_NAME='location_number');
SET @s = IF(@q=0,'ALTER TABLE agenda_events ADD COLUMN location_number VARCHAR(20) NULL AFTER location_cep','SELECT ''ok'' AS r');
PREPARE p FROM @s; EXECUTE p; DEALLOCATE PREPARE p;

SET @q = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='agenda_events' AND COLUMN_NAME='location_city');
SET @s = IF(@q=0,'ALTER TABLE agenda_events ADD COLUMN location_city VARCHAR(80) NULL AFTER location_number','SELECT ''ok'' AS r');
PREPARE p FROM @s; EXECUTE p; DEALLOCATE PREPARE p;

SET @q = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='agenda_events' AND COLUMN_NAME='location_state');
SET @s = IF(@q=0,'ALTER TABLE agenda_events ADD COLUMN location_state VARCHAR(2) NULL AFTER location_city','SELECT ''ok'' AS r');
PREPARE p FROM @s; EXECUTE p; DEALLOCATE PREPARE p;