-- Aprova — esquema MySQL 8, versão 2
--
-- Substitui sql/schema.sql. Nada foi criado ainda em produção, então isto é
-- uma reescrita e não uma migração — não há dado para preservar.
--
-- O que mudou em relação à v1, e por quê está em docs/09-melhorias.md:
--
--   · imagens              → itens, com coluna `tipo`            (M2: vídeo entra no escopo)
--   · versoes              + duracao_s, poster_path, preview_path (M2)
--   · aprovacoes           tabela nova                            (M1: aprovação explícita)
--   · comentarios          + tempo_s, marca_tipo, pin_x2, pin_y2  (M2, M3)
--   · comentarios          − resposta  (virou tabela própria)     (M4)
--   · comentario_respostas tabela nova                            (M4: conversa)
--   · anexos               tabela nova                            (M5: referência no pedido)
--   · comentarios.origem   + rótulo de procedência                (M6: importação)
--   · etapas_modelo, etapas tabelas novas               (M13: etapas de produção)
--   · item_etapa           tabela nova                  (M13: o check do artista)
--   · comentarios          + etapa_id                   (M13)
--   · projetos             + mostrar_andamento          (M14: cliente acompanha)
--   · rodadas              + previsao_entrega           (M14)
--   · comentarios          + interno                    (M15: tarefa do estúdio)
--   · comentarios          + editado_em                 (M16: editar o que é seu)
--   · etapa_cronograma     tabela nova                  (M17: cronograma por etapa)
--   · usuarios             UNIQUE(email) → UNIQUE(estudio_id,email)   (D2, ver docs/11)
--   · usuarios             + email_verificado_em        (M18: contas)
--   · sessoes, tokens, tentativas_login, emails_enviados  tabelas novas (M18)
--   · clientes, revisor_projeto  tabelas novas             (M19: link por pessoa)
--   · revisores            + cliente_id; nunca tem senha (D8)
--   · revisor_projeto      + token_hash: o link é por pessoa E por projeto
--   · projetos             cliente_nome → cliente_id       (M19)
--   · rodadas              + link_aberto                   (M19)
--
-- Mantido da v1 sem alteração de intenção: multi-inquilino por coluna (D2),
-- fase da rodada como guarda de autorização, auditoria em `eventos`.
--
-- Convenções: utf8mb4, InnoDB, timestamps UTC, ids BIGINT AUTO_INCREMENT,
-- tokens e chaves públicas em CHAR(32) aleatório (nunca id sequencial exposto).

SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- ---------------------------------------------------------------- inquilinos

