-- =============================================================================
-- 001_schema.sql — Plataforma de Gestão de Recursos, Eventos, Cotações e Repasses
--
-- Schema relacional completo (substitui o "esqueleto" do Apêndice A do
-- DOCUMENTO-MESTRE-plataforma-gestao.md). Alvo: PostgreSQL 14+ / Supabase.
--
-- Convenções herdadas do backend municipal (backend/sql/NNN_*.sql), MAS este é
-- um schema SEPARADO, em banco próprio — finanças municipais e de empresas
-- privadas não devem co-habitar o banco da Cultura. A segregação por âmbito
-- (RLS, ver 004_rls.sql) protege DENTRO deste banco.
--
-- Idempotente: pode rodar mais de uma vez sem erro.
-- =============================================================================

-- Extensões (gen_random_uuid)
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- -----------------------------------------------------------------------------
-- Helper: atualiza automaticamente a coluna atualizado_em em UPDATE.
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION gr_touch_atualizado_em()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.atualizado_em := now();
  RETURN NEW;
END $$;

-- -----------------------------------------------------------------------------
-- ENUMs (sets fechados). Sets que podem crescer ficam como text + CHECK.
-- -----------------------------------------------------------------------------
DO $$ BEGIN
  CREATE TYPE gr_contexto_enum AS ENUM ('publico', 'empresarial');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  -- A escada de comprometimento (Seção 4 do documento-mestre).
  CREATE TYPE gr_estagio_enum AS ENUM
    ('pre_reserva', 'reserva', 'comprometido', 'devido', 'pago');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE TYPE gr_movimento_tipo_enum AS ENUM ('margem', 'remanejamento');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE TYPE gr_movimento_modo_enum AS ENUM ('percentual', 'absoluto');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- =============================================================================
-- ÂMBITOS E ACESSO
-- =============================================================================
CREATE TABLE IF NOT EXISTS ambitos (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  nome        text NOT NULL,
  tipo        text NOT NULL
              CHECK (tipo IN ('secretaria','empresa','associacao','paroquia','instituto','pessoal')),
  criado_em   timestamptz NOT NULL DEFAULT now()
);
COMMENT ON TABLE ambitos IS 'Dono de cada registro e base da RLS: Secretaria, empresas, associações, paróquia/instituto, pessoal.';

-- Vínculo usuário ↔ âmbito. No Supabase, user_id referencia auth.users(id);
-- mantido como uuid puro para o schema rodar standalone (adicione a FK no Supabase).
CREATE TABLE IF NOT EXISTS ator_ambitos (
  user_id     uuid NOT NULL,
  ambito_id   uuid NOT NULL REFERENCES ambitos(id) ON DELETE CASCADE,
  papel       text NOT NULL CHECK (papel IN ('gestor','membro','leitor')),
  criado_em   timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, ambito_id)
);
COMMENT ON COLUMN ator_ambitos.papel IS 'gestor = vê tudo (qualquer âmbito); membro/leitor = só o próprio âmbito.';

-- =============================================================================
-- PROTOCOLOS (repositório "como resolver X") — definido antes de fontes (FK).
-- =============================================================================
CREATE TABLE IF NOT EXISTS protocolos (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  titulo       text NOT NULL,
  categoria    text CHECK (categoria IN ('emenda','dado','burocracia') OR categoria IS NULL),
  passos       text,
  orgao        text,
  departamento text,
  contato      text,
  meio         text,
  criado_em    timestamptz NOT NULL DEFAULT now()
);

-- =============================================================================
-- FONTES / COFRES
-- =============================================================================
CREATE TABLE IF NOT EXISTS fontes (
  id                       uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id                uuid NOT NULL REFERENCES ambitos(id),
  codigo                   text,
  nome                     text NOT NULL,
  contexto                 gr_contexto_enum NOT NULL DEFAULT 'publico',
  tipo                     text,  -- emenda|licitacao|orcamentaria|convenio|receita_propria|aporte
  teto                     numeric(14,2) NOT NULL DEFAULT 0,
  vigencia                 daterange,
  limite_remanejamento_pct numeric(5,2),       -- semáforo informativo (Seção 6.4)
  nota_interna             text,
  protocolo_id             uuid REFERENCES protocolos(id) ON DELETE SET NULL,
  criado_em                timestamptz NOT NULL DEFAULT now(),
  atualizado_em            timestamptz NOT NULL DEFAULT now()
);
COMMENT ON COLUMN fontes.limite_remanejamento_pct IS 'Só informativo (semáforo). Não bloqueia nem julga — decisão é do gestor.';

