-- =====================================================================
-- MIGRATION 001  (Bloco 2)
-- Ajustes estruturais em cidadãos e equipe.
-- Aplicar SOMENTE se você já instalou o Bloco 1. Em instalação nova,
-- o database.sql já contém tudo isto.
-- Esta migration é IDEMPOTENTE: rodar de novo não causa erro.
-- =====================================================================

-- Helpers defensivos (adicionam coluna/índice/FK só se ainda não existirem)
DROP PROCEDURE IF EXISTS sgg_add_col;
DROP PROCEDURE IF EXISTS sgg_add_idx;
DROP PROCEDURE IF EXISTS sgg_add_fk;
DELIMITER //
CREATE PROCEDURE sgg_add_col(IN tbl VARCHAR(64), IN col VARCHAR(64), IN ddl VARCHAR(255))
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns
    WHERE table_schema=DATABASE() AND table_name=tbl AND column_name=col) THEN
    SET @s=CONCAT('ALTER TABLE `',tbl,'` ADD COLUMN ',ddl); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
  END IF;
END //
CREATE PROCEDURE sgg_add_idx(IN tbl VARCHAR(64), IN idx VARCHAR(64), IN ddl VARCHAR(255))
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.statistics
    WHERE table_schema=DATABASE() AND table_name=tbl AND index_name=idx) THEN
    SET @s=CONCAT('ALTER TABLE `',tbl,'` ADD ',ddl); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
  END IF;
END //
CREATE PROCEDURE sgg_add_fk(IN tbl VARCHAR(64), IN fk VARCHAR(64), IN ddl VARCHAR(255))
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints
    WHERE table_schema=DATABASE() AND table_name=tbl AND constraint_name=fk) THEN
    SET @s=CONCAT('ALTER TABLE `',tbl,'` ADD CONSTRAINT `',fk,'` ',ddl); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
  END IF;
END //
DELIMITER ;

-- ---- CIDADÃOS ----
CALL sgg_add_col('citizens','nickname',"nickname VARCHAR(120) NULL AFTER name");
CALL sgg_add_col('citizens','is_community_leader',"is_community_leader TINYINT(1) NOT NULL DEFAULT 0 AFTER notes");
CALL sgg_add_col('citizens','source_id',"source_id BIGINT UNSIGNED NULL AFTER neighborhood_id");
CALL sgg_add_idx('citizens','idx_citizens_source','KEY idx_citizens_source (source_id)');
CALL sgg_add_idx('citizens','idx_citizens_leader','KEY idx_citizens_leader (is_community_leader)');
CALL sgg_add_fk('citizens','fk_citizens_source',
  "FOREIGN KEY (source_id) REFERENCES request_sources(id) ON DELETE SET NULL");

-- ---- EQUIPE (users) ---- (phone e position já existem desde o Bloco 1)
CALL sgg_add_col('users','region_id',"region_id BIGINT UNSIGNED NULL AFTER position");
CALL sgg_add_idx('users','idx_users_region','KEY idx_users_region (region_id)');
CALL sgg_add_fk('users','fk_users_region',
  "FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE SET NULL");

-- bairros atendidos por um membro da equipe (N:N)
CREATE TABLE IF NOT EXISTS user_neighborhoods (
  user_id         BIGINT UNSIGNED NOT NULL,
  neighborhood_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, neighborhood_id),
  KEY idx_un_neigh (neighborhood_id),
  CONSTRAINT fk_un_user  FOREIGN KEY (user_id)         REFERENCES users(id)         ON DELETE CASCADE,
  CONSTRAINT fk_un_neigh FOREIGN KEY (neighborhood_id) REFERENCES neighborhoods(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- ÍNDICES ÚNICOS PARA IDEMPOTÊNCIA DOS SEEDS ----
-- (neighborhoods e request_sources já têm unique no schema base.)
CALL sgg_add_idx('regions','uq_regions_name_tenant','UNIQUE KEY uq_regions_name_tenant (tenant_id, name)');
CALL sgg_add_idx('departments','uq_dept_acronym_tenant','UNIQUE KEY uq_dept_acronym_tenant (tenant_id, acronym)');

-- limpeza dos helpers
DROP PROCEDURE IF EXISTS sgg_add_col;
DROP PROCEDURE IF EXISTS sgg_add_idx;
DROP PROCEDURE IF EXISTS sgg_add_fk;