-- =====================================================================
-- Tabela: tb_adm_user_detail (valor de detalhe por adm_user — EAV)
-- Banco:  w19_clikil41_eleicao (operacional)
-- Projeto: f19
--
-- Ponte entre tb_adm_user e tb_detail. Cada linha = 1 valor de 1 tipo
-- de detalhe pra 1 adm_user. UNIQUE (id_adm_user, id_detail) — pra
-- múltiplos valores do mesmo tipo (ex: 2 telefones secundários), criar
-- variantes no catálogo tb_detail (phone_secondary_1, phone_secondary_2).
--
-- No futuro tb_contact_detail seguirá o mesmo padrão usando o mesmo
-- catálogo tb_detail (compartilhado entre adm_user e contact).
-- =====================================================================

CREATE TABLE IF NOT EXISTS `tb_adm_user_detail` (
  `id_adm_user_detail`         BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
  `id_adm_user`                BIGINT UNSIGNED   NOT NULL,
  `id_detail`                  SMALLINT UNSIGNED NOT NULL,

  `adm_user_detail_value`      TEXT              DEFAULT NULL,   -- valor literal (qualquer tipo serializado como string;
                                                                  -- JSON quando tb_detail.detail_data_type='json')
  `adm_user_detail_status`     TINYINT(1)        NOT NULL DEFAULT 1,
  `adm_user_detail_created_at` DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `adm_user_detail_updated_at` DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id_adm_user_detail`),
  UNIQUE KEY `uk_aud_pair`   (`id_adm_user`,`id_detail`),
  KEY `ix_aud_user`   (`id_adm_user`),
  KEY `ix_aud_detail` (`id_detail`),
  CONSTRAINT `fk_aud_user`   FOREIGN KEY (`id_adm_user`) REFERENCES `tb_adm_user` (`id_adm_user`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_aud_detail` FOREIGN KEY (`id_detail`)   REFERENCES `tb_detail`   (`id_detail`)   ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;