CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_tenant_users_roles` (
  `id` int NOT NULL AUTO_INCREMENT,
  `role_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `role_level` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `role_description` text COLLATE utf8mb4_general_ci,
  `is_active` tinyint(1) DEFAULT '1',
  `created_by` int DEFAULT NULL,
  `modified_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_tenant_users_permissions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `module_id` INT NOT NULL,
  `action_id` INT NOT NULL,

  `is_active` TINYINT(1) NOT NULL DEFAULT 1,

  `created_by` INT NULL,
  `modified_by` INT NULL,

  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  KEY `idx_module_id` (`module_id`),
  KEY `idx_action_id` (`action_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_tenant_users_role_and_permissions` (
  `id` INT NOT NULL AUTO_INCREMENT,

  `role_id` INT NOT NULL,          -- tenant role
  `module_id` INT NOT NULL,        -- from global modules table
  `action_id` INT NOT NULL,        -- from global actions table

  `is_active` TINYINT(1) NOT NULL DEFAULT 1,

  `created_by` INT NULL,
  `modified_by` INT NULL,

  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP 
      ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  KEY `idx_role_id` (`role_id`),
  KEY `idx_module_id` (`module_id`),
  KEY `idx_action_id` (`action_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
