-- SistemasWeby Financeiro - PostgreSQL 16
-- Execute conectado ao banco sistemasweby_financeiro com o usuário sistemasweby_admin.
-- CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS estados (
  id BIGSERIAL PRIMARY KEY, uf CHAR(2) NOT NULL UNIQUE, nome VARCHAR(80) NOT NULL,
  codigo_ibge INTEGER UNIQUE, criado_em TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS cidades (
  id BIGSERIAL PRIMARY KEY, estado_id BIGINT NOT NULL REFERENCES estados(id), nome VARCHAR(120) NOT NULL,
  codigo_ibge INTEGER UNIQUE, cep_consultado VARCHAR(8), dados_consulta JSONB,
  consultado_em TIMESTAMPTZ, criado_em TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE(estado_id,nome)
);
CREATE TABLE IF NOT EXISTS consultas_cnpj (
  id BIGSERIAL PRIMARY KEY, cnpj VARCHAR(14) NOT NULL, razao_social VARCHAR(200), nome_fantasia VARCHAR(200),
  situacao VARCHAR(60), dados_consulta JSONB NOT NULL DEFAULT '{}'::jsonb, sucesso BOOLEAN NOT NULL DEFAULT true,
  mensagem_erro TEXT, consultado_por BIGINT, consultado_em TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_consultas_cnpj_cnpj ON consultas_cnpj(cnpj,consultado_em DESC);

CREATE TABLE IF NOT EXISTS parametros (
  id BIGSERIAL PRIMARY KEY, chave VARCHAR(100) NOT NULL UNIQUE, valor TEXT, valor_criptografado BOOLEAN NOT NULL DEFAULT false,
  descricao VARCHAR(255), atualizado_em TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS pessoas (
  id BIGSERIAL PRIMARY KEY, tipo CHAR(2) NOT NULL CHECK(tipo IN ('PF','PJ')), nome_razao_social VARCHAR(200) NOT NULL,
  nome_fantasia VARCHAR(200), cpf_cnpj VARCHAR(14) NOT NULL UNIQUE, rg_ie VARCHAR(30), email VARCHAR(160), telefone VARCHAR(30),
  cep VARCHAR(8), logradouro VARCHAR(180), numero VARCHAR(20), complemento VARCHAR(100), bairro VARCHAR(100), cidade_id BIGINT REFERENCES cidades(id),
  ativo BOOLEAN NOT NULL DEFAULT true, observacoes TEXT, criado_em TIMESTAMPTZ NOT NULL DEFAULT now(), atualizado_em TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_pessoas_nome ON pessoas(nome_razao_social);
CREATE TABLE IF NOT EXISTS formas_pagamento (
  id BIGSERIAL PRIMARY KEY, nome VARCHAR(100) NOT NULL UNIQUE, prazo_dias INTEGER NOT NULL DEFAULT 0,
  ativo BOOLEAN NOT NULL DEFAULT true, criado_em TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS contas_financeiras (
  id BIGSERIAL PRIMARY KEY, nome VARCHAR(120) NOT NULL, tipo VARCHAR(20) NOT NULL CHECK(tipo IN ('BANCO','CAIXA','CARTEIRA','INVESTIMENTO')),
  banco_codigo VARCHAR(10), agencia VARCHAR(20), numero_conta VARCHAR(30), saldo_inicial NUMERIC(15,2) NOT NULL DEFAULT 0,
  ativo BOOLEAN NOT NULL DEFAULT true, criado_em TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS categorias (
  id BIGSERIAL PRIMARY KEY, categoria_pai_id BIGINT REFERENCES categorias(id), codigo VARCHAR(30) NOT NULL UNIQUE, nome VARCHAR(140) NOT NULL,
  tipo VARCHAR(10) NOT NULL CHECK(tipo IN ('RECEITA','DESPESA','AMBOS')), sintetica BOOLEAN NOT NULL DEFAULT false,
  ativo BOOLEAN NOT NULL DEFAULT true, criado_em TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_categorias_pai ON categorias(categoria_pai_id);
CREATE TABLE IF NOT EXISTS contas (
  id BIGSERIAL PRIMARY KEY, pessoa_id BIGINT REFERENCES pessoas(id), categoria_id BIGINT NOT NULL REFERENCES categorias(id),
  conta_financeira_id BIGINT REFERENCES contas_financeiras(id), forma_pagamento_id BIGINT REFERENCES formas_pagamento(id),
  tipo VARCHAR(10) NOT NULL CHECK(tipo IN ('PAGAR','RECEBER')), descricao VARCHAR(240) NOT NULL,
  documento VARCHAR(80), competencia DATE NOT NULL, vencimento DATE NOT NULL, valor NUMERIC(15,2) NOT NULL CHECK(valor >= 0),
  desconto NUMERIC(15,2) NOT NULL DEFAULT 0, acrescimo NUMERIC(15,2) NOT NULL DEFAULT 0,
  situacao VARCHAR(15) NOT NULL DEFAULT 'PENDENTE' CHECK(situacao IN ('PENDENTE','PARCIAL','PAGO','CANCELADO','ATRASADO')),
  recorrente BOOLEAN NOT NULL DEFAULT false, observacoes TEXT, criado_por BIGINT, atualizado_por BIGINT,
  criado_em TIMESTAMPTZ NOT NULL DEFAULT now(), atualizado_em TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_contas_vencimento ON contas(vencimento);
CREATE INDEX IF NOT EXISTS idx_contas_situacao_tipo ON contas(situacao,tipo);
CREATE TABLE IF NOT EXISTS baixas (
  id BIGSERIAL PRIMARY KEY, conta_id BIGINT NOT NULL REFERENCES contas(id), conta_financeira_id BIGINT NOT NULL REFERENCES contas_financeiras(id),
  forma_pagamento_id BIGINT REFERENCES formas_pagamento(id), data_baixa DATE NOT NULL, valor NUMERIC(15,2) NOT NULL CHECK(valor > 0),
  juros NUMERIC(15,2) NOT NULL DEFAULT 0, multa NUMERIC(15,2) NOT NULL DEFAULT 0, desconto NUMERIC(15,2) NOT NULL DEFAULT 0,
  observacoes TEXT, criado_por BIGINT, criado_em TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS programas (
  id BIGSERIAL PRIMARY KEY, codigo VARCHAR(50) NOT NULL UNIQUE, nome VARCHAR(120) NOT NULL, rota VARCHAR(180),
  ativo BOOLEAN NOT NULL DEFAULT true, criado_em TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS modulos (
  id BIGSERIAL PRIMARY KEY, programa_id BIGINT NOT NULL REFERENCES programas(id) ON DELETE CASCADE, codigo VARCHAR(50) NOT NULL,
  nome VARCHAR(120) NOT NULL, ordem INTEGER NOT NULL DEFAULT 0, ativo BOOLEAN NOT NULL DEFAULT true, UNIQUE(programa_id,codigo)
);
CREATE TABLE IF NOT EXISTS usuarios (
  id BIGSERIAL PRIMARY KEY, nome VARCHAR(160) NOT NULL, email VARCHAR(160) NOT NULL UNIQUE, senha_hash TEXT NOT NULL,
  administrador BOOLEAN NOT NULL DEFAULT false, ativo BOOLEAN NOT NULL DEFAULT true, ultimo_acesso TIMESTAMPTZ,
  criado_em TIMESTAMPTZ NOT NULL DEFAULT now(), atualizado_em TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS usuarios_modulos (
  usuario_id BIGINT NOT NULL REFERENCES usuarios(id) ON DELETE CASCADE, modulo_id BIGINT NOT NULL REFERENCES modulos(id) ON DELETE CASCADE,
  consultar BOOLEAN NOT NULL DEFAULT true, incluir BOOLEAN NOT NULL DEFAULT false, alterar BOOLEAN NOT NULL DEFAULT false,
  excluir BOOLEAN NOT NULL DEFAULT false, exportar BOOLEAN NOT NULL DEFAULT false, PRIMARY KEY(usuario_id,modulo_id)
);
ALTER TABLE consultas_cnpj ADD CONSTRAINT fk_consultas_cnpj_usuario FOREIGN KEY(consultado_por) REFERENCES usuarios(id);
ALTER TABLE contas ADD CONSTRAINT fk_contas_criado_por FOREIGN KEY(criado_por) REFERENCES usuarios(id);
ALTER TABLE contas ADD CONSTRAINT fk_contas_atualizado_por FOREIGN KEY(atualizado_por) REFERENCES usuarios(id);
ALTER TABLE baixas ADD CONSTRAINT fk_baixas_criado_por FOREIGN KEY(criado_por) REFERENCES usuarios(id);

CREATE TABLE IF NOT EXISTS auditoria_logs (
  id BIGSERIAL PRIMARY KEY, usuario_id BIGINT REFERENCES usuarios(id), data_hora TIMESTAMPTZ NOT NULL DEFAULT now(),
  tabela VARCHAR(100) NOT NULL, registro_id VARCHAR(100), acao VARCHAR(30) NOT NULL, dados_anteriores JSONB,
  dados_novos JSONB, endereco_ip INET, user_agent TEXT
);
CREATE INDEX IF NOT EXISTS idx_auditoria_usuario_data ON auditoria_logs(usuario_id,data_hora DESC);
CREATE TABLE IF NOT EXISTS ia_pesquisas (
  id BIGSERIAL PRIMARY KEY, usuario_id BIGINT REFERENCES usuarios(id), termo TEXT NOT NULL, contexto JSONB,
  resposta_resumida TEXT, modelo VARCHAR(80), tokens_entrada INTEGER, tokens_saida INTEGER, criado_em TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS ia_perguntas_respostas (
  id BIGSERIAL PRIMARY KEY, usuario_id BIGINT REFERENCES usuarios(id), pergunta TEXT NOT NULL, resposta TEXT NOT NULL,
  contexto JSONB, modelo VARCHAR(80), avaliacao SMALLINT CHECK(avaliacao BETWEEN 1 AND 5), criado_em TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS lgpd_consentimentos (
  id BIGSERIAL PRIMARY KEY, pessoa_id BIGINT REFERENCES pessoas(id), finalidade VARCHAR(200) NOT NULL, versao_termo VARCHAR(30) NOT NULL,
  consentido BOOLEAN NOT NULL, origem VARCHAR(80), endereco_ip INET, registrado_em TIMESTAMPTZ NOT NULL DEFAULT now(), revogado_em TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS lgpd_solicitacoes (
  id BIGSERIAL PRIMARY KEY, pessoa_id BIGINT REFERENCES pessoas(id), tipo VARCHAR(30) NOT NULL CHECK(tipo IN ('ACESSO','CORRECAO','ANONIMIZACAO','PORTABILIDADE','EXCLUSAO','REVOGACAO')),
  situacao VARCHAR(20) NOT NULL DEFAULT 'ABERTA', protocolo UUID NOT NULL DEFAULT gen_random_uuid(), solicitado_em TIMESTAMPTZ NOT NULL DEFAULT now(),
  atendido_em TIMESTAMPTZ, responsavel_id BIGINT REFERENCES usuarios(id), observacoes TEXT
);

INSERT INTO formas_pagamento(nome,prazo_dias) VALUES ('PIX',0),('Dinheiro',0),('Boleto bancário',2),('Cartão de crédito',30),('Transferência',0) ON CONFLICT DO NOTHING;
INSERT INTO contas_financeiras(nome,tipo,saldo_inicial) VALUES ('Banco Principal','BANCO',0),('Caixa Interno','CAIXA',0) ON CONFLICT DO NOTHING;
INSERT INTO categorias(codigo,nome,tipo,sintetica) VALUES
 ('1','Receitas','RECEITA',true),('1.01','Receitas de serviços','RECEITA',false),('2','Despesas','DESPESA',true),
 ('2.01','Infraestrutura','DESPESA',false),('2.02','Ocupação','DESPESA',false),('2.03','Comunicação','DESPESA',false)
 ON CONFLICT DO NOTHING;
INSERT INTO parametros(chave,valor,descricao) VALUES
 ('EMPRESA_NOME','SistemasWeby','Nome da empresa'),('EMPRESA_CNPJ','','CNPJ da empresa'),('EMPRESA_ENDERECO','','Endereço'),
 ('EMPRESA_RESPONSAVEL','','Responsável'),('CEP_API_URL','https://viacep.com.br/ws','API de CEP'),
 ('CNPJ_API_URL','','API de consulta CNPJ'),('IA_PROVIDER','openai','Provedor de IA') ON CONFLICT DO NOTHING;
INSERT INTO programas(codigo,nome,rota) VALUES ('FINANCEIRO','Financeiro','/'),('RELATORIOS','Relatórios','/relatorios'),('ADMIN','Administração','/administracao') ON CONFLICT DO NOTHING;
INSERT INTO modulos(programa_id,codigo,nome,ordem)
 SELECT p.id,v.codigo,v.nome,v.ordem FROM programas p JOIN (VALUES
 ('FINANCEIRO','CONTAS','Contas',1),('FINANCEIRO','PESSOAS','Pessoas',2),('FINANCEIRO','CADASTROS','Cadastros financeiros',3),
 ('RELATORIOS','DASHBOARD','Dashboard',1),('RELATORIOS','RELATORIOS','Relatórios',2),
 ('ADMIN','AUDITORIA','Auditoria e LGPD',1),('ADMIN','USUARIOS','Usuários e acessos',2),('ADMIN','SQL','Consulta SQL',3)
 ) AS v(programa,codigo,nome,ordem) ON v.programa=p.codigo ON CONFLICT DO NOTHING;
-- Senha inicial do usuário da aplicação: altere no primeiro acesso.
-- Hash bcrypt/crypt gerado no próprio PostgreSQL para: SistemasWeby@2026!Trocar
INSERT INTO usuarios(nome,email,senha_hash,administrador)
 VALUES ('Administrador SistemasWeby','admin@sistemasweby.local',crypt('P}a.8K.Z(@@Z?Upm',gen_salt('bf',12)),true)
 ON CONFLICT(email) DO NOTHING;

CREATE OR REPLACE VIEW vw_fluxo_caixa AS
 SELECT c.id,c.tipo,c.descricao,c.competencia,c.vencimento,c.valor,c.situacao,p.nome_razao_social pessoa,
 cat.codigo categoria_codigo,cat.nome categoria,cf.nome conta_financeira
 FROM contas c LEFT JOIN pessoas p ON p.id=c.pessoa_id JOIN categorias cat ON cat.id=c.categoria_id
 LEFT JOIN contas_financeiras cf ON cf.id=c.conta_financeira_id;

CREATE OR REPLACE FUNCTION atualizar_timestamp() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN NEW.atualizado_em=now(); RETURN NEW; END $$;
DROP TRIGGER IF EXISTS trg_pessoas_atualizado ON pessoas;
CREATE TRIGGER trg_pessoas_atualizado BEFORE UPDATE ON pessoas FOR EACH ROW EXECUTE FUNCTION atualizar_timestamp();
DROP TRIGGER IF EXISTS trg_contas_atualizado ON contas;
CREATE TRIGGER trg_contas_atualizado BEFORE UPDATE ON contas FOR EACH ROW EXECUTE FUNCTION atualizar_timestamp();