Implementação de referência para PostgreSQL/PostGIS. O script activa as extensões necessárias, usa UUID como identificador, aplica integridade referencial e cria as restrições essenciais do modelo.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE sectores (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  codigo VARCHAR(30) NOT NULL UNIQUE,
  nome VARCHAR(150) NOT NULL UNIQUE,
  descricao TEXT,
  activo BOOLEAN NOT NULL DEFAULT TRUE,
  criado_em TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  actualizado_em TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE prioridades (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  codigo VARCHAR(30) NOT NULL UNIQUE,
  nome VARCHAR(80) NOT NULL UNIQUE,
  peso SMALLINT NOT NULL UNIQUE CHECK (peso > 0),
  cor VARCHAR(7) CHECK (cor IS NULL OR cor ~ '^#[0-9A-Fa-f]{6}$'),
  descricao TEXT
);

CREATE TABLE oportunidades (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sector_id UUID NOT NULL REFERENCES sectores(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  prioridade_id UUID REFERENCES prioridades(id) ON UPDATE CASCADE ON DELETE SET NULL,
  titulo VARCHAR(250) NOT NULL,
  descricao TEXT NOT NULL,
  estado VARCHAR(30) NOT NULL DEFAULT 'rascunho' CHECK (estado IN ('rascunho','em_validacao','publicada','suspensa','arquivada')),
  fonte TEXT,
  publicado_em TIMESTAMPTZ,
  criado_em TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  actualizado_em TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (sector_id, titulo)
);
CREATE TABLE entidades_procura (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tipo VARCHAR(50) NOT NULL,
  nome VARCHAR(200) NOT NULL,
  descricao TEXT,
  contacto_url TEXT,
  activo BOOLEAN NOT NULL DEFAULT TRUE,
  UNIQUE (tipo, nome)
);

CREATE TABLE oportunidade_entidade (
  oportunidade_id UUID NOT NULL REFERENCES oportunidades(id) ON UPDATE CASCADE ON DELETE CASCADE,
  entidade_id UUID NOT NULL REFERENCES entidades_procura(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  papel VARCHAR(80) NOT NULL,
  observacoes TEXT,
  PRIMARY KEY (oportunidade_id, entidade_id, papel)
);

CREATE TABLE requisitos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  categoria VARCHAR(50) NOT NULL,
  nome VARCHAR(180) NOT NULL,
  descricao TEXT,
  obrigatorio_padrao BOOLEAN NOT NULL DEFAULT FALSE,
  entidade_emissora VARCHAR(180),
  UNIQUE (categoria, nome)
);
CREATE TABLE oportunidade_requisito (
  oportunidade_id UUID NOT NULL REFERENCES oportunidades(id) ON UPDATE CASCADE ON DELETE CASCADE,
  requisito_id UUID NOT NULL REFERENCES requisitos(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  obrigatorio BOOLEAN NOT NULL DEFAULT TRUE,
  nivel_exigencia VARCHAR(30) CHECK (nivel_exigencia IN ('basico','intermedio','avancado','critico')),
  observacoes TEXT,
  PRIMARY KEY (oportunidade_id, requisito_id)
);

CREATE TABLE empregos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  codigo VARCHAR(30) NOT NULL UNIQUE,
  nome VARCHAR(180) NOT NULL UNIQUE,
  descricao TEXT,
  nivel_qualificacao VARCHAR(60)
);

CREATE TABLE oportunidade_emprego (
  oportunidade_id UUID NOT NULL REFERENCES oportunidades(id) ON UPDATE CASCADE ON DELETE CASCADE,
  emprego_id UUID NOT NULL REFERENCES empregos(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  quantidade_estimada INTEGER CHECK (quantidade_estimada IS NULL OR quantidade_estimada >= 0),
  horizonte VARCHAR(60),
  observacoes TEXT,
  PRIMARY KEY (oportunidade_id, emprego_id)
);
CREATE TABLE competencias (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  codigo VARCHAR(30) NOT NULL UNIQUE,
  nome VARCHAR(180) NOT NULL UNIQUE,
  tipo VARCHAR(40) NOT NULL CHECK (tipo IN ('tecnica','transversal','digital','linguistica','gestao')),
  descricao TEXT,
  nivel_requerido VARCHAR(30)
);

CREATE TABLE emprego_competencia (
  emprego_id UUID NOT NULL REFERENCES empregos(id) ON UPDATE CASCADE ON DELETE CASCADE,
  competencia_id UUID NOT NULL REFERENCES competencias(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  nivel_requerido VARCHAR(30),
  obrigatoria BOOLEAN NOT NULL DEFAULT TRUE,
  PRIMARY KEY (emprego_id, competencia_id)
);

CREATE TABLE localizacoes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  parent_id UUID REFERENCES localizacoes(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  tipo VARCHAR(40) NOT NULL CHECK (tipo IN ('pais','provincia','distrito','localidade','zee','zfi','corredor')),
  nome VARCHAR(180) NOT NULL,
  codigo VARCHAR(50) NOT NULL,
  latitude NUMERIC(9,6),
  longitude NUMERIC(9,6),
  geometria geometry(Geometry,4326),
  UNIQUE (tipo, codigo),
  CHECK (parent_id IS NULL OR parent_id <> id)
);
CREATE TABLE oportunidade_localizacao (
  oportunidade_id UUID NOT NULL REFERENCES oportunidades(id) ON UPDATE CASCADE ON DELETE CASCADE,
  localizacao_id UUID NOT NULL REFERENCES localizacoes(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  principal BOOLEAN NOT NULL DEFAULT FALSE,
  justificacao TEXT,
  PRIMARY KEY (oportunidade_id, localizacao_id)
);

CREATE UNIQUE INDEX uq_oportunidade_localizacao_principal
  ON oportunidade_localizacao (oportunidade_id) WHERE principal;

CREATE TABLE fontes_dados (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  nome VARCHAR(200) NOT NULL,
  url TEXT,
  entidade_responsavel VARCHAR(200),
  data_referencia DATE,
  versao VARCHAR(50),
  checksum VARCHAR(128),
  UNIQUE (nome, versao, checksum)
);

CREATE TABLE oportunidade_fonte (
  oportunidade_id UUID NOT NULL REFERENCES oportunidades(id) ON UPDATE CASCADE ON DELETE CASCADE,
  fonte_id UUID NOT NULL REFERENCES fontes_dados(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  referencia_externa VARCHAR(250),
  PRIMARY KEY (oportunidade_id, fonte_id)
);
CREATE INDEX idx_oportunidades_sector ON oportunidades(sector_id);
CREATE INDEX idx_oportunidades_prioridade ON oportunidades(prioridade_id);
CREATE INDEX idx_oportunidades_estado_publicacao ON oportunidades(estado, publicado_em);
CREATE INDEX idx_oportunidade_entidade_entidade ON oportunidade_entidade(entidade_id);
CREATE INDEX idx_oportunidade_requisito_requisito ON oportunidade_requisito(requisito_id);
CREATE INDEX idx_oportunidade_emprego_emprego ON oportunidade_emprego(emprego_id);
CREATE INDEX idx_emprego_competencia_competencia ON emprego_competencia(competencia_id);
CREATE INDEX idx_localizacoes_parent ON localizacoes(parent_id);
CREATE INDEX idx_localizacoes_geometria ON localizacoes USING GIST(geometria);
CREATE INDEX idx_oportunidade_localizacao_local ON oportunidade_localizacao(localizacao_id);
CREATE INDEX idx_oportunidade_fonte_fonte ON oportunidade_fonte(fonte_id);

