-- =============================================================================
-- 009_planos_trabalho.sql — Módulo Planos de Trabalho (Assessoria de Eventos)
--
-- Núcleo: pt_orgaos → pt_planos → pt_diligencias → pt_tarefas → pt_documentos,
-- com pt_clientes, pt_eventos, pt_fontes_recurso e pt_formatos_orgao.
--
-- AUTOSSUFICIENTE: só precisa da extensão pgcrypto. NÃO depende do 001_schema.sql.
-- Prefixo pt_ evita colisão com `eventos`/`fontes` da plataforma (quando existirem).
--
-- Pontes de integração — ambito_id, evento_plataforma_id, fonte_plataforma_id —
-- são uuid SIMPLES (SEM foreign key). Ficam PRONTAS para ligar à plataforma
-- depois (um 012 futuro adiciona as FKs quando você decidir conectar).
--
-- Espelha 1:1 as entidades do protótipo planos-trabalho.html.
-- Alvo: Supabase (Postgres 14+). Idempotente.
-- =============================================================================

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Helper de atualizado_em (próprio, para rodar sem o 001). CREATE OR REPLACE
-- convive com o do 001 caso ambos existam.
CREATE OR REPLACE FUNCTION gr_touch_atualizado_em()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.atualizado_em := now();
  RETURN NEW;
END $$;

-- =============================================================================
-- ÓRGÃOS concedentes — globais (compartilhados entre clientes).
-- =============================================================================
CREATE TABLE IF NOT EXISTS pt_orgaos (
  id                     uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  nome                   text NOT NULL,
  sigla                  text,
  nome_completo          text,
  cnpj                   text,
  email_convenios        text,
  email_prestacao_contas text,
  endereco               text,
  telefone               text,
  responsavel            text,
  unidade_gerencia       text,
  observacoes            text,
  criado_em              timestamptz NOT NULL DEFAULT now(),
  atualizado_em          timestamptz NOT NULL DEFAULT now()
);
COMMENT ON TABLE pt_orgaos IS 'Órgãos concedentes (SERINT, SECULT, ...). Reconfiguram o formato exigido por plano.';

-- =============================================================================
-- CLIENTES / proponentes. ambito_id = ponte multicliente (uuid simples, sem FK).
-- =============================================================================
CREATE TABLE IF NOT EXISTS pt_clientes (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id     uuid,                          -- PONTE multicliente (sem FK até conectar)
  nome          text NOT NULL,
  cnpj          text,
  responsavel   text,
  contato       text,
  observacoes   text,
  criado_em     timestamptz NOT NULL DEFAULT now(),
  atualizado_em timestamptz NOT NULL DEFAULT now()
);
COMMENT ON TABLE pt_clientes IS 'Proponentes atendidos pela assessoria (ex.: Prefeitura de Águas Lindas de Goiás).';

-- =============================================================================
-- EVENTOS do módulo. evento_plataforma_id = ponte ao eventos da plataforma.
-- =============================================================================
CREATE TABLE IF NOT EXISTS pt_eventos (
  id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id            uuid,
  cliente_id           uuid REFERENCES pt_clientes(id) ON DELETE SET NULL,
  evento_plataforma_id uuid,                   -- PONTE → eventos(id) (sem FK até conectar)
  nome                 text NOT NULL,
  data_inicio          date,
  data_fim             date,
  local                text,
  status               text,
  observacoes          text,
  criado_em            timestamptz NOT NULL DEFAULT now(),
  atualizado_em        timestamptz NOT NULL DEFAULT now()
);
COMMENT ON COLUMN pt_eventos.evento_plataforma_id IS 'Ponte para eventos(id) da plataforma. uuid simples: integração pronta, não conectada.';

-- =============================================================================
-- FONTES DE RECURSO do evento. fonte_plataforma_id = ponte ao cofre/fonte.
-- =============================================================================
CREATE TABLE IF NOT EXISTS pt_fontes_recurso (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id           uuid,
  evento_id           uuid REFERENCES pt_eventos(id) ON DELETE CASCADE,
  fonte_plataforma_id uuid,                    -- PONTE → fontes(id) (sem FK até conectar)
  tipo                text,   -- emenda parlamentar | convênio | termo de fomento | recurso próprio | outro
  origem              text,
  programa            text,
  valor_repasse       numeric(14,2),
  contrapartida       numeric(14,2),
  criado_em           timestamptz NOT NULL DEFAULT now()
);
COMMENT ON COLUMN pt_fontes_recurso.fonte_plataforma_id IS 'Ponte para fontes(id) (cofre). uuid simples: integração pronta, não conectada.';

