-- =====================================================================
-- MIGRATION 015  (Bloco 13) — Tabelas administrativas de status/prioridade
-- Cria 5 tabelas de apoio (slug estável = valor ENUM atual) e faz o seed
-- com EXATAMENTE os valores existentes. NÃO altera as tabelas de negócio
-- ainda (isso é a 016). sla_status NÃO entra (permanece ENUM calculado).
-- Idempotente: cria só se não existir; seed via INSERT ... ON DUPLICATE KEY.
-- =====================================================================
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS demand_statuses (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED NOT NULL DEFAULT 1,
  slug        VARCHAR(30)  NOT NULL,
  label       VARCHAR(60)  NOT NULL,
  color       VARCHAR(20)  NULL,
  sort_order  INT          NOT NULL DEFAULT 0,
  is_active   TINYINT(1)   NOT NULL DEFAULT 1,
  is_final    TINYINT(1)   NOT NULL DEFAULT 0,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_demand_status_slug (tenant_id, slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS demand_priorities (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED NOT NULL DEFAULT 1,
  slug        VARCHAR(30)  NOT NULL,
  label       VARCHAR(60)  NOT NULL,
  color       VARCHAR(20)  NULL,
  sort_order  INT          NOT NULL DEFAULT 0,
  is_active   TINYINT(1)   NOT NULL DEFAULT 1,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_demand_priority_slug (tenant_id, slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS visit_statuses (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED NOT NULL DEFAULT 1,
  slug        VARCHAR(30)  NOT NULL,
  label       VARCHAR(60)  NOT NULL,
  color       VARCHAR(20)  NULL,
  sort_order  INT          NOT NULL DEFAULT 0,
  is_active   TINYINT(1)   NOT NULL DEFAULT 1,
  is_final    TINYINT(1)   NOT NULL DEFAULT 0,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_visit_status_slug (tenant_id, slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS task_statuses (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED NOT NULL DEFAULT 1,
  slug        VARCHAR(30)  NOT NULL,
  label       VARCHAR(60)  NOT NULL,
  color       VARCHAR(20)  NULL,
  sort_order  INT          NOT NULL DEFAULT 0,
  is_active   TINYINT(1)   NOT NULL DEFAULT 1,
  is_final    TINYINT(1)   NOT NULL DEFAULT 0,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_task_status_slug (tenant_id, slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS task_priorities (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED NOT NULL DEFAULT 1,
  slug        VARCHAR(30)  NOT NULL,
  label       VARCHAR(60)  NOT NULL,
  color       VARCHAR(20)  NULL,
  sort_order  INT          NOT NULL DEFAULT 0,
  is_active   TINYINT(1)   NOT NULL DEFAULT 1,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_task_priority_slug (tenant_id, slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- SEED — slugs EXATAMENTE iguais aos ENUM atuais.
INSERT INTO demand_statuses (tenant_id, slug, label, sort_order, is_final) VALUES
 (1,'nova','Nova',1,0),
 (1,'em_analise','Em análise',2,0),
 (1,'encaminhada','Encaminhada',3,0),
 (1,'em_andamento','Em andamento',4,0),
 (1,'aguardando_retorno','Aguardando retorno',5,0),
 (1,'resolvida','Resolvida',6,1),
 (1,'arquivada','Arquivada',7,1)
ON DUPLICATE KEY UPDATE label=VALUES(label), sort_order=VALUES(sort_order), is_final=VALUES(is_final);

INSERT INTO demand_priorities (tenant_id, slug, label, sort_order) VALUES
 (1,'baixa','Baixa',1),(1,'media','Média',2),(1,'alta','Alta',3),(1,'urgente','Urgente',4)
ON DUPLICATE KEY UPDATE label=VALUES(label), sort_order=VALUES(sort_order);

INSERT INTO visit_statuses (tenant_id, slug, label, sort_order, is_final) VALUES
 (1,'agendada','Agendada',1,0),(1,'realizada','Realizada',2,1),
 (1,'cancelada','Cancelada',3,1),(1,'remarcada','Remarcada',4,0)
ON DUPLICATE KEY UPDATE label=VALUES(label), sort_order=VALUES(sort_order), is_final=VALUES(is_final);

INSERT INTO task_statuses (tenant_id, slug, label, sort_order, is_final) VALUES
 (1,'aberta','Aberta',1,0),(1,'em_andamento','Em andamento',2,0),
 (1,'concluida','Concluída',3,1),(1,'cancelada','Cancelada',4,1)
ON DUPLICATE KEY UPDATE label=VALUES(label), sort_order=VALUES(sort_order), is_final=VALUES(is_final);

INSERT INTO task_priorities (tenant_id, slug, label, sort_order) VALUES
 (1,'baixa','Baixa',1),(1,'media','Média',2),(1,'alta','Alta',3),(1,'urgente','Urgente',4)
ON DUPLICATE KEY UPDATE label=VALUES(label), sort_order=VALUES(sort_order);