• permissions.role_id → roles.id (دور واحد له عدّة صلاحيات)
• permissions.feature_id → features.id (ميزة واحدة في عدّة صلاحيات)
• user_roles.user_id → users.id & user_roles.role_id → roles.id (علاقة كثير-لكثير)
• stage_approvers.stage_id → workflow_stages.id & stage_approvers.role_id → roles.id
• audit_log.actor_id → users.id (تتبّع كل تغيير)

CREATE TYPE access_level AS ENUM ('none','read','edit','manage');

CREATE TABLE roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(80) NOT NULL, name_en VARCHAR(80),
  description TEXT, color CHAR(7), is_system BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT now()
);

CREATE TABLE features (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  key VARCHAR(40) UNIQUE NOT NULL, name VARCHAR(80), category VARCHAR(40)
);

CREATE TABLE permissions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  role_id UUID REFERENCES roles(id) ON DELETE CASCADE,
  feature_id UUID REFERENCES features(id) ON DELETE CASCADE,
  access_level access_level NOT NULL DEFAULT 'none',
  UNIQUE(role_id, feature_id)
);

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(120), email VARCHAR(160) UNIQUE, department VARCHAR(80)
);

CREATE TABLE user_roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  role_id UUID REFERENCES roles(id) ON DELETE CASCADE,
  assigned_by UUID REFERENCES users(id), assigned_at TIMESTAMP DEFAULT now(),
  UNIQUE(user_id, role_id)
);

CREATE TABLE workflow_stages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(80), status_key VARCHAR(40), action VARCHAR(120), sort_order INT
);

CREATE TABLE stage_approvers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  stage_id UUID REFERENCES workflow_stages(id) ON DELETE CASCADE,
  role_id UUID REFERENCES roles(id) ON DELETE CASCADE
);

CREATE TABLE audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  actor_id UUID REFERENCES users(id), action VARCHAR(80),
  entity VARCHAR(40), entity_id UUID, created_at TIMESTAMP DEFAULT now()
);