-- =============================================================================
-- FORMATOS POR ÓRGÃO (Fase 2): campos do formulário + checklist-base + modelo.
-- =============================================================================
CREATE TABLE IF NOT EXISTS pt_formatos_orgao (
  id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  orgao_id             uuid REFERENCES pt_orgaos(id) ON DELETE CASCADE,
  programa             text,
  nome                 text,
  campos_formulario    jsonb,
  documentos_exigidos  jsonb,
  modelo_documento_url text,
  criado_em            timestamptz NOT NULL DEFAULT now()
);

-- =============================================================================
-- PLANOS DE TRABALHO (núcleo).
-- =============================================================================
CREATE TABLE IF NOT EXISTS pt_planos (
  id                 uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id          uuid,
  nome               text NOT NULL,
  processo_sei       text,
  orgao_id           uuid REFERENCES pt_orgaos(id),
  cliente_id         uuid REFERENCES pt_clientes(id),
  evento_id          uuid REFERENCES pt_eventos(id),
  fonte_recurso_id   uuid REFERENCES pt_fontes_recurso(id),
  formato_id         uuid REFERENCES pt_formatos_orgao(id),
  modelo_id          uuid REFERENCES pt_modelos(id),  -- catálogo de itens-mestre que este plano instancia
  programa           text,
  status             text NOT NULL DEFAULT 'em elaboração'
                     CHECK (status IN ('em elaboração','em diligência','aguardando análise',
                            'aprovado','em execução','prestação de contas','concluído','arquivado')),
  valor_repasse      numeric(14,2),
  contrapartida      numeric(14,2),
  prazo_critico      date,
  responsavel        text,
  pasta_drive_url    text,
  doc_vivo_url       text,
  campos_preenchidos jsonb,
  observacoes        text,
  criado_em          timestamptz NOT NULL DEFAULT now(),
  atualizado_em      timestamptz NOT NULL DEFAULT now()
);
COMMENT ON TABLE pt_planos IS 'Plano de trabalho: formaliza e presta contas de uma fonte de recurso, perante um órgão.';

-- =============================================================================
-- DILIGÊNCIAS (sucessão ao longo do plano).
-- =============================================================================
CREATE TABLE IF NOT EXISTS pt_diligencias (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id           uuid,                    -- denormalizado p/ RLS
  plano_id            uuid NOT NULL REFERENCES pt_planos(id) ON DELETE CASCADE,
  numero              text,
  status              text NOT NULL DEFAULT 'nova'
                      CHECK (status IN ('nova','em atendimento','pronta para envio','enviada','concluída')),
  data_recebida       date,
  prazo_resposta      date,
  analista            text,
  contato_analista    text,
  email_envio         text,
  link_orientacoes    text,
  link_email_rascunho text,
  data_envio          date,
  texto_referencia    text,
  criado_em           timestamptz NOT NULL DEFAULT now(),
  atualizado_em       timestamptz NOT NULL DEFAULT now()
);

-- =============================================================================
-- MODELOS (templates de plano por órgão + programa + tipo de proponente).
-- Define O CATÁLOGO PERMANENTE de itens-mestre que o órgão sempre exige.
-- Ex.: "Goiás do Crescimento — Prefeitura", "Goiás do Crescimento — Instituto".
-- =============================================================================
CREATE TABLE IF NOT EXISTS pt_modelos (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  orgao_id        uuid REFERENCES pt_orgaos(id) ON DELETE SET NULL,
  programa        text,
  tipo_proponente text,                  -- 'prefeitura' | 'instituto' | 'entidade' | ...
  nome            text NOT NULL,
  descricao       text,
  ativo           boolean NOT NULL DEFAULT true,
  criado_em       timestamptz NOT NULL DEFAULT now(),
  atualizado_em   timestamptz NOT NULL DEFAULT now()
);

