-- Permissions and role-based access control

CREATE TABLE IF NOT EXISTS "permission" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "slug" varchar(100) NOT NULL UNIQUE,
  "domain" varchar(50) NOT NULL,
  "action" varchar(50) NOT NULL,
  "description" varchar(255) NOT NULL,
  "created_at" timestamp with time zone NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS "role" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "company_id" uuid NOT NULL REFERENCES "company"("id"),
  "name" varchar(100) NOT NULL,
  "slug" varchar(100) NOT NULL,
  "description" text,
  "is_system" boolean NOT NULL DEFAULT false,
  "is_active" boolean NOT NULL DEFAULT true,
  "created_at" timestamp with time zone NOT NULL DEFAULT now(),
  "updated_at" timestamp with time zone NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX "role_company_slug" ON "role" ("company_id", "slug");

CREATE TABLE IF NOT EXISTS "role_permission" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "role_id" uuid NOT NULL REFERENCES "role"("id"),
  "permission_id" uuid NOT NULL REFERENCES "permission"("id"),
  "created_at" timestamp with time zone NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX "role_permission_unique" ON "role_permission" ("role_id", "permission_id");

CREATE TABLE IF NOT EXISTS "user_role_assignment" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "user_id" uuid NOT NULL REFERENCES "user"("id"),
  "role_id" uuid NOT NULL REFERENCES "role"("id"),
  "assigned_by" uuid,
  "created_at" timestamp with time zone NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX "user_role_assignment_unique" ON "user_role_assignment" ("user_id", "role_id");
