-- ============================================================================ -- SIGESTE-IE — Esquema de base de dados real (PostgreSQL + PostGIS) -- Destino: base de dados "sitegeu" já criada no VPS (schema dedicado "territorial" -- para não colidir com os schemas "public" e "geoserver" já existentes). -- -- Como aplicar (no VPS, como utilizador com acesso à base "sitegeu"): -- psql -h localhost -U sitegeu_user -d sitegeu -f schema.sql -- -- Idempotente: pode ser corrido mais do que uma vez sem apagar dados -- (usa CREATE ... IF NOT EXISTS em todo o lado). -- ============================================================================ CREATE SCHEMA IF NOT EXISTS territorial; SET search_path TO territorial, public; CREATE EXTENSION IF NOT EXISTS postgis; -- --------------------------------------------------------------------------- -- Utilizadores da aplicação (login, perfis de acesso) -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS territorial.usuarios ( id SERIAL PRIMARY KEY, username VARCHAR(60) NOT NULL UNIQUE, senha_hash VARCHAR(255) NOT NULL, -- hash (werkzeug.security), nunca texto simples nome_completo VARCHAR(160) NOT NULL, perfil VARCHAR(30) NOT NULL DEFAULT 'tecnico' CHECK (perfil IN ('administrador', 'gestor', 'agente', 'tecnico')), classe_acesso VARCHAR(30) NOT NULL DEFAULT 'provincial' CHECK (classe_acesso IN ('provincial', 'municipal')), municipio VARCHAR(60), -- preenchido quando classe_acesso = 'municipal' activo BOOLEAN NOT NULL DEFAULT TRUE, criado_em TIMESTAMPTZ NOT NULL DEFAULT now() ); -- --------------------------------------------------------------------------- -- Projectos de loteamento -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS territorial.projectos ( id SERIAL PRIMARY KEY, nome VARCHAR(160) NOT NULL, localizacao VARCHAR(255), municipio VARCHAR(60) NOT NULL, area_ha NUMERIC(10,3), largura_lote_m NUMERIC(8,2), comprimento_lote_m NUMERIC(8,2), perimetro_m NUMERIC(10,2), estado VARCHAR(30) NOT NULL DEFAULT 'Ativo' CHECK (estado IN ('Ativo', 'Em planeamento', 'Concluído', 'Suspenso')), data_inicio DATE, base_lat DOUBLE PRECISION, base_lng DOUBLE PRECISION, utm_e DOUBLE PRECISION, utm_n DOUBLE PRECISION, geom geometry(Polygon, 4326), -- perímetro real do projecto (quando disponível) criado_em TIMESTAMPTZ NOT NULL DEFAULT now(), actualizado_em TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX IF NOT EXISTS idx_projectos_geom ON territorial.projectos USING GIST (geom); -- --------------------------------------------------------------------------- -- Clientes (pessoas singulares ou coletivas) -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS territorial.clientes ( id SERIAL PRIMARY KEY, nome VARCHAR(200) NOT NULL, tipo VARCHAR(12) NOT NULL DEFAULT 'singular' CHECK (tipo IN ('singular', 'coletiva')), email VARCHAR(160), telefone VARCHAR(40), bi VARCHAR(30), -- só para tipo = singular nif VARCHAR(30), morada VARCHAR(255), representante VARCHAR(160), -- só para tipo = coletiva cargo_representante VARCHAR(100), perc_entrada_padrao NUMERIC(5,2) DEFAULT 20, num_prestacoes_padrao SMALLINT DEFAULT 14, criado_em TIMESTAMPTZ NOT NULL DEFAULT now() ); -- --------------------------------------------------------------------------- -- Agentes/comerciais (para comissões) -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS territorial.agentes ( id SERIAL PRIMARY KEY, nome VARCHAR(160) NOT NULL, comissao_pct NUMERIC(5,2) NOT NULL DEFAULT 3, usuario_id INTEGER UNIQUE REFERENCES territorial.usuarios(id) ON DELETE SET NULL, telefone VARCHAR(40), email VARCHAR(160), activo BOOLEAN NOT NULL DEFAULT TRUE, criado_em TIMESTAMPTZ NOT NULL DEFAULT now() ); -- --------------------------------------------------------------------------- -- Lotes -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS territorial.lotes ( id SERIAL PRIMARY KEY, projecto_id INTEGER NOT NULL REFERENCES territorial.projectos(id) ON DELETE CASCADE, numero INTEGER NOT NULL, quadra VARCHAR(10), lote_numero VARCHAR(20), -- ex.: "M07" rua VARCHAR(120), uso VARCHAR(60), pisos SMALLINT, indice_ocupacao NUMERIC(5,2), largura_m NUMERIC(8,2), comprimento_m NUMERIC(8,2), area_m2 NUMERIC(10,2), estado VARCHAR(12) NOT NULL DEFAULT 'free' CHECK (estado IN ('occupied', 'pending', 'free')), valor NUMERIC(14,2), agente_id INTEGER REFERENCES territorial.agentes(id), cliente_id INTEGER REFERENCES territorial.clientes(id), utm_e DOUBLE PRECISION, utm_n DOUBLE PRECISION, lat DOUBLE PRECISION, lng DOUBLE PRECISION, bbox_norm JSONB, -- {left,top,width,height} normalizado (0..1) — usado quando -- o lote vem de um ficheiro georreferenciado (GeoJSON/KML/Shapefile) geom geometry(Polygon, 4326), -- geometria real do lote, quando disponível observacoes TEXT, criado_em TIMESTAMPTZ NOT NULL DEFAULT now(), actualizado_em TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (projecto_id, numero) ); CREATE INDEX IF NOT EXISTS idx_lotes_projecto ON territorial.lotes (projecto_id); CREATE INDEX IF NOT EXISTS idx_lotes_estado ON territorial.lotes (estado); CREATE INDEX IF NOT EXISTS idx_lotes_geom ON territorial.lotes USING GIST (geom); -- --------------------------------------------------------------------------- -- Contratos (Contrato-Promessa de Direito de Superfície, etc.) -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS territorial.contratos ( id SERIAL PRIMARY KEY, lote_id INTEGER NOT NULL REFERENCES territorial.lotes(id) ON DELETE CASCADE, cliente_id INTEGER NOT NULL REFERENCES territorial.clientes(id), tipo VARCHAR(100) NOT NULL DEFAULT 'Contrato-Promessa de Direito de Superfície', data_contrato DATE NOT NULL, estado VARCHAR(30) NOT NULL DEFAULT 'Pendente assinatura' CHECK (estado IN ('Pendente assinatura', 'Assinado', 'Cancelado')), valor_global NUMERIC(14,2) NOT NULL, perc_entrada NUMERIC(5,2) NOT NULL, num_prestacoes SMALLINT NOT NULL DEFAULT 0, criado_em TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX IF NOT EXISTS idx_contratos_lote ON territorial.contratos (lote_id); CREATE INDEX IF NOT EXISTS idx_contratos_cliente ON territorial.contratos (cliente_id); -- --------------------------------------------------------------------------- -- Parcelas do plano de pagamento de cada contrato (entrada + prestações) -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS territorial.parcelas ( id SERIAL PRIMARY KEY, contrato_id INTEGER NOT NULL REFERENCES territorial.contratos(id) ON DELETE CASCADE, numero SMALLINT NOT NULL, -- 0 = Entrada, 1..N = Prestações rotulo VARCHAR(40) NOT NULL, valor NUMERIC(14,2) NOT NULL, vencimento DATE NOT NULL, estado VARCHAR(20) NOT NULL DEFAULT 'Pendente' CHECK (estado IN ('Paga', 'Pendente', 'Vencida', 'Em cobrança')), pago_em TIMESTAMPTZ, UNIQUE (contrato_id, numero) ); CREATE INDEX IF NOT EXISTS idx_parcelas_contrato ON territorial.parcelas (contrato_id); -- --------------------------------------------------------------------------- -- Comissões de agentes sobre vendas -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS territorial.comissoes ( id SERIAL PRIMARY KEY, lote_id INTEGER NOT NULL REFERENCES territorial.lotes(id) ON DELETE CASCADE, agente_id INTEGER NOT NULL REFERENCES territorial.agentes(id), valor_venda NUMERIC(14,2) NOT NULL, pct NUMERIC(5,2) NOT NULL, valor NUMERIC(14,2) NOT NULL, estado VARCHAR(20) NOT NULL DEFAULT 'Pendente' CHECK (estado IN ('Pendente', 'Paga')) ); -- --------------------------------------------------------------------------- -- Desenhos/anotações feitas no Mapa Geral (linhas, polígonos, marcadores) -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS territorial.desenhos_mapa ( id SERIAL PRIMARY KEY, projecto_id INTEGER NOT NULL REFERENCES territorial.projectos(id) ON DELETE CASCADE, tipo VARCHAR(20) NOT NULL CHECK (tipo IN ('linha', 'poligono', 'marcador', 'medicao')), pontos_pct JSONB NOT NULL, -- [{x,y}, ...] em percentagem da camada do projecto etiqueta VARCHAR(160), criado_por INTEGER REFERENCES territorial.usuarios(id), criado_em TIMESTAMPTZ NOT NULL DEFAULT now() ); -- --------------------------------------------------------------------------- -- Vista publicada pelo GeoServer (WMS/WFS): só a informação do mapa, sem -- valores nem referências a clientes/agentes. -- --------------------------------------------------------------------------- CREATE OR REPLACE VIEW territorial.v_lotes_mapa AS SELECT id, projecto_id, numero, quadra, lote_numero, uso, area_m2, estado, geom FROM territorial.lotes WHERE geom IS NOT NULL; -- --------------------------------------------------------------------------- -- Semente de dados: ver seed_inicial.py (apenas os utilizadores iniciais). -- Os projectos e lotes reais são importados (ex.: docker/import/kilamba_para_lotes.sql). -- ---------------------------------------------------------------------------