-- Itens-mestre DO MODELO: o catálogo template (hierárquico).
-- Aqui mora o que o órgão SEMPRE exige; cada item pode ter modelo .docx ou URL
-- de serviço (cartão CNPJ, CAUC etc.). 'condicional' marca itens opcionais
-- (ex.: Módulo Artistas, Declaração de Nomeação de Gestor, ramo SIM/NÃO).
CREATE TABLE IF NOT EXISTS pt_itens_modelo (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  modelo_id       uuid NOT NULL REFERENCES pt_modelos(id) ON DELETE CASCADE,
  parent_id       uuid REFERENCES pt_itens_modelo(id) ON DELETE CASCADE,
  numero          text,                  -- '1', '2.8', '4.10', '6'…
  titulo          text NOT NULL,
  categoria       text,                  -- conta bancária | declaração | certidão | plano de trabalho | documentação de artista | identificação | outro
  especificacao   text,
  requisito_chave text,
  modelo_url      text,                  -- link do modelo .docx ou serviço
  condicional     boolean NOT NULL DEFAULT false,
  ordem           int,
  criado_em       timestamptz NOT NULL DEFAULT now()
);

-- =============================================================================
-- ITENS DO PLANO (instâncias do modelo neste plano específico).
-- Criado pela função pt_instanciar_modelo(). Cada item pode ser desativado/
-- customizado sem afetar o modelo-base. instancia_de aponta para o item-modelo
-- de origem (permite saber "esse item nasceu de qual catálogo").
-- =============================================================================
CREATE TABLE IF NOT EXISTS pt_itens (
  id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id         uuid,
  plano_id          uuid NOT NULL REFERENCES pt_planos(id) ON DELETE CASCADE,
  parent_id         uuid REFERENCES pt_itens(id) ON DELETE CASCADE,
  instancia_de      uuid REFERENCES pt_itens_modelo(id) ON DELETE SET NULL,
  numero            text,
  titulo            text NOT NULL,
  categoria         text,
  especificacao     text,
  requisito_chave   text,
  modelo_url        text,
  condicional       boolean NOT NULL DEFAULT false,
  aplicavel         boolean NOT NULL DEFAULT true,   -- false = "não se aplica neste plano"
  referencia        text,
  link_documento    uuid,
  criado_em         timestamptz NOT NULL DEFAULT now(),
  atualizado_em     timestamptz NOT NULL DEFAULT now()
);

-- =============================================================================
-- TAREFAS (mapeamento da diligência em itens objetivos).
-- =============================================================================
CREATE TABLE IF NOT EXISTS pt_tarefas (
  id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id             uuid,                  -- denormalizado p/ RLS
  diligencia_id         uuid NOT NULL REFERENCES pt_diligencias(id) ON DELETE CASCADE,
  item_id               uuid REFERENCES pt_itens(id) ON DELETE SET NULL,  -- FK ao item-mestre do plano (vínculo principal)
  titulo                text NOT NULL,
  item_categoria        text,   -- derivado do item (mantido p/ compat); conta bancária | declaração | certidão | plano de trabalho | documentação de artista | envio | outro
  item_numero           text,   -- derivado do item (mantido p/ compat)
  referencia_diligencia text,
  status                text NOT NULL DEFAULT 'pendente'
                        CHECK (status IN ('pendente','em produção','pronto','não se aplica')),
  anexado_email         boolean NOT NULL DEFAULT false,
  responsavel           text,
  link_documento        text,
  observacoes           text,
  criado_em             timestamptz NOT NULL DEFAULT now(),
  atualizado_em         timestamptz NOT NULL DEFAULT now()
);
COMMENT ON COLUMN pt_tarefas.anexado_email IS 'Controle "anexado no e-mail": o envio só libera quando não falta nada a anexar.';

