-- =====================================================================
-- MIGRATION 021 — BLOCO 15: Central de Comunicação e Marketing
--
-- Cria a estrutura PRÓPRIA e DESACOPLADA da comunicação institucional.
-- Princípios:
--   * NÃO usa a tabela demands para armazenar dados de comunicação.
--   * communication_items referencia demands por FK; bairro/região/solicitante/
--     secretaria são LIDOS por JOIN (não duplicados). Guarda apenas um snapshot
--     mínimo (protocol, demand_title) para histórico/listagem rápida.
--   * Status da comunicação é INDEPENDENTE do status da demanda.
--   * Nomeado communication_* (não marketing_*) para escalar a todo o mandato
--     (eventos, visitas, obras, campanhas, datas comemorativas etc.).
--   * Idempotente: CREATE TABLE IF NOT EXISTS; seeds com guarda; permissões
--     inseridas só se não existirem. Seguro rodar em banco limpo e existente.
--
-- Compatível com MySQL 5.7/8.0+ e MariaDB 10.3+ (sem ENUM para status: VARCHAR
-- configurável, alinhado ao padrão demand_statuses).
-- =====================================================================
SET NAMES utf8mb4;

-- ---------------------------------------------------------------------
-- 1) Status configuráveis da comunicação (próprios, expansíveis)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS communication_statuses (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED NOT NULL DEFAULT 1,
  slug        VARCHAR(40)  NOT NULL,
  label       VARCHAR(80)  NOT NULL,
  color       VARCHAR(20)  NULL,
  sort_order  INT          NOT NULL DEFAULT 0,
  is_active   TINYINT(1)   NOT NULL DEFAULT 1,
  is_initial  TINYINT(1)   NOT NULL DEFAULT 0,  -- status ao criar item
  is_final    TINYINT(1)   NOT NULL DEFAULT 0,  -- publicado/arquivado/cancelado
  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_comm_status_slug (tenant_id, slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO communication_statuses (tenant_id, slug, label, sort_order, is_active, is_initial, is_final) VALUES
 (1,'recebido','Recebido',1,1,1,0),
 (1,'em_analise','Em análise',2,1,0,0),
 (1,'aguardando_midia','Aguardando mídia',3,1,0,0),
 (1,'material_incompleto','Material incompleto',4,1,0,0),
 (1,'arte_em_producao','Arte em produção',5,1,0,0),
 (1,'aguardando_aprovacao','Aguardando aprovação',6,1,0,0),
 (1,'pronto_publicar','Pronto para publicar',7,1,0,0),
 (1,'publicado','Publicado',8,1,0,1),
 (1,'arquivado','Arquivado',9,1,0,1),
 (1,'cancelado','Cancelado',10,1,0,1)
ON DUPLICATE KEY UPDATE label=VALUES(label), sort_order=VALUES(sort_order),
  is_initial=VALUES(is_initial), is_final=VALUES(is_final);

-- ---------------------------------------------------------------------
-- 2) Itens da Central de Comunicação (1 por encaminhamento de demanda)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS communication_items (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id      BIGINT UNSIGNED NOT NULL DEFAULT 1,
  demand_id      BIGINT UNSIGNED NOT NULL,           -- vínculo (não duplica a demanda)
  -- snapshot mínimo para listagem/histórico (dados vivos vêm por JOIN):
  demand_protocol VARCHAR(24)  NULL,
  demand_title    VARCHAR(200) NULL,
  status         VARCHAR(40)  NOT NULL DEFAULT 'recebido', -- status PRÓPRIO
  assigned_to    BIGINT UNSIGNED NULL,               -- responsável da Comunicação
  assigned_at    DATETIME     NULL,
  forwarded_by   BIGINT UNSIGNED NULL,               -- quem encaminhou (Equipe de Rua)
  forwarded_at   DATETIME     NULL,
  internal_notes TEXT         NULL,                  -- observações internas (só Comunicação)
  published_at   DATETIME     NULL,                  -- 1ª publicação (para tempo médio)
  created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at     DATETIME     NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_comm_demand (tenant_id, demand_id), -- NUNCA duplica por demanda
  KEY idx_comm_tenant (tenant_id),
  KEY idx_comm_status (status),
  KEY idx_comm_assigned (assigned_to),
  KEY idx_comm_forwarded_by (forwarded_by),
  KEY idx_comm_created (created_at),
  CONSTRAINT fk_comm_demand   FOREIGN KEY (demand_id)    REFERENCES demands(id) ON DELETE CASCADE,
  CONSTRAINT fk_comm_assigned FOREIGN KEY (assigned_to)  REFERENCES users(id)   ON DELETE SET NULL,
  CONSTRAINT fk_comm_forward  FOREIGN KEY (forwarded_by) REFERENCES users(id)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 3) Histórico de status da comunicação (auditável, espelha demand_status_history)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS communication_status_history (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  item_id      BIGINT UNSIGNED NOT NULL,
  user_id      BIGINT UNSIGNED NULL,
  event_type   VARCHAR(30)  NOT NULL DEFAULT 'mudanca_status', -- criacao|mudanca_status|atribuicao|checklist|publicacao|comentario|arquivamento
  status_from  VARCHAR(40)  NULL,
  status_to    VARCHAR(40)  NULL,
  comment      TEXT         NULL,
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_csh_item (item_id),
  KEY idx_csh_user (user_id),
  KEY idx_csh_created (created_at),
  CONSTRAINT fk_csh_item FOREIGN KEY (item_id) REFERENCES communication_items(id) ON DELETE CASCADE,
  CONSTRAINT fk_csh_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 4) Checklist por item (progresso de produção)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS communication_checklists (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  item_id     BIGINT UNSIGNED NOT NULL,
  slug        VARCHAR(40)  NOT NULL,     -- foto_antes, foto_depois, video, ...
  label       VARCHAR(120) NOT NULL,
  is_done     TINYINT(1)   NOT NULL DEFAULT 0,
  sort_order  INT          NOT NULL DEFAULT 0,
  done_by     BIGINT UNSIGNED NULL,
  done_at     DATETIME     NULL,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_checklist_item_slug (item_id, slug),
  KEY idx_checklist_item (item_id),
  CONSTRAINT fk_checklist_item FOREIGN KEY (item_id) REFERENCES communication_items(id) ON DELETE CASCADE,
  CONSTRAINT fk_checklist_user FOREIGN KEY (done_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 5) Publicações por canal (status/data/usuário/link)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS communication_publications (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  item_id       BIGINT UNSIGNED NOT NULL,
  channel       VARCHAR(40)  NOT NULL,   -- instagram_feed, instagram_story, instagram_reels, facebook, whatsapp, portal, youtube, tiktok
  status        VARCHAR(20)  NOT NULL DEFAULT 'nao_publicado', -- nao_publicado | publicado
  link          VARCHAR(500) NULL,
  published_by  BIGINT UNSIGNED NULL,
  published_at  DATETIME     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_pub_item_channel (item_id, channel),
  KEY idx_pub_item (item_id),
  KEY idx_pub_status (status),
  CONSTRAINT fk_pub_item FOREIGN KEY (item_id) REFERENCES communication_items(id) ON DELETE CASCADE,
  CONSTRAINT fk_pub_user FOREIGN KEY (published_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 6) Histórico de atribuições (quem assumiu, quando)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS communication_assignments (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  item_id      BIGINT UNSIGNED NOT NULL,
  assigned_to  BIGINT UNSIGNED NULL,
  assigned_by  BIGINT UNSIGNED NULL,
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_assign_item (item_id),
  CONSTRAINT fk_assign_item FOREIGN KEY (item_id)     REFERENCES communication_items(id) ON DELETE CASCADE,
  CONSTRAINT fk_assign_to   FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_assign_by   FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 7) Permissões próprias da comunicação (NÃO reutiliza as de demanda)
-- ---------------------------------------------------------------------
INSERT INTO permissions (slug, name, module)
SELECT * FROM (
  SELECT 'communication.view'    AS slug, 'Ver comunicação'            AS name, 'communication' AS module UNION ALL
  SELECT 'communication.create',  'Criar itens de comunicação',       'communication' UNION ALL
  SELECT 'communication.update',  'Editar comunicação',               'communication' UNION ALL
  SELECT 'communication.assign',  'Atribuir responsável de comunicação','communication' UNION ALL
  SELECT 'communication.publish', 'Publicar/registrar publicação',    'communication' UNION ALL
  SELECT 'communication.archive', 'Arquivar comunicação',             'communication'
) AS novos
WHERE NOT EXISTS (SELECT 1 FROM permissions p WHERE p.slug = novos.slug);

-- admin: todas as permissões de comunicação
INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r CROSS JOIN permissions p
WHERE r.slug='admin' AND p.module='communication'
  AND NOT EXISTS (SELECT 1 FROM role_permissions rp WHERE rp.role_id=r.id AND rp.permission_id=p.id);

-- chefe_gabinete: todas as permissões de comunicação
INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r CROSS JOIN permissions p
WHERE r.slug='chefe_gabinete' AND p.module='communication'
  AND NOT EXISTS (SELECT 1 FROM role_permissions rp WHERE rp.role_id=r.id AND rp.permission_id=p.id);

-- marketing: ver, editar, atribuir, publicar, arquivar (não cria via encaminhamento)
INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r CROSS JOIN permissions p
WHERE r.slug='marketing'
  AND p.slug IN ('communication.view','communication.update','communication.assign',
                 'communication.publish','communication.archive')
  AND NOT EXISTS (SELECT 1 FROM role_permissions rp WHERE rp.role_id=r.id AND rp.permission_id=p.id);

-- equipe_rua: ver e criar (encaminhar a partir da demanda)
INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r CROSS JOIN permissions p
WHERE r.slug='equipe_rua'
  AND p.slug IN ('communication.view','communication.create')
  AND NOT EXISTS (SELECT 1 FROM role_permissions rp WHERE rp.role_id=r.id AND rp.permission_id=p.id);

-- vereador: ver (acompanhamento)
INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r CROSS JOIN permissions p
WHERE r.slug='vereador'
  AND p.slug IN ('communication.view')
  AND NOT EXISTS (SELECT 1 FROM role_permissions rp WHERE rp.role_id=r.id AND rp.permission_id=p.id);