-- =============================================================================
-- EMPENHOS — livro oficial importado do relatório da prefeitura.
-- empenhado_liquido é coluna GERADA (= vl_empenho − vl_anulado).
-- =============================================================================
CREATE TABLE IF NOT EXISTS empenhos (
  id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id         uuid NOT NULL REFERENCES ambitos(id),
  fonte_id          uuid REFERENCES fontes(id),     -- ligação real (substitui fonte_codigo solto)
  fonte_codigo      text,                           -- mantido p/ conveniência de import
  fornecedor        text,
  natureza          text,
  num_empenho       text,
  data              date,
  vl_empenho        numeric(14,2) NOT NULL DEFAULT 0,
  vl_anulado        numeric(14,2) NOT NULL DEFAULT 0,
  liquidado         numeric(14,2) NOT NULL DEFAULT 0,
  pago              numeric(14,2) NOT NULL DEFAULT 0,
  saldo_liquidar    numeric(14,2) NOT NULL DEFAULT 0,
  empenhado_liquido numeric(14,2) GENERATED ALWAYS AS (vl_empenho - vl_anulado) STORED,
  criado_em         timestamptz NOT NULL DEFAULT now()
);

-- =============================================================================
-- EVENTOS
-- =============================================================================
CREATE TABLE IF NOT EXISTS eventos (
  id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id      uuid NOT NULL REFERENCES ambitos(id),
  nome           text NOT NULL,
  contexto       gr_contexto_enum NOT NULL DEFAULT 'publico',
  periodo_inicio date,
  periodo_fim    date,
  local          text,
  responsavel    text,
  status         text,
  criado_em      timestamptz NOT NULL DEFAULT now(),
  atualizado_em  timestamptz NOT NULL DEFAULT now()
);

-- =============================================================================
-- FORNECEDORES / FONTES DE PREÇO / ITENS COTADOS (referência de preço)
-- =============================================================================
CREATE TABLE IF NOT EXISTS fornecedores (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id  uuid NOT NULL REFERENCES ambitos(id),
  nome       text NOT NULL,
  cnpj_cpf   text,
  tipo       text CHECK (tipo IN ('oficial','executante') OR tipo IS NULL),
  contato    text,
  criado_em  timestamptz NOT NULL DEFAULT now()
);

-- Documento de referência de preço (ARP / proposta / orçamento).
CREATE TABLE IF NOT EXISTS fontes_preco (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id  uuid NOT NULL REFERENCES ambitos(id),
  tipo       text,            -- arp | proposta | orcamento
  numero     text,
  vigencia   daterange,
  anexo_url  text,
  criado_em  timestamptz NOT NULL DEFAULT now()
);

-- Itens unitários de um documento de preço (NÃO confundir com `cotacoes`,
-- que são propostas de fornecedor por item do livro-razão — ver doc Seção 5).
CREATE TABLE IF NOT EXISTS itens_cotados (
  id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  fonte_preco_id        uuid REFERENCES fontes_preco(id) ON DELETE CASCADE,
  descricao             text,
  unidade               text,
  vu                    numeric(14,2),
  fornecedor_oficial_id uuid REFERENCES fornecedores(id)
);

-- =============================================================================
-- ITENS_CALCULO — o livro-razão (processo × real, estágios de comprometimento).
-- Amarra a cadeia auditável: evento → item → preço → fornecedor → cofre → real.
-- =============================================================================
CREATE TABLE IF NOT EXISTS itens_calculo (
  id                       uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id                uuid NOT NULL REFERENCES ambitos(id),  -- denormalizado p/ RLS
  evento_id                uuid NOT NULL REFERENCES eventos(id),
  fonte_id                 uuid REFERENCES fontes(id),  -- nulo em pré-reserva (cofre ainda não escolhido)
  item_cotado_id           uuid REFERENCES itens_cotados(id),
  fornecedor_executante_id uuid REFERENCES fornecedores(id),
  empenho_id               uuid REFERENCES empenhos(id),  -- fecha a cadeia no contexto público
  descricao                text,
  categoria                text,           -- ex.: Estrutura | Artista (livre)
  dias                     int NOT NULL DEFAULT 1,
  qtd_por_dia              numeric(14,2) NOT NULL DEFAULT 1,
  qtd_total                numeric(14,2),
  vu_referencia            numeric(14,2),   -- preço acordado (processo)
  valor_total_processo     numeric(14,2),
  vu_efetivo               numeric(14,2),   -- custo real (pode divergir; margem travada)
  qtd_real                 numeric(14,2),
  total_real               numeric(14,2),
  estagio                  gr_estagio_enum NOT NULL DEFAULT 'pre_reserva',
  agenda_entrega           date,
  agenda_instalacao        date,
  conferente               text,
  observacoes              text,
  motivo_diferenca         text,
  criado_em                timestamptz NOT NULL DEFAULT now(),
  atualizado_em            timestamptz NOT NULL DEFAULT now()
);
COMMENT ON COLUMN itens_calculo.vu_efetivo IS 'Custo real (sensível). vu_referencia = preço acordado ao contratante. A diferença é a margem (pode ser negativa).';