-- =============================================================================
-- DOCUMENTOS (versionamento + obsolescência automática).
-- substitui_id é a auto-relação de versão (Seção 8.3 da espec).
-- =============================================================================
CREATE TABLE IF NOT EXISTS pt_documentos (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id           uuid,                    -- denormalizado p/ RLS
  plano_id            uuid NOT NULL REFERENCES pt_planos(id) ON DELETE CASCADE,
  diligencia_envio_id uuid REFERENCES pt_diligencias(id),
  tarefa_id           uuid REFERENCES pt_tarefas(id) ON DELETE SET NULL,
  item_id             uuid REFERENCES pt_itens(id) ON DELETE SET NULL, -- FK ao item-mestre do plano (vínculo principal)
  requisito_chave     text,   -- derivado do item; mantido p/ compatibilidade e p/ a view de checklist
  nome                text NOT NULL,
  tipo                text[], -- array; pode ter vários quando o arquivo é um juntado (ex.: ARRAY['contrato','nota fiscal','proposta']). Valores: nota fiscal | contrato | proposta | declaração | extrato | plano de trabalho | certidão | comprovante | ofício | outro
  status              text NOT NULL DEFAULT 'rascunho'
                      CHECK (status IN ('rascunho','enviado','aceito/validado','substituído','glosado/rejeitado')),
  versao_final        boolean NOT NULL DEFAULT false,
  substitui_id        uuid REFERENCES pt_documentos(id),
  arquivo_url         text,
  observacoes         text,
  criado_em           timestamptz NOT NULL DEFAULT now()
);
COMMENT ON COLUMN pt_documentos.substitui_id IS 'Versão anterior que este documento substitui. Ao substituir, o anterior vira obsoleto.';

