-- =====================================================================
-- MIGRATION 018 (Bloco 14) — Metas do gabinete (goals)
-- Estrutura mínima para metas mensais (demandas resolvidas, visitas, tarefas).
-- Idempotente: cria só se não existir.
-- =====================================================================
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS goals (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id     BIGINT UNSIGNED NOT NULL DEFAULT 1,
  mandate_id    BIGINT UNSIGNED NULL,
  metric        VARCHAR(40)  NOT NULL,          -- demands_resolved | visits_done | tasks_done
  target_value  INT          NOT NULL DEFAULT 0,
  period_month  TINYINT      NOT NULL,          -- 1..12
  period_year   SMALLINT     NOT NULL,
  is_active     TINYINT(1)   NOT NULL DEFAULT 1,
  created_by    BIGINT UNSIGNED NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_goal (tenant_id, metric, period_month, period_year),
  KEY idx_goals_period (tenant_id, period_year, period_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;