-- =============================================================================
-- COTAÇÕES — propostas de fornecedor por item do livro-razão.
-- (Variações por forma de pagamento = múltiplas linhas.)
-- =============================================================================
CREATE TABLE IF NOT EXISTS cotacoes (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id       uuid NOT NULL REFERENCES ambitos(id),
  item_id         uuid NOT NULL REFERENCES itens_calculo(id) ON DELETE CASCADE,
  fornecedor_id   uuid REFERENCES fornecedores(id),
  valor           numeric(14,2),
  forma_pagamento text,
  data            date,
  selecionada     boolean NOT NULL DEFAULT false,
  criado_em       timestamptz NOT NULL DEFAULT now()
);
-- No máximo UMA cotação selecionada por item.
CREATE UNIQUE INDEX IF NOT EXISTS uq_cotacao_selecionada
  ON cotacoes (item_id) WHERE selecionada;

-- =============================================================================
-- MOVIMENTOS — transformações sobre valor-base (margem e remanejamento).
-- Mesma mecânica (Seções 6.2 e 6.4). FKs tipadas + rótulos texto de fallback.
-- =============================================================================
CREATE TABLE IF NOT EXISTS movimentos (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id       uuid NOT NULL REFERENCES ambitos(id),
  tipo            gr_movimento_tipo_enum NOT NULL,
  item_id         uuid REFERENCES itens_calculo(id) ON DELETE CASCADE,  -- margem
  fonte_origem_id uuid REFERENCES fontes(id),                            -- remanejamento (saída)
  fonte_destino_id uuid REFERENCES fontes(id),                           -- remanejamento (entrada)
  origem_ref      text,
  destino_ref     text,
  valor_base      numeric(14,2),
  modo            gr_movimento_modo_enum NOT NULL DEFAULT 'percentual',
  percentual      numeric(7,4),
  valor_movimento numeric(14,2),
  data            date NOT NULL DEFAULT CURRENT_DATE,
  observacao      text,
  criado_em       timestamptz NOT NULL DEFAULT now()
);

-- =============================================================================
-- PRESTAÇÃO DE CONTAS / ANEXOS
-- =============================================================================
CREATE TABLE IF NOT EXISTS prestacao_contas (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id     uuid NOT NULL REFERENCES ambitos(id),
  evento_id     uuid REFERENCES eventos(id),
  fonte_id      uuid REFERENCES fontes(id),
  etapa         text CHECK (etapa IN ('planejado','contratado','executado','documentado','prestado') OR etapa IS NULL),
  status        text,
  criado_em     timestamptz NOT NULL DEFAULT now(),
  atualizado_em timestamptz NOT NULL DEFAULT now()
);

-- Anexo polimórfico (Supabase Storage). vinculo_tipo + vinculo_id.
CREATE TABLE IF NOT EXISTS anexos (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id    uuid NOT NULL REFERENCES ambitos(id),
  tipo         text CHECK (tipo IN ('relatorio_fotografico','documento','espelho_emenda') OR tipo IS NULL),
  url          text,
  vinculo_tipo text CHECK (vinculo_tipo IN ('evento','item','prestacao','fonte') OR vinculo_tipo IS NULL),
  vinculo_id   uuid,
  criado_em    timestamptz NOT NULL DEFAULT now()
);