-- -----------------------------------------------------------------------------
-- Triggers de atualizado_em.
-- -----------------------------------------------------------------------------
DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY['pt_orgaos','pt_clientes','pt_eventos','pt_planos','pt_diligencias','pt_tarefas'] LOOP
    EXECUTE format('DROP TRIGGER IF EXISTS trg_touch_%1$s ON %1$s', t);
    EXECUTE format(
      'CREATE TRIGGER trg_touch_%1$s BEFORE UPDATE ON %1$s
         FOR EACH ROW EXECUTE FUNCTION gr_touch_atualizado_em()', t);
  END LOOP;
END $$;

-- -----------------------------------------------------------------------------
-- Índices (FKs e colunas de filtro frequente).
-- -----------------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS ix_pt_clientes_ambito      ON pt_clientes(ambito_id);
CREATE INDEX IF NOT EXISTS ix_pt_eventos_cliente      ON pt_eventos(cliente_id);
CREATE INDEX IF NOT EXISTS ix_pt_fontes_evento        ON pt_fontes_recurso(evento_id);
CREATE INDEX IF NOT EXISTS ix_pt_planos_orgao         ON pt_planos(orgao_id);
CREATE INDEX IF NOT EXISTS ix_pt_planos_cliente       ON pt_planos(cliente_id);
CREATE INDEX IF NOT EXISTS ix_pt_planos_evento        ON pt_planos(evento_id);
CREATE INDEX IF NOT EXISTS ix_pt_planos_ambito        ON pt_planos(ambito_id);
CREATE INDEX IF NOT EXISTS ix_pt_planos_status        ON pt_planos(status);
CREATE INDEX IF NOT EXISTS ix_pt_diligencias_plano    ON pt_diligencias(plano_id);
CREATE INDEX IF NOT EXISTS ix_pt_diligencias_status   ON pt_diligencias(status);
CREATE INDEX IF NOT EXISTS ix_pt_modelos_orgao        ON pt_modelos(orgao_id);
CREATE INDEX IF NOT EXISTS ix_pt_itens_modelo_modelo  ON pt_itens_modelo(modelo_id);
CREATE INDEX IF NOT EXISTS ix_pt_itens_modelo_parent  ON pt_itens_modelo(parent_id);
CREATE INDEX IF NOT EXISTS ix_pt_itens_plano          ON pt_itens(plano_id);
CREATE INDEX IF NOT EXISTS ix_pt_itens_parent         ON pt_itens(parent_id);
CREATE INDEX IF NOT EXISTS ix_pt_itens_instancia      ON pt_itens(instancia_de);
CREATE INDEX IF NOT EXISTS ix_pt_itens_req            ON pt_itens(plano_id, requisito_chave);
CREATE INDEX IF NOT EXISTS ix_pt_planos_modelo        ON pt_planos(modelo_id);
CREATE INDEX IF NOT EXISTS ix_pt_tarefas_diligencia   ON pt_tarefas(diligencia_id);
CREATE INDEX IF NOT EXISTS ix_pt_tarefas_item         ON pt_tarefas(item_id);
CREATE INDEX IF NOT EXISTS ix_pt_tarefas_status       ON pt_tarefas(status);
CREATE INDEX IF NOT EXISTS ix_pt_documentos_plano     ON pt_documentos(plano_id);
CREATE INDEX IF NOT EXISTS ix_pt_documentos_requisito ON pt_documentos(plano_id, requisito_chave);
CREATE INDEX IF NOT EXISTS ix_pt_documentos_substitui ON pt_documentos(substitui_id);
CREATE INDEX IF NOT EXISTS ix_pt_documentos_item      ON pt_documentos(item_id);
CREATE INDEX IF NOT EXISTS ix_pt_documentos_tipo_gin  ON pt_documentos USING GIN (tipo);

-- -----------------------------------------------------------------------------
-- VIEW: checklist de versões finais — 1 documento por (plano, requisito),
-- preferindo os já 'aceito/validado', depois o mais recente. Recompõe sozinha
-- a cada substituição (obsolescência automática).
-- -----------------------------------------------------------------------------
CREATE OR REPLACE VIEW vw_pt_checklist_final AS
SELECT DISTINCT ON (d.plano_id, COALESCE(d.item_id::text, d.requisito_chave))
       d.plano_id, d.item_id, d.requisito_chave,
       d.id AS documento_id, d.nome, d.tipo,
       d.status, d.arquivo_url, d.criado_em
FROM pt_documentos d
WHERE d.versao_final = true
ORDER BY d.plano_id, COALESCE(d.item_id::text, d.requisito_chave),
         (d.status = 'aceito/validado') DESC, d.criado_em DESC;
COMMENT ON VIEW vw_pt_checklist_final IS 'Versão definitiva de cada requisito exigido (checklist final que se recompõe sozinho).';

-- =============================================================================
-- FUNÇÃO: pt_instanciar_modelo(plano_id, modelo_id)
-- Copia em massa os itens-mestre de um modelo (pt_itens_modelo) p/ pt_itens
-- do plano, preservando a hierarquia. Idempotente: se o plano já tem itens
-- vindos deste modelo (instancia_de batendo), pula. Retorna nº de itens criados.
-- =============================================================================
CREATE OR REPLACE FUNCTION pt_instanciar_modelo(p_plano_id uuid, p_modelo_id uuid)
RETURNS int LANGUAGE plpgsql AS $$
DECLARE
  v_count int := 0;
  r       record;
BEGIN
  -- 1ª passada: cria os itens do plano a partir do modelo (parent_id ainda NULL)
  FOR r IN
    SELECT im.*
      FROM pt_itens_modelo im
     WHERE im.modelo_id = p_modelo_id
       AND NOT EXISTS (
         SELECT 1 FROM pt_itens pi
          WHERE pi.plano_id = p_plano_id AND pi.instancia_de = im.id
       )
     ORDER BY (im.parent_id IS NULL) DESC, im.ordem NULLS LAST, im.numero
  LOOP
    INSERT INTO pt_itens
      (plano_id, instancia_de, numero, titulo, categoria,
       especificacao, requisito_chave, modelo_url, condicional)
    VALUES
      (p_plano_id, r.id, r.numero, r.titulo, r.categoria,
       r.especificacao, r.requisito_chave, r.modelo_url, r.condicional);
    v_count := v_count + 1;
  END LOOP;

  -- 2ª passada: resolve parent_id via instancia_de
  UPDATE pt_itens i_filho
     SET parent_id = i_pai.id
    FROM pt_itens_modelo im_filho
    JOIN pt_itens_modelo im_pai ON im_filho.parent_id = im_pai.id
    JOIN pt_itens        i_pai  ON i_pai.instancia_de = im_pai.id
                                AND i_pai.plano_id = p_plano_id
   WHERE i_filho.plano_id = p_plano_id
     AND i_filho.instancia_de = im_filho.id
     AND i_filho.parent_id IS NULL
     AND im_filho.parent_id IS NOT NULL;

  RETURN v_count;
END;
$$;
COMMENT ON FUNCTION pt_instanciar_modelo(uuid, uuid) IS
  'Instancia em pt_itens todos os itens-mestre de um modelo para um plano. Idempotente.';

-- Fim do 009_planos_trabalho.sql
