CREATE TABLE skills_edu.audit_events (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  occurred_at timestamptz NOT NULL DEFAULT now(),
  actor_user_id bigint,
  actor_role varchar(100),
  organisation_master_id uuid,
  action varchar(100) NOT NULL,
  object_type varchar(100) NOT NULL,
  object_id uuid,
  previous_state jsonb,
  new_state jsonb,
  result varchar(20) NOT NULL CHECK (result IN ('success','denied','failed')),
  reason text,
  correlation_id uuid NOT NULL,
  ip_hash char(64),
  user_agent_hash char(64),
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);

REVOKE UPDATE, DELETE, TRUNCATE ON skills_edu.audit_events FROM PUBLIC;
CREATE INDEX idx_audit_object ON skills_edu.audit_events(object_type,object_id,occurred_at DESC);
CREATE INDEX idx_audit_actor ON skills_edu.audit_events(actor_user_id,occurred_at DESC);
CREATE INDEX idx_audit_correlation ON skills_edu.audit_events(correlation_id);

CREATE INDEX idx_profiles_location ON skills_edu.professional_profiles(location_master_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_documents_uploaded_by ON skills_edu.qualification_document_details(uploaded_by,created_at DESC);
CREATE INDEX idx_opportunity_skills_skill ON skills_edu.educational_opportunity_skills(skill_id);
CREATE INDEX idx_program_skills_skill ON skills_edu.education_program_skills(skill_id);

CREATE TRIGGER trg_profile_contacts_updated_at BEFORE UPDATE ON skills_edu.profile_contacts FOR EACH ROW EXECUTE FUNCTION skills_edu.set_updated_at();
CREATE TRIGGER trg_profile_experiences_updated_at BEFORE UPDATE ON skills_edu.profile_experiences FOR EACH ROW EXECUTE FUNCTION skills_edu.set_updated_at();
CREATE TRIGGER trg_institutional_registrations_updated_at BEFORE UPDATE ON skills_edu.institutional_candidate_registrations FOR EACH ROW EXECUTE FUNCTION skills_edu.set_updated_at();
CREATE TRIGGER trg_qualifications_updated_at BEFORE UPDATE ON skills_edu.profile_qualifications FOR EACH ROW EXECUTE FUNCTION skills_edu.set_updated_at();
CREATE TRIGGER trg_documents_updated_at BEFORE UPDATE ON skills_edu.qualification_document_details FOR EACH ROW EXECUTE FUNCTION skills_edu.set_updated_at();
CREATE TRIGGER trg_programs_updated_at BEFORE UPDATE ON skills_edu.program_details FOR EACH ROW EXECUTE FUNCTION skills_edu.set_updated_at();
CREATE TRIGGER trg_skill_gaps_updated_at BEFORE UPDATE ON skills_edu.skill_gaps FOR EACH ROW EXECUTE FUNCTION skills_edu.set_updated_at();
CREATE TRIGGER trg_opportunities_updated_at BEFORE UPDATE ON skills_edu.educational_opportunity_details FOR EACH ROW EXECUTE FUNCTION skills_edu.set_updated_at();

COMMENT ON COLUMN skills_edu.professional_profiles.identity_hash IS 'Hash normalizado para deduplicação; nunca guardar o identificador em claro nesta coluna.';
COMMENT ON COLUMN skills_edu.profile_contacts.contact_value_encrypted IS 'Valor cifrado pela aplicação ou serviço de segredos; não indexar.';
COMMENT ON COLUMN skills_edu.profile_need_matches.explanation IS 'Critérios, pesos, dados, fonte, limitações e lacunas usados na recomendação.';


ALTER TABLE skills_edu.audit_events ADD FOREIGN KEY (actor_user_id) REFERENCES public.users(id) ON DELETE RESTRICT;

ALTER TABLE skills_edu.audit_events ADD FOREIGN KEY (organisation_master_id) REFERENCES public.organizations(id) ON DELETE RESTRICT;