CREATE TABLE IF NOT EXISTS access_roles (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  permissions_json TEXT NOT NULL,
  is_system INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS app_users (
  id TEXT PRIMARY KEY,
  auth_user_id TEXT NOT NULL DEFAULT '',
  email TEXT NOT NULL,
  name TEXT NOT NULL,
  role_id TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'Actif',
  last_login_at TEXT NOT NULL DEFAULT '',
  created_by TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS access_audit_log (
  id TEXT PRIMARY KEY,
  actor_user_id TEXT NOT NULL,
  action TEXT NOT NULL,
  target_type TEXT NOT NULL,
  target_id TEXT NOT NULL,
  details TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_app_users_email ON app_users(email COLLATE NOCASE);
CREATE UNIQUE INDEX IF NOT EXISTS idx_app_users_auth_id ON app_users(auth_user_id) WHERE auth_user_id<>'';
CREATE INDEX IF NOT EXISTS idx_app_users_role_status ON app_users(role_id,status);
CREATE INDEX IF NOT EXISTS idx_access_audit_created ON access_audit_log(created_at DESC);
PRAGMA optimize;