CREATE TABLE estudios (
  id              BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nome            VARCHAR(120)  NOT NULL,
  slug            VARCHAR(60)   NOT NULL,
  plano           ENUM('trial','estudio','time','sob_medida','suspenso') NOT NULL DEFAULT 'trial',
  -- marca exibida na página do cliente (white label)
  logo_path       VARCHAR(255)  NULL,
  cor_destaque    CHAR(7)       NULL,
  dominio_proprio VARCHAR(180)  NULL,
  -- cotas do plano; NULL = ilimitado. Convidado do lado do cliente nunca conta
  -- como assento — é o que torna o produto vendável (docs/09, P1).
  limite_usuarios         SMALLINT UNSIGNED NULL,
  limite_projetos_ativos  SMALLINT UNSIGNED NULL,
  limite_armazenamento_mb BIGINT UNSIGNED NULL,
  criado_em       TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_estudios_slug (slug),
  UNIQUE KEY uq_estudios_dominio (dominio_proprio)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE usuarios (
  id            BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  estudio_id    BIGINT UNSIGNED NOT NULL,
  nome          VARCHAR(120) NOT NULL,
  email         VARCHAR(190) NOT NULL,
  senha_hash    VARCHAR(255) NULL,              -- password_hash(); NULL até aceitar o convite
  papel         ENUM('admin','equipe') NOT NULL DEFAULT 'equipe',
  ativo         TINYINT(1)   NOT NULL DEFAULT 1,
  email_verificado_em TIMESTAMP NULL,           -- sem isto, não convida ninguém
  ultimo_acesso TIMESTAMP    NULL,
  criado_em     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  -- A unicidade é POR ESTÚDIO, não global. A v1 tinha UNIQUE(email) e isso
  -- contradiz D2: a mesma pessoa pode ser terceirizada de dois estúdios, e o
  -- primeiro que cadastrasse trancaria o endereço para o outro.
  UNIQUE KEY uq_usuarios_email (estudio_id, email),
  KEY ix_usuarios_email (email),
  KEY ix_usuarios_estudio (estudio_id),
  CONSTRAINT fk_usuarios_estudio FOREIGN KEY (estudio_id) REFERENCES estudios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------------- cliente
-- M19. O cliente virou entidade. Era só `projetos.cliente_nome`, um texto, e
-- por isso não havia onde pendurar as pessoas dele nem o histórico entre
-- projetos — que é justamente o que dá ao gerente uma casa para voltar.

CREATE TABLE clientes (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  estudio_id  BIGINT UNSIGNED NOT NULL,
  nome        VARCHAR(160) NOT NULL,
  criado_em   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_clientes_estudio (estudio_id),
  CONSTRAINT fk_clientes_estudio FOREIGN KEY (estudio_id) REFERENCES estudios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Pessoas do lado do cliente. NENHUMA tem senha (D8): entram por link pessoal
-- que o estúdio manda e que o estúdio revoga. É o que faz o cliente adotar, e o
-- link ser de uma pessoa só é o que mantém o comentário assinado.
CREATE TABLE revisores (
  id           BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  estudio_id   BIGINT UNSIGNED NOT NULL,
  cliente_id   BIGINT UNSIGNED NOT NULL,
  nome         VARCHAR(120) NOT NULL,
  funcao       VARCHAR(120) NULL,               -- "Aprovação geral", "Conceituação"
  email        VARCHAR(190) NOT NULL,           -- é a identidade: para onde o link vai
  ativo        TINYINT(1)   NOT NULL DEFAULT 1,
  criado_em    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_revisor_email (cliente_id, email),
  KEY ix_revisores_cliente (cliente_id),
  KEY ix_revisores_email (email),
  CONSTRAINT fk_revisores_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- --------------------------------------------------------- sessões e tokens
-- Sessão em tabela, não só em cookie: é o que permite derrubá-la. Trocar a
-- senha encerra as outras; remover alguém da equipe corta o acesso na hora em
-- vez de esperar o cookie vencer.

CREATE TABLE sessoes (
  id           CHAR(64) PRIMARY KEY,            -- SHA-256 do id de sessão, nunca o id
  estudio_id   BIGINT UNSIGNED NOT NULL,
  -- um dos dois. O revisor também tem sessão: o link do e-mail é de uso único e
  -- abre uma sessão de 30 dias, senão ele pediria link a cada visita.
  usuario_id   BIGINT UNSIGNED NULL,            -- lado do estúdio
  revisor_id   BIGINT UNSIGNED NULL,            -- lado do cliente
  ip           VARBINARY(16) NULL,
  agente       VARCHAR(255) NULL,               -- para a pessoa reconhecer o aparelho
  criada_em    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  vista_em     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expira_em    DATETIME NOT NULL,
  KEY ix_sessoes_usuario (usuario_id),
  KEY ix_sessoes_revisor (revisor_id),
  KEY ix_sessoes_expira (expira_em),
  CONSTRAINT fk_sessoes_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id)  ON DELETE CASCADE,
  CONSTRAINT fk_sessoes_revisor FOREIGN KEY (revisor_id) REFERENCES revisores(id) ON DELETE CASCADE,
  CONSTRAINT ck_sessao_dono CHECK (usuario_id IS NOT NULL OR revisor_id IS NOT NULL)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Um token para tudo: confirmar e-mail, convidar, redefinir senha.
-- O link do cliente NÃO mora aqui — ele é por rodada e vive em rodadas.token_cliente.
--
-- Guarda-se o HASH, nunca o token. Quem vazar esta tabela não consegue usar
-- link nenhum. SHA-256 basta: o segredo tem 32 bytes de random_bytes(), então
-- não há dicionário a resistir e bcrypt seria só custo.
CREATE TABLE tokens (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  estudio_id  BIGINT UNSIGNED NULL,             -- NULL no cadastro, que precede o estúdio
  usuario_id  BIGINT UNSIGNED NULL,
  revisor_id  BIGINT UNSIGNED NULL,            -- nos tipos do lado do cliente
  tipo        ENUM('verificacao','convite','reset') NOT NULL,
  token_hash  CHAR(64) NOT NULL,                -- SHA-256 do token
  email       VARCHAR(190) NOT NULL,            -- destino, para convite a quem ainda não existe
  papel       ENUM('admin','equipe') NULL,      -- só em convite
  criado_por  BIGINT UNSIGNED NULL,
  ip_pedido   VARBINARY(16) NULL,
  expira_em   DATETIME NOT NULL,
  usado_em    DATETIME NULL,                    -- uso único: preenchido invalida
  criado_em   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_token_hash (token_hash),
  KEY ix_tokens_email (email, tipo),
  KEY ix_tokens_expira (expira_em),
  CONSTRAINT fk_tokens_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id)  ON DELETE CASCADE,
  CONSTRAINT fk_tokens_revisor FOREIGN KEY (revisor_id) REFERENCES revisores(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Limite de tentativa em dois eixos. Por conta cobre adivinhar a senha de
-- alguém; por IP cobre varrer muitas contas de uma origem só. Nenhum dos dois
-- sozinho basta, e o bloqueio por conta precisa expirar — senão quem souber o
-- e-mail derruba o acesso da pessoa quando quiser.
CREATE TABLE tentativas_login (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  email       VARCHAR(190) NULL,
  ip          VARBINARY(16) NOT NULL,
  sucesso     TINYINT(1) NOT NULL DEFAULT 0,
  criado_em   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_tent_email (email, criado_em),
  KEY ix_tent_ip (ip, criado_em)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Registro de envio. Sem isto, "o cliente diz que não recebeu" não tem resposta.
CREATE TABLE emails_enviados (
  id           BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  estudio_id   BIGINT UNSIGNED NULL,
  tipo         VARCHAR(40) NOT NULL,            -- convite-revisor, reset, lembrete...
  destinatario VARCHAR(190) NOT NULL,
  rodada_id    BIGINT UNSIGNED NULL,
  provedor_id  VARCHAR(120) NULL,               -- id da mensagem no Brevo/Resend
  estado       ENUM('enfileirado','enviado','falhou','devolvido') NOT NULL DEFAULT 'enfileirado',
  erro         VARCHAR(255) NULL,
  criado_em    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_em_estudio (estudio_id, criado_em),
  KEY ix_em_dest (destinatario, criado_em)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------------ projetos

CREATE TABLE projetos (
  id           BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  estudio_id   BIGINT UNSIGNED NOT NULL,
  nome         VARCHAR(160) NOT NULL,
  cliente_id   BIGINT UNSIGNED NOT NULL,   -- era cliente_nome, um texto solto
  arquivado    TINYINT(1)   NOT NULL DEFAULT 0,
  criado_por   BIGINT UNSIGNED NULL,
  criado_em    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_projetos_estudio (estudio_id, arquivado),
  CONSTRAINT fk_projetos_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE RESTRICT,
  CONSTRAINT fk_projetos_estudio FOREIGN KEY (estudio_id) REFERENCES estudios(id) ON DELETE CASCADE,
  CONSTRAINT fk_projetos_autor   FOREIGN KEY (criado_por) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Quem revisa o quê. A pessoa pertence ao cliente; a participação é por projeto.
-- `obrigatorio` saiu de revisores e veio para cá: a mesma pessoa pode ser
-- obrigatória num projeto e opcional noutro.
CREATE TABLE revisor_projeto (
  id           BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  estudio_id   BIGINT UNSIGNED NOT NULL,
  revisor_id   BIGINT UNSIGNED NOT NULL,
  projeto_id   BIGINT UNSIGNED NOT NULL,
  obrigatorio  TINYINT(1) NOT NULL DEFAULT 0,   -- trava o fechamento da janela
  -- O LINK. Um por pessoa e por projeto: é o que permite tirar o acesso de
  -- alguém a um projeto sem mexer no dela nos outros, e é o que a tela
  -- "quem tem qual link" do estúdio lista.
  token_hash   CHAR(64) NULL,                   -- SHA-256; o token só existe no e-mail
  token_criado_em DATETIME NULL,
  revogado_em  DATETIME NULL,                   -- preenchido, o link morre
  convidado_em DATETIME NULL,
  abriu_em     DATETIME NULL,                   -- primeira vez que usou
  ultimo_acesso DATETIME NULL,
  criado_em    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_revisor_projeto (revisor_id, projeto_id),
  UNIQUE KEY uq_rp_token (token_hash),
  KEY ix_rp_projeto (projeto_id),
  CONSTRAINT fk_rp_revisor FOREIGN KEY (revisor_id) REFERENCES revisores(id) ON DELETE CASCADE,
  CONSTRAINT fk_rp_projeto FOREIGN KEY (projeto_id) REFERENCES projetos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Um "item" é o ESPAÇO ou a PEÇA, estável entre versões: "Hall — recepção",
-- "Fachada — animação de aproximação". O arquivo de cada versão mora em versoes.
--
-- Era `imagens` na v1. Renomeado porque a tabela passou a guardar vídeo também,
-- e tabela com nome que mente sobre o conteúdo envelhece mal.
CREATE TABLE itens (
  id         BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  estudio_id BIGINT UNSIGNED NOT NULL,
  projeto_id BIGINT UNSIGNED NOT NULL,
  tipo       ENUM('imagem','video') NOT NULL DEFAULT 'imagem',
  titulo     VARCHAR(160) NOT NULL,
  ambiente   VARCHAR(80)  NULL,          -- agrupador livre: "Hall", "Piscina"
  ordem      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  -- referência de briefing do espaço inteiro. Referência que pertence a um
  -- pedido específico vai em `anexos`, não aqui.
  ref_path   VARCHAR(255) NULL,
  ref_titulo VARCHAR(160) NULL,
  removido   TINYINT(1)   NOT NULL DEFAULT 0,
  criado_em  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_itens_projeto (projeto_id, ordem),
  KEY ix_itens_estudio (estudio_id),
  CONSTRAINT fk_itens_projeto FOREIGN KEY (projeto_id) REFERENCES projetos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------------------------------- rodadas

CREATE TABLE rodadas (
  id            BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  estudio_id    BIGINT UNSIGNED NOT NULL,
  projeto_id    BIGINT UNSIGNED NOT NULL,
  numero        SMALLINT UNSIGNED NOT NULL,          -- 1, 2, 3...
  fase          ENUM('rascunho','cliente','fechada','equipe','concluida')
                NOT NULL DEFAULT 'rascunho',
  -- Link ABERTO, desligado por padrão (M19): endereço único que qualquer um que
  -- o receba abre e comenta como convidado, sem e-mail. Existe porque D8 continua
  -- valendo para o cliente que se recusa a abrir e-mail — mas o caminho normal
  -- é o link pessoal de cada revisor, que identifica o autor e é revogável um a um.
  link_aberto   TINYINT(1) NOT NULL DEFAULT 0,
  token_cliente CHAR(32)  NULL,
  recado        TEXT      NULL,                      -- mensagem do estúdio ao cliente
  prazo_em      DATETIME  NULL,                      -- NULL = sem prazo
  aberta_em     DATETIME  NULL,
  fechada_em    DATETIME  NULL,
  liberada_em   DATETIME  NULL,                      -- liberada para a equipe
  concluida_em  DATETIME  NULL,
  criado_em     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_rodadas_projeto_numero (projeto_id, numero),
  UNIQUE KEY uq_rodadas_token (token_cliente),
  KEY ix_rodadas_fase (estudio_id, fase),
  CONSTRAINT fk_rodadas_projeto FOREIGN KEY (projeto_id) REFERENCES projetos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Arquivo de um item numa rodada. Um item pode não ter versão nova numa
-- rodada — nesse caso o cliente vê a última versão existente.
CREATE TABLE versoes (
  id            BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  estudio_id    BIGINT UNSIGNED NOT NULL,
  item_id       BIGINT UNSIGNED NOT NULL,
  rodada_id     BIGINT UNSIGNED NOT NULL,
  numero        SMALLINT UNSIGNED NOT NULL,          -- v1, v2...
  arquivo_nome  VARCHAR(255) NOT NULL,               -- nome original enviado
  original_path VARCHAR(255) NULL,                   -- NULL se o original foi arquivado
  original_bytes BIGINT UNSIGNED NULL,
  largura       INT UNSIGNED NULL,
  altura        INT UNSIGNED NULL,

  -- imagem: pirâmide de tiles WebP (D6)
  piramide_path VARCHAR(255) NULL,                   -- diretório ou pacote de tiles

  -- vídeo (M2): o arquivo de entrega não é servível. `preview_path` é a cópia
  -- de revisão em H.264, gerada fora do servidor web pelo mesmo motivo de D5.
  duracao_s     DECIMAL(8,2) NULL,
  preview_path  VARCHAR(255) NULL,
  poster_path   VARCHAR(255) NULL,

  estado        ENUM('recebida','processando','pronta','erro') NOT NULL DEFAULT 'recebida',
  erro_msg      VARCHAR(255) NULL,
  avisos        JSON NULL,                           -- ex.: ["transparencia_real"]
  criado_em     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_versoes_item_numero (item_id, numero),
  KEY ix_versoes_rodada (rodada_id),
  KEY ix_versoes_estado (estudio_id, estado),
  CONSTRAINT fk_versoes_item   FOREIGN KEY (item_id)   REFERENCES itens(id)   ON DELETE CASCADE,
  CONSTRAINT fk_versoes_rodada FOREIGN KEY (rodada_id) REFERENCES rodadas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE revisor_rodada (
  id           BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  estudio_id   BIGINT UNSIGNED NOT NULL,
  revisor_id   BIGINT UNSIGNED NOT NULL,
  rodada_id    BIGINT UNSIGNED NOT NULL,
  convidado_em DATETIME NULL,
  abriu_em     DATETIME NULL,
  concluiu_em  DATETIME NULL,                    -- marcou "terminei de comentar"
  UNIQUE KEY uq_revisor_rodada (revisor_id, rodada_id),
  KEY ix_rr_rodada (rodada_id),
  CONSTRAINT fk_rr_revisor FOREIGN KEY (revisor_id) REFERENCES revisores(id) ON DELETE CASCADE,
  CONSTRAINT fk_rr_rodada  FOREIGN KEY (rodada_id)  REFERENCES rodadas(id)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------- aprovações
-- M1. Aprovar é ato de uma pessoa sobre uma versão, e é o registro que responde
-- "quem liberou isso e quando".
--
-- A situação exibida na grade é DERIVADA, não guardada:
--
--   ajustes   se existe comentário do cliente naquela versão
--   aprovada  se não existe comentário do cliente e há ao menos uma aprovação
--   pendente  nos demais casos
--
-- Comentário do cliente ganha de aprovação de propósito: se um revisor aprovou
-- e outro pediu ajuste, quem manda é o pedido. E a regra olha a existência do
-- comentário, não o status dele — resolver o pedido na fase de execução não
-- transforma a versão em aprovada; quem aprova a correção é a versão seguinte.

CREATE TABLE aprovacoes (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  estudio_id  BIGINT UNSIGNED NOT NULL,
  versao_id   BIGINT UNSIGNED NOT NULL,
  rodada_id   BIGINT UNSIGNED NOT NULL,
  revisor_id  BIGINT UNSIGNED NOT NULL,
  criado_em   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_aprovacao (versao_id, revisor_id),
  KEY ix_aprov_rodada (rodada_id),
  CONSTRAINT fk_aprov_versao  FOREIGN KEY (versao_id)  REFERENCES versoes(id)   ON DELETE CASCADE,
  CONSTRAINT fk_aprov_rodada  FOREIGN KEY (rodada_id)  REFERENCES rodadas(id)   ON DELETE CASCADE,
  CONSTRAINT fk_aprov_revisor FOREIGN KEY (revisor_id) REFERENCES revisores(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------------- comentários

CREATE TABLE comentarios (
  id            BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  estudio_id    BIGINT UNSIGNED NOT NULL,
  versao_id     BIGINT UNSIGNED NOT NULL,
  rodada_id     BIGINT UNSIGNED NOT NULL,
  -- autor: um dos dois é NULL
  revisor_id    BIGINT UNSIGNED NULL,            -- lado do cliente
  usuario_id    BIGINT UNSIGNED NULL,            -- lado do estúdio
  texto         TEXT NOT NULL,

  -- Marcação na imagem, em porcentagem (0–100) para sobreviver a mudança de
  -- resolução entre versões e não depender do zoom em que foi criada.
  --   pino  usa (pin_x, pin_y)
  --   area  usa os dois pares como cantos opostos
  --   seta  usa o primeiro par como origem e o segundo como ponta
  --   NULL  comentário geral, sem ponto
  marca_tipo    ENUM('pino','area','seta') NULL,
  pin_x         DECIMAL(5,2) NULL,
  pin_y         DECIMAL(5,2) NULL,
  pin_x2        DECIMAL(5,2) NULL,
  pin_y2        DECIMAL(5,2) NULL,

  -- M2. Instante do vídeo. NULL em item de imagem.
  tempo_s       DECIMAL(8,2) NULL,

  origem        ENUM('plataforma','importado') NOT NULL DEFAULT 'plataforma',
  origem_nota   VARCHAR(80) NULL,                -- "WhatsApp, 06/08", "e-mail"

  -- M15. Tarefa interna é comentário com autor do lado do estúdio e este marcador.
  -- O cliente NUNCA a recebe: o filtro tem que estar na camada de acesso a dados,
  -- não repetido em cada rota.
  interno       TINYINT(1) NOT NULL DEFAULT 0,

  -- M16. Cada um edita o que escreveu. O estúdio não edita comentário do cliente
  -- em hipótese alguma — registro que a outra parte reescreve não é prova.
  editado_em    DATETIME NULL,
  status        ENUM('pendente','execucao','resolvido','nao_aplica') NOT NULL DEFAULT 'pendente',
  responsavel_id BIGINT UNSIGNED NULL,
  -- migração entre versões
  herdado_de_id BIGINT UNSIGNED NULL,
  criado_em     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  atualizado_em TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY ix_com_versao (versao_id),
  KEY ix_com_rodada_status (rodada_id, status),
  KEY ix_com_estudio (estudio_id),
  CONSTRAINT fk_com_versao   FOREIGN KEY (versao_id)  REFERENCES versoes(id)  ON DELETE CASCADE,
  CONSTRAINT fk_com_rodada   FOREIGN KEY (rodada_id)  REFERENCES rodadas(id)  ON DELETE CASCADE,
  -- RESTRICT, não SET NULL: quem escreveu não se apaga, se desativa
  -- (usuarios.ativo, revisores.ativo). Comentário sem autor não é auditoria, e
  -- o MySQL 8 ainda proíbe SET NULL numa coluna usada em CHECK.
  CONSTRAINT fk_com_revisor  FOREIGN KEY (revisor_id) REFERENCES revisores(id) ON DELETE RESTRICT,
  CONSTRAINT fk_com_usuario  FOREIGN KEY (usuario_id) REFERENCES usuarios(id)  ON DELETE RESTRICT,
  CONSTRAINT fk_com_resp     FOREIGN KEY (responsavel_id) REFERENCES usuarios(id) ON DELETE SET NULL,
  CONSTRAINT fk_com_herdado  FOREIGN KEY (herdado_de_id)  REFERENCES comentarios(id) ON DELETE SET NULL,
  CONSTRAINT ck_com_autor CHECK (revisor_id IS NOT NULL OR usuario_id IS NOT NULL),
  -- área e seta precisam do segundo par; pino não pode ter
  CONSTRAINT ck_com_marca CHECK (
    (marca_tipo IS NULL  AND pin_x IS NULL AND pin_x2 IS NULL) OR
    (marca_tipo = 'pino' AND pin_x IS NOT NULL AND pin_y IS NOT NULL AND pin_x2 IS NULL) OR
    (marca_tipo IN ('area','seta') AND pin_x IS NOT NULL AND pin_y IS NOT NULL
                                   AND pin_x2 IS NOT NULL AND pin_y2 IS NOT NULL))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- M4. A conversa dentro do pedido. Sem isso, "qual bege?" acontece no WhatsApp
-- e o registro se rompe exatamente onde ele vale.
--
-- Resposta do estúdio só é visível para o cliente quando a rodada sai da fase
-- `cliente` — mesma guarda de fase, na direção contrária.
CREATE TABLE comentario_respostas (
  id            BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  estudio_id    BIGINT UNSIGNED NOT NULL,
  comentario_id BIGINT UNSIGNED NOT NULL,
  revisor_id    BIGINT UNSIGNED NULL,
  usuario_id    BIGINT UNSIGNED NULL,
  texto         TEXT NOT NULL,
  criado_em     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_resp_comentario (comentario_id, criado_em),
  KEY ix_resp_estudio (estudio_id),
  CONSTRAINT fk_resp_com     FOREIGN KEY (comentario_id) REFERENCES comentarios(id) ON DELETE CASCADE,
  CONSTRAINT fk_resp_revisor FOREIGN KEY (revisor_id) REFERENCES revisores(id) ON DELETE RESTRICT,
  CONSTRAINT fk_resp_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id)  ON DELETE RESTRICT,
  CONSTRAINT ck_resp_autor CHECK (revisor_id IS NOT NULL OR usuario_id IS NOT NULL)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- M5. Referência que pertence a um pedido específico — a foto da cadeira, o
-- print do catálogo. Diferente de itens.ref_path, que é o briefing do espaço.
CREATE TABLE anexos (
  id            BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  estudio_id    BIGINT UNSIGNED NOT NULL,
  comentario_id BIGINT UNSIGNED NOT NULL,
  arquivo_nome  VARCHAR(255) NOT NULL,
  caminho       VARCHAR(255) NOT NULL,
  mime          VARCHAR(80)  NOT NULL,
  bytes         BIGINT UNSIGNED NOT NULL,
  largura       INT UNSIGNED NULL,
  altura        INT UNSIGNED NULL,
  criado_em     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_anexo_comentario (comentario_id),
  KEY ix_anexo_estudio (estudio_id),
  CONSTRAINT fk_anexo_com FOREIGN KEY (comentario_id) REFERENCES comentarios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------- etapas de produção
-- M13. O pipeline do estúdio. `etapas_modelo` é o padrão da casa; ele é copiado
-- para `etapas` quando o projeto nasce, porque projeto que já começou não pode
-- mudar de forma quando alguém edita o modelo.

CREATE TABLE etapas_modelo (
  id         BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  estudio_id BIGINT UNSIGNED NOT NULL,
  nome       VARCHAR(80)  NOT NULL,        -- nome interno: "Iluminação"
  ordem      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  -- publica = o cliente vê esta etapa no acompanhamento (M14).
  -- rotulo_publico permite chamar a etapa de outro jeito lá fora, e permite que
  -- várias etapas internas caiam no mesmo rótulo quando o estúdio quiser dar
  -- andamento sem expor o método.
  publica         TINYINT(1)  NOT NULL DEFAULT 1,
  rotulo_publico  VARCHAR(80) NULL,
  -- pipeline de animação não é o de render estático. Sem isto, toda linha de
  -- vídeo no quadro exigia marcar "não se aplica" à mão nas etapas de imagem.
  aplica     ENUM('ambos','imagem','video') NOT NULL DEFAULT 'ambos',
  criado_em  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_etmod_estudio (estudio_id, ordem),
  CONSTRAINT fk_etmod_estudio FOREIGN KEY (estudio_id) REFERENCES estudios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE etapas (
  id         BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  estudio_id BIGINT UNSIGNED NOT NULL,
  projeto_id BIGINT UNSIGNED NOT NULL,
  nome       VARCHAR(80)  NOT NULL,
  ordem      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  publica         TINYINT(1)  NOT NULL DEFAULT 1,
  rotulo_publico  VARCHAR(80) NULL,
  -- pipeline de animação não é o de render estático. Sem isto, toda linha de
  -- vídeo no quadro exigia marcar "não se aplica" à mão nas etapas de imagem.
  aplica     ENUM('ambos','imagem','video') NOT NULL DEFAULT 'ambos',
  criado_em  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_etapas_projeto (projeto_id, ordem),
  CONSTRAINT fk_etapas_projeto FOREIGN KEY (projeto_id) REFERENCES projetos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- O check do artista. Preso a (item, rodada) e não à versão: durante a execução
-- a versão nova ainda não existe — ela é o resultado desse trabalho.
CREATE TABLE item_etapa (
  id            BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  estudio_id    BIGINT UNSIGNED NOT NULL,
  item_id       BIGINT UNSIGNED NOT NULL,
  rodada_id     BIGINT UNSIGNED NOT NULL,
  etapa_id      BIGINT UNSIGNED NOT NULL,
  estado        ENUM('pendente','fazendo','feita','nao_aplica') NOT NULL DEFAULT 'pendente',
  responsavel_id BIGINT UNSIGNED NULL,
  concluida_em  DATETIME NULL,
  atualizado_em TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_item_etapa (item_id, rodada_id, etapa_id),
  KEY ix_ie_rodada (rodada_id, estado),
  KEY ix_ie_estudio (estudio_id),
  CONSTRAINT fk_ie_item   FOREIGN KEY (item_id)  REFERENCES itens(id)    ON DELETE CASCADE,
  CONSTRAINT fk_ie_rodada FOREIGN KEY (rodada_id) REFERENCES rodadas(id) ON DELETE CASCADE,
  CONSTRAINT fk_ie_etapa  FOREIGN KEY (etapa_id)  REFERENCES etapas(id)  ON DELETE CASCADE,
  CONSTRAINT fk_ie_resp   FOREIGN KEY (responsavel_id) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Ligar um pedido do cliente à etapa que vai resolvê-lo. Opcional, e é o que
-- permite ao artista puxar "toda a iluminação de todas as imagens" de uma vez,
-- que é como o trabalho realmente acontece.
ALTER TABLE comentarios
  ADD COLUMN etapa_id BIGINT UNSIGNED NULL AFTER responsavel_id,
  ADD CONSTRAINT fk_com_etapa FOREIGN KEY (etapa_id) REFERENCES etapas(id) ON DELETE SET NULL;

-- M14. O interruptor do acompanhamento, e a data que o estúdio promete.
ALTER TABLE projetos
  ADD COLUMN mostrar_andamento TINYINT(1) NOT NULL DEFAULT 1 AFTER cliente_id;
ALTER TABLE rodadas
  ADD COLUMN previsao_entrega DATE NULL AFTER prazo_em;

-- Editar as etapas de um projeto em andamento tem consequência visível: etapa
-- nova entra como 'pendente' em todo item e derruba o percentual que o cliente
-- vê; etapa removida leva junto o que já estava marcado. A interface avisa os
-- dois casos com o número antes e depois, e o DELETE em item_etapa é o que
-- executa a segunda parte.

-- Progresso é DERIVADO, nunca guardado:
--   por item   = etapas 'feita' ÷ etapas que valem para o tipo dele
--                e que não estão em 'nao_aplica'
--   por rodada = média do progresso dos itens em produção
--   em produção = itens com comentário do cliente na versão corrente; item
--                 aprovado não entra na conta, já está fechado
--
-- O que o cliente lê é a etapa PÚBLICA mais avançada que saiu de 'pendente'.
-- Com tudo feito, lê "pronta para a próxima entrega".

-- ------------------------------------------------------------- cronograma
-- M17. Uma faixa por etapa do pipeline, por rodada. Plano de coordenação, não
-- estimativa por tarefa: a etapa vale para a rodada inteira e não existe barra
-- por item. Sem duração calculada e sem dependência entre etapas, de propósito.
--
-- A data de entrega da rodada é DERIVADA: o maior `fim` entre as etapas. Não há
-- campo separado para ela — `rodadas.previsao_entrega` existe apenas como cópia
-- materializada para consulta rápida, e é escrita pelo mesmo código que salva a
-- faixa. Duas fontes para a mesma data divergem na terceira semana.

CREATE TABLE etapa_cronograma (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  estudio_id  BIGINT UNSIGNED NOT NULL,
  rodada_id   BIGINT UNSIGNED NOT NULL,
  etapa_id    BIGINT UNSIGNED NOT NULL,
  inicio      DATE NOT NULL,
  fim         DATE NOT NULL,
  criado_em   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  atualizado_em TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_etapa_rodada (rodada_id, etapa_id),
  KEY ix_ec_estudio (estudio_id),
  CONSTRAINT fk_ec_rodada FOREIGN KEY (rodada_id) REFERENCES rodadas(id) ON DELETE CASCADE,
  CONSTRAINT fk_ec_etapa  FOREIGN KEY (etapa_id)  REFERENCES etapas(id)  ON DELETE CASCADE,
  CONSTRAINT ck_ec_ordem CHECK (fim >= inicio)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- O que o cliente recebe do cronograma, quando projetos.mostrar_andamento = 1:
--   · a janela dele            rodadas.aberta_em → rodadas.prazo_em
--   · as etapas PÚBLICAS       etapas.publica = 1, com etapas.rotulo_publico
--   · a entrega                maior fim entre as etapas
--   · a próxima janela dele    projetada a partir da entrega, com a mesma
--                              duração em dias da janela atual
-- Etapa interna nunca sai na resposta do token do cliente, embora continue
-- contando no percentual.
--
-- Alertas, ambos derivados — nada é guardado:
--   atrasada  fim < hoje e ainda há item_etapa aberto naquela etapa
--   cedo      inicio <= rodadas.prazo_em, ou seja, produção planejada para
--             começar antes de o cliente terminar de comentar (contraria a
--             regra 1 do produto, em docs/01-produto.md)

-- ------------------------------------------------------------------ auditoria
-- Quem abriu e fechou o quê, e quando. É a prova em discussão de prazo, e é a
-- fonte do relatório da rodada (M10). Só grava, nunca altera.

CREATE TABLE eventos (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  estudio_id  BIGINT UNSIGNED NOT NULL,
  projeto_id  BIGINT UNSIGNED NULL,
  rodada_id   BIGINT UNSIGNED NULL,
  tipo        VARCHAR(50) NOT NULL,   -- rodada.aberta, versao.publicada, item.aprovado...
  usuario_id  BIGINT UNSIGNED NULL,
  revisor_id  BIGINT UNSIGNED NULL,
  detalhe     JSON NULL,
  ip          VARBINARY(16) NULL,
  criado_em   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_ev_rodada (rodada_id, criado_em),
  KEY ix_ev_estudio (estudio_id, criado_em)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------- cobrança
-- Coleta os indicadores dos três modelos possíveis. A régua de cobrança só
-- entra depois de P1 decidido — ver docs/07-pendencias.md e docs/09-melhorias.md.

CREATE TABLE uso_mensal (
  id                BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  estudio_id        BIGINT UNSIGNED NOT NULL,
  competencia       CHAR(7) NOT NULL,            -- 'YYYY-MM'
  projetos_ativos   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  usuarios_ativos   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  armazenamento_mb  BIGINT UNSIGNED NOT NULL DEFAULT 0,
  video_mb          BIGINT UNSIGNED NOT NULL DEFAULT 0,   -- separado: cresce diferente
  criado_em         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_uso (estudio_id, competencia),
  CONSTRAINT fk_uso_estudio FOREIGN KEY (estudio_id) REFERENCES estudios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------- migração de versão
-- Ao criar a v(n), todo comentário aberto do cliente é copiado. Resolvido e
-- não se aplica ficam para trás. A cadeia herdado_de_id conta a história de um
-- pedido que atravessou várias rodadas.
--
-- INSERT INTO comentarios
--   (estudio_id, versao_id, rodada_id, revisor_id, usuario_id, texto,
--    marca_tipo, pin_x, pin_y, pin_x2, pin_y2, tempo_s,
--    origem, origem_nota, status, herdado_de_id)
-- SELECT
--   c.estudio_id, :nova_versao_id, :nova_rodada_id, c.revisor_id, c.usuario_id, c.texto,
--   c.marca_tipo, c.pin_x, c.pin_y, c.pin_x2, c.pin_y2, c.tempo_s,
--   c.origem, c.origem_nota, 'pendente', c.id
-- FROM comentarios c
-- WHERE c.versao_id = :versao_anterior_id
--   AND c.estudio_id = :estudio_id
--   AND c.status IN ('pendente','execucao');
--
-- Anexos não são copiados: a linha nova aponta para a antiga por herdado_de_id,
-- e o anexo é lido pela cadeia. Copiar arquivo a cada versão multiplica disco à toa.