-- BI Igrejas - Schema PostgreSQL
-- Multi-tenant: igreja = empresa
--
-- Se já existe PostgreSQL na 5432, rode outro na 5433:
--   docker run -d --name bi-igrejas-db -e POSTGRES_USER=bi -e POSTGRES_PASSWORD=bi -e POSTGRES_DB=bi_igrejas -p 5433:5432 postgres:15
-- Ou use o mesmo Postgres (5432) com outro database.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Regiões (referência)
CREATE TABLE IF NOT EXISTS regiao (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(50) NOT NULL UNIQUE
);

INSERT INTO regiao (nome) VALUES
    ('Norte'), ('Nordeste'), ('Centro-Oeste'), ('Sudeste'), ('Sul')
ON CONFLICT (nome) DO NOTHING;

-- Igreja (empresa multi-tenant)
CREATE TABLE igreja (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    nome VARCHAR(255) NOT NULL,
    estado CHAR(2) NOT NULL,
    regiao_id INT REFERENCES regiao(id),
    cidade VARCHAR(150),
    data_fundacao DATE,
    possui_cnpj BOOLEAN DEFAULT FALSE,
    cnpj VARCHAR(18),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_igreja_estado ON igreja(estado);
CREATE INDEX idx_igreja_regiao ON igreja(regiao_id);

-- Estatísticas da membresia
CREATE TABLE estatisticas_membresia (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    igreja_id UUID NOT NULL REFERENCES igreja(id) ON DELETE CASCADE,
    total_membros INT NOT NULL DEFAULT 0,
    criancas_0_12 INT DEFAULT 0,
    adolescentes_13_17 INT DEFAULT 0,
    jovens_18_29 INT DEFAULT 0,
    adultos_30_59 INT DEFAULT 0,
    terceira_idade_60_74 INT DEFAULT 0,
    anciaos_75_plus INT DEFAULT 0,
    ano_ref INT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(igreja_id, ano_ref)
);

CREATE INDEX idx_em_igreja ON estatisticas_membresia(igreja_id);

-- Estrutura ministerial
CREATE TABLE estrutura_ministerial (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    igreja_id UUID NOT NULL REFERENCES igreja(id) ON DELETE CASCADE,
    total_pastores INT DEFAULT 0,
    total_presbiteros INT DEFAULT 0,
    total_diacones INT DEFAULT 0,
    total_obreiro INT DEFAULT 0,
    ano_ref INT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(igreja_id, ano_ref)
);

CREATE INDEX idx_em_min_igreja ON estrutura_ministerial(igreja_id);

-- Estrutura familiar
CREATE TABLE estrutura_familiar (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    igreja_id UUID NOT NULL REFERENCES igreja(id) ON DELETE CASCADE,
    total_casais INT DEFAULT 0,
    ano_ref INT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(igreja_id, ano_ref)
);

CREATE INDEX idx_ef_igreja ON estrutura_familiar(igreja_id);

-- KPIs calculados (cache)
CREATE TABLE kpi_calculado (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    igreja_id UUID REFERENCES igreja(id) ON DELETE CASCADE,
    nome_kpi VARCHAR(100) NOT NULL,
    valor NUMERIC,
    classificacao VARCHAR(50),
    ano_ref INT,
    data_calculo TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_kpi_igreja ON kpi_calculado(igreja_id);
CREATE INDEX idx_kpi_nome ON kpi_calculado(nome_kpi);

-- Usuários
CREATE TABLE usuario (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email VARCHAR(255) NOT NULL UNIQUE,
    nome VARCHAR(255) NOT NULL,
    senha_hash VARCHAR(255) NOT NULL,
    perfil VARCHAR(50) NOT NULL DEFAULT 'usuario', -- usuario | admin_igreja | administrador_geral
    ativo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Usuário ↔ Igreja (multiusuário, multi-igreja)
CREATE TABLE usuario_igreja (
    usuario_id UUID REFERENCES usuario(id) ON DELETE CASCADE,
    igreja_id UUID REFERENCES igreja(id) ON DELETE CASCADE,
    PRIMARY KEY (usuario_id, igreja_id)
);

CREATE INDEX idx_ui_igreja ON usuario_igreja(igreja_id);

-- Sessões / auditoria (opcional)
CREATE TABLE upload_planilha (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    usuario_id UUID REFERENCES usuario(id),
    igreja_id UUID REFERENCES igreja(id),
    nome_arquivo VARCHAR(255),
    linhas_importadas INT,
    erros TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
