-- ============================================================
-- Sistema de Cobrança de Empréstimo - Schema PostgreSQL
-- ============================================================

CREATE EXTENSION IF NOT EXISTS "pgcrypto"; -- para gen_random_uuid()

-- Usuários do sistema (quem faz login: operadores/administradores)
CREATE TABLE usuarios (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(150) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    senha_hash VARCHAR(255) NOT NULL,
    ativo BOOLEAN NOT NULL DEFAULT TRUE,
    criado_em TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Captadores (quem traz os clientes)
CREATE TABLE captadores (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(150) NOT NULL,
    email VARCHAR(150),
    telefone VARCHAR(20),
    whatsapp VARCHAR(20),
    chave_pix VARCHAR(150),
    comissao_percentual NUMERIC(5,2) DEFAULT 0,
    link_token UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    ativo BOOLEAN NOT NULL DEFAULT TRUE,
    observacoes TEXT,
    criado_em TIMESTAMP NOT NULL DEFAULT NOW(),
    atualizado_em TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Clientes (quem toma o empréstimo)
CREATE TABLE clientes (
    id SERIAL PRIMARY KEY,
    captador_id INTEGER REFERENCES captadores(id) ON DELETE SET NULL,
    nome VARCHAR(150) NOT NULL,
    cpf VARCHAR(14) UNIQUE,
    rg VARCHAR(20),
    data_nascimento DATE,
    estado_civil VARCHAR(30),
    nome_mae VARCHAR(150),
    profissao VARCHAR(100),
    renda_mensal NUMERIC(12,2),
    email VARCHAR(150),
    telefone VARCHAR(20),
    whatsapp VARCHAR(20) NOT NULL,
    cep VARCHAR(10),
    endereco VARCHAR(200),
    numero VARCHAR(20),
    bairro VARCHAR(100),
    cidade VARCHAR(100),
    estado VARCHAR(2),
    referencia_nome VARCHAR(150),
    referencia_telefone VARCHAR(20),
    referencia_parentesco VARCHAR(50),
    observacoes TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'ativo', -- ativo, inativo, bloqueado
    origem VARCHAR(20) NOT NULL DEFAULT 'manual', -- manual, formulario_publico
    criado_em TIMESTAMP NOT NULL DEFAULT NOW(),
    atualizado_em TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_clientes_captador ON clientes(captador_id);

-- Anexos dos clientes (documentos, comprovantes, fotos)
CREATE TABLE cliente_anexos (
    id SERIAL PRIMARY KEY,
    cliente_id INTEGER NOT NULL REFERENCES clientes(id) ON DELETE CASCADE,
    nome_original VARCHAR(255) NOT NULL,
    caminho_arquivo VARCHAR(500) NOT NULL,
    tipo_documento VARCHAR(50), -- rg, cpf, comprovante_residencia, comprovante_renda, outro
    tamanho_bytes INTEGER,
    criado_em TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Empréstimos
CREATE TABLE emprestimos (
    id SERIAL PRIMARY KEY,
    cliente_id INTEGER NOT NULL REFERENCES clientes(id) ON DELETE CASCADE,
    captador_id INTEGER REFERENCES captadores(id) ON DELETE SET NULL,
    valor_emprestado NUMERIC(12,2) NOT NULL,
    valor_total_devido NUMERIC(12,2) NOT NULL,
    qtd_parcelas INTEGER NOT NULL,
    frequencia VARCHAR(20) NOT NULL DEFAULT 'mensal', -- semanal, quinzenal, mensal
    data_primeira_parcela DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'ativo', -- ativo, quitado, cancelado
    observacoes TEXT,
    criado_por INTEGER REFERENCES usuarios(id),
    criado_em TIMESTAMP NOT NULL DEFAULT NOW(),
    atualizado_em TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_emprestimos_cliente ON emprestimos(cliente_id);
CREATE INDEX idx_emprestimos_status ON emprestimos(status);

-- Parcelas
CREATE TABLE parcelas (
    id SERIAL PRIMARY KEY,
    emprestimo_id INTEGER NOT NULL REFERENCES emprestimos(id) ON DELETE CASCADE,
    numero_parcela INTEGER NOT NULL,
    valor_previsto NUMERIC(12,2) NOT NULL,
    valor_pago NUMERIC(12,2) NOT NULL DEFAULT 0,
    data_vencimento DATE NOT NULL,
    data_ultimo_pagamento DATE,
    status VARCHAR(20) NOT NULL DEFAULT 'pendente', -- pendente, parcial, pago
    criado_em TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_parcelas_emprestimo ON parcelas(emprestimo_id);
CREATE INDEX idx_parcelas_vencimento ON parcelas(data_vencimento);
CREATE INDEX idx_parcelas_status ON parcelas(status);

-- Histórico de pagamentos recebidos dos clientes
CREATE TABLE pagamentos (
    id SERIAL PRIMARY KEY,
    parcela_id INTEGER NOT NULL REFERENCES parcelas(id) ON DELETE CASCADE,
    valor_pago NUMERIC(12,2) NOT NULL,
    data_pagamento DATE NOT NULL,
    forma_pagamento VARCHAR(30), -- pix, dinheiro, transferencia, outro
    observacao TEXT,
    registrado_por INTEGER REFERENCES usuarios(id),
    criado_em TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_pagamentos_parcela ON pagamentos(parcela_id);

-- Pagamentos feitos AO captador (comissão)
CREATE TABLE pagamentos_captador (
    id SERIAL PRIMARY KEY,
    captador_id INTEGER NOT NULL REFERENCES captadores(id) ON DELETE CASCADE,
    valor NUMERIC(12,2) NOT NULL,
    data_pagamento DATE NOT NULL,
    observacao TEXT,
    registrado_por INTEGER REFERENCES usuarios(id),
    criado_em TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_pagcaptador_captador ON pagamentos_captador(captador_id);

-- Tabela de sessões (usada pelo connect-pg-simple para login)
CREATE TABLE "session" (
    "sid" varchar NOT NULL COLLATE "default",
    "sess" json NOT NULL,
    "expire" timestamp(6) NOT NULL
)
WITH (OIDS=FALSE);
ALTER TABLE "session" ADD CONSTRAINT "session_pkey" PRIMARY KEY ("sid") NOT DEFERRABLE INITIALLY IMMEDIATE;
CREATE INDEX "IDX_session_expire" ON "session" ("expire");

-- ============================================================
-- Usuário administrador inicial
-- Senha padrão: admin123  (troque assim que possível)
-- Hash gerado com bcrypt, custo 10
-- ============================================================
INSERT INTO usuarios (nome, email, senha_hash) VALUES
('Administrador', 'admin@sistema.com', '$2b$10$m9gJr/y9MIgkDn2YN5lKcefWVLVMqDgOBRlTmBW8Ce/BR5z.XFKQm');