-- =============================================================================
-- CUSTOS FIXOS / INVENTÁRIO
-- =============================================================================
CREATE TABLE IF NOT EXISTS custos_fixos (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id    uuid NOT NULL REFERENCES ambitos(id),
  descricao    text,
  valor_padrao numeric(14,2),
  criado_em    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS inventario (
  id                     uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id              uuid NOT NULL REFERENCES ambitos(id),
  item                   text,
  quantidade             numeric(14,2),
  valor_unitario_repasse numeric(14,2),
  gera_custeio           boolean NOT NULL DEFAULT false,  -- subsídio cruzado (Seção 6.6)
  criado_em              timestamptz NOT NULL DEFAULT now()
);

-- =============================================================================
-- SOCIETÁRIA (aporte com prêmio decrescente — Seção 6.5)
-- =============================================================================
CREATE TABLE IF NOT EXISTS socios (
  id                     uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id              uuid NOT NULL REFERENCES ambitos(id),
  nome                   text,
  cota_percentual        numeric(5,2) CHECK (cota_percentual IS NULL OR (cota_percentual >= 0 AND cota_percentual <= 100)),
  remuneracao_operacional numeric(14,2) NOT NULL DEFAULT 0,
  criado_em              timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS aportes (
  id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id      uuid NOT NULL REFERENCES ambitos(id),
  socio_id       uuid REFERENCES socios(id) ON DELETE CASCADE,
  valor_total    numeric(14,2),
  premio_inicial numeric(7,4) NOT NULL DEFAULT 1,  -- 1 = recebe o dobro no início
  criado_em      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS pagamentos_aporte (
  id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  aporte_id uuid REFERENCES aportes(id) ON DELETE CASCADE,
  valor     numeric(14,2),
  data      date NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE IF NOT EXISTS distribuicao_lucros (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ambito_id  uuid NOT NULL REFERENCES ambitos(id),
  periodo    text,
  socio_id   uuid REFERENCES socios(id) ON DELETE CASCADE,
  percentual numeric(5,2),
  valor      numeric(14,2)
);

-- =============================================================================
-- AUDITORIA / HISTÓRICO (rastreabilidade total é o 1º princípio do produto).
-- Espelha o padrão de backend/app/models/auditoria.py.
-- =============================================================================
CREATE TABLE IF NOT EXISTS gestao_auditoria (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  ambito_id   uuid REFERENCES ambitos(id),
  ator        uuid,            -- user_id (auth.users no Supabase)
  acao        text NOT NULL,   -- ex.: estagio_transicao | remanejamento | ...
  entidade    text,            -- ex.: itens_calculo
  entidade_id uuid,
  detalhes    jsonb,
  criado_em   timestamptz NOT NULL DEFAULT now()
);

-- -----------------------------------------------------------------------------
-- Triggers de atualizado_em
-- -----------------------------------------------------------------------------
DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY['fontes','eventos','itens_calculo','prestacao_contas'] 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_fontes_ambito        ON fontes(ambito_id);
CREATE INDEX IF NOT EXISTS ix_empenhos_fonte        ON empenhos(fonte_id);
CREATE INDEX IF NOT EXISTS ix_empenhos_ambito       ON empenhos(ambito_id);
CREATE INDEX IF NOT EXISTS ix_empenhos_fonte_codigo ON empenhos(fonte_codigo);
CREATE INDEX IF NOT EXISTS ix_eventos_ambito        ON eventos(ambito_id);
CREATE INDEX IF NOT EXISTS ix_itens_evento          ON itens_calculo(evento_id);
CREATE INDEX IF NOT EXISTS ix_itens_fonte           ON itens_calculo(fonte_id);
CREATE INDEX IF NOT EXISTS ix_itens_ambito          ON itens_calculo(ambito_id);
CREATE INDEX IF NOT EXISTS ix_itens_estagio         ON itens_calculo(estagio);
CREATE INDEX IF NOT EXISTS ix_cotacoes_item         ON cotacoes(item_id);
CREATE INDEX IF NOT EXISTS ix_movimentos_ambito     ON movimentos(ambito_id);
CREATE INDEX IF NOT EXISTS ix_movimentos_item       ON movimentos(item_id);
CREATE INDEX IF NOT EXISTS ix_anexos_vinculo        ON anexos(vinculo_tipo, vinculo_id);
CREATE INDEX IF NOT EXISTS ix_aud_entidade          ON gestao_auditoria(entidade, entidade_id);

-- Fim do 001_schema.sql
