CREATE TABLE role_permissions (
  role TEXT NOT NULL CHECK(role IN ('admin','responsable','benevole','lecture')),
  module TEXT NOT NULL CHECK(module IN ('dashboard','animals','medical','care','directory','statistics','administrative','grants')),
  can_view INTEGER NOT NULL DEFAULT 0 CHECK(can_view IN (0,1)),
  can_edit INTEGER NOT NULL DEFAULT 0 CHECK(can_edit IN (0,1)),
  updated_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY(role,module)
);

INSERT INTO role_permissions(role,module,can_view,can_edit) VALUES
('admin','dashboard',1,1),('admin','animals',1,1),('admin','medical',1,1),('admin','care',1,1),('admin','directory',1,1),('admin','statistics',1,1),('admin','administrative',1,1),('admin','grants',1,1),
('responsable','dashboard',1,1),('responsable','animals',1,1),('responsable','medical',1,1),('responsable','care',1,1),('responsable','directory',1,1),('responsable','statistics',1,0),('responsable','administrative',1,1),('responsable','grants',1,1),
('benevole','dashboard',1,0),('benevole','animals',1,0),('benevole','medical',1,0),('benevole','care',1,1),('benevole','directory',1,0),('benevole','statistics',1,0),('benevole','administrative',0,0),('benevole','grants',0,0),
('lecture','dashboard',1,0),('lecture','animals',1,0),('lecture','medical',1,0),('lecture','care',0,0),('lecture','directory',0,0),('lecture','statistics',1,0),('lecture','administrative',0,0),('lecture','grants',0,0);