-- IAM 服务表结构（P2.1 + P2.2：身份 + RBAC + 审计 + 家长关系）
-- 对齐 coord-final-decisions I1-I8 + president §2.15/§2.16/§3.2/§5.5

-- ============================================================
-- 1. iam_users：用户主表
-- ============================================================
CREATE TABLE IF NOT EXISTS `iam_users` (
  `id` CHAR(36) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'active',
  -- DataScope 6 级（president §3.2：SUBJECT 替代 DISTRICT）
  `data_scope` ENUM('self','subject','class','grade','school','all') NOT NULL DEFAULT 'self',
  -- 密码策略：记录上次修改时间，用于过期校验
  `password_changed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `iam_users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 2. iam_roles：角色表（三层角色模型）
-- ============================================================
CREATE TABLE IF NOT EXISTS `iam_roles` (
  `id` CHAR(36) NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  `description` VARCHAR(255) NULL,
  -- 三层角色模型：system(系统预设) / organization(组织分配) / temporary(临时授权)
  `role_type` ENUM('system','organization','temporary') NOT NULL DEFAULT 'system',
  -- 三层优先级：system=0(最高) / organization=1 / temporary=2
  `level` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `iam_roles_name_unique` (`name`),
  INDEX `idx_iam_roles_type` (`role_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 3. iam_user_roles：用户-角色关联
-- ============================================================
CREATE TABLE IF NOT EXISTS `iam_user_roles` (
  `user_id` CHAR(36) NOT NULL,
  `role_id` CHAR(36) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`, `role_id`),
  INDEX `idx_iam_user_roles_role` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 4. iam_permissions：权限点
-- ============================================================
CREATE TABLE IF NOT EXISTS `iam_permissions` (
  `id` CHAR(36) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `resource` VARCHAR(50) NOT NULL,
  `action` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `iam_permissions_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 5. iam_role_permissions：角色-权限关联
-- ============================================================
CREATE TABLE IF NOT EXISTS `iam_role_permissions` (
  `role_id` CHAR(36) NOT NULL,
  `permission_id` CHAR(36) NOT NULL,
  PRIMARY KEY (`role_id`, `permission_id`),
  INDEX `idx_iam_role_permissions_perm` (`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 6. iam_refresh_tokens：刷新令牌（含 jti 用于黑名单追踪）
-- ============================================================
CREATE TABLE IF NOT EXISTS `iam_refresh_tokens` (
  `id` CHAR(36) NOT NULL,
  `user_id` CHAR(36) NOT NULL,
  `token_hash` VARCHAR(255) NOT NULL,
  -- JWT ID，用于黑名单追踪（I7：JWT 黑名单）
  `jti` VARCHAR(36) NULL,
  `expires_at` TIMESTAMP NOT NULL,
  `revoked_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_iam_refresh_tokens_user` (`user_id`),
  INDEX `idx_iam_refresh_tokens_jti` (`jti`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 7. iam_role_viewports：视口配置表
-- 4 层模型：L1 导航 / L2 路由 / L3 组件 / L4 数据
-- ============================================================
CREATE TABLE IF NOT EXISTS `iam_role_viewports` (
  `id` CHAR(36) NOT NULL,
  `role_id` CHAR(36) NOT NULL,
  `viewport_key` VARCHAR(50) NOT NULL,
  `label` VARCHAR(100) NOT NULL,
  `route` VARCHAR(200) NOT NULL,
  `icon` VARCHAR(50) NULL,
  `sort_order` VARCHAR(10) NOT NULL DEFAULT '0',
  `required_permission` VARCHAR(100) NULL,
  -- 视口层级：admin / teacher / student / parent
  `level` ENUM('admin','teacher','student','parent') NOT NULL DEFAULT 'teacher',
  -- L3 组件级配置（JSON）：控制组件内按钮/操作的显隐
  `component_config` TEXT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_iam_role_viewports_role` (`role_id`),
  INDEX `idx_iam_role_viewports_level` (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 8. iam_student_guardians：学生-家长关系表（I6 裁决）
-- ============================================================
CREATE TABLE IF NOT EXISTS `iam_student_guardians` (
  `id` CHAR(36) NOT NULL,
  `student_id` CHAR(36) NOT NULL,
  `guardian_id` CHAR(36) NOT NULL,
  `relation` VARCHAR(20) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_student_guardian` (`student_id`, `guardian_id`),
  INDEX `idx_iam_student_guardians_guardian` (`guardian_id`),
  INDEX `idx_iam_student_guardians_student` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 9. iam_user_audit_log：审计日志表（president §5.5：归 iam）
-- ============================================================
CREATE TABLE IF NOT EXISTS `iam_user_audit_log` (
  `id` CHAR(36) NOT NULL,
  `actor_user_id` CHAR(36) NOT NULL,
  `action` VARCHAR(50) NOT NULL,
  `resource_type` VARCHAR(50) NOT NULL,
  `resource_id` VARCHAR(36) NOT NULL,
  `before_state` TEXT NULL,
  `after_state` TEXT NULL,
  `ip` VARCHAR(45) NULL,
  `user_agent` VARCHAR(255) NULL,
  `trace_id` VARCHAR(64) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_iam_audit_actor` (`actor_user_id`),
  INDEX `idx_iam_audit_resource` (`resource_type`, `resource_id`),
  INDEX `idx_iam_audit_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 10. iam_password_history：密码历史表（密码重用限制）
-- ============================================================
CREATE TABLE IF NOT EXISTS `iam_password_history` (
  `id` CHAR(36) NOT NULL,
  `user_id` CHAR(36) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_iam_password_history_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 种子数据：4 个系统角色（三层角色模型 system 层）
-- ============================================================
INSERT IGNORE INTO `iam_roles` (`id`, `name`, `description`, `role_type`, `level`) VALUES
  ('00000000-0000-0000-0000-000000000001', 'admin',   '系统管理员', 'system', 0),
  ('00000000-0000-0000-0000-000000000002', 'teacher', '教师',       'system', 0),
  ('00000000-0000-0000-0000-000000000003', 'student', '学生',       'system', 0),
  ('00000000-0000-0000-0000-000000000004', 'parent',  '家长',       'system', 0);

-- ============================================================
-- 种子数据：权限点（固定 UUID 便于 role_permissions 引用）
-- ============================================================
INSERT IGNORE INTO `iam_permissions` (`id`, `name`, `resource`, `action`) VALUES
  ('00000000-0000-0000-0000-000000000101', 'classes:read',   'classes', 'read'),
  ('00000000-0000-0000-0000-000000000102', 'classes:create', 'classes', 'create'),
  ('00000000-0000-0000-0000-000000000103', 'classes:update', 'classes', 'update'),
  ('00000000-0000-0000-0000-000000000104', 'classes:delete', 'classes', 'delete'),
  ('00000000-0000-0000-0000-000000000201', 'iam:user:read',     'iam', 'user:read'),
  ('00000000-0000-0000-0000-000000000202', 'iam:user:manage',   'iam', 'user:manage'),
  ('00000000-0000-0000-0000-000000000203', 'iam:role:manage',   'iam', 'role:manage'),
  ('00000000-0000-0000-0000-000000000204', 'iam:audit:read',    'iam', 'audit:read'),
  ('00000000-0000-0000-0000-000000000205', 'iam:viewport:read', 'iam', 'viewport:read'),
  ('00000000-0000-0000-0000-000000000301', 'exam:read',   'exam', 'read'),
  ('00000000-0000-0000-0000-000000000302', 'exam:manage', 'exam', 'manage');

-- ============================================================
-- 种子数据：admin 角色权限（全部权限）
-- ============================================================
INSERT IGNORE INTO `iam_role_permissions` (`role_id`, `permission_id`) VALUES
  ('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000101'),
  ('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000102'),
  ('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000103'),
  ('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000104'),
  ('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000201'),
  ('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000202'),
  ('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000203'),
  ('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000204'),
  ('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000205'),
  ('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000301'),
  ('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000302');

-- ============================================================
-- 种子数据：teacher 角色权限（classes CRUD + user:read + exam:read）
-- ============================================================
INSERT IGNORE INTO `iam_role_permissions` (`role_id`, `permission_id`) VALUES
  ('00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000101'),
  ('00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000102'),
  ('00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000103'),
  ('00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000104'),
  ('00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000201'),
  ('00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000301');

-- ============================================================
-- 种子数据：student 角色权限（classes:read + exam:read）
-- ============================================================
INSERT IGNORE INTO `iam_role_permissions` (`role_id`, `permission_id`) VALUES
  ('00000000-0000-0000-0000-000000000003', '00000000-0000-0000-0000-000000000101'),
  ('00000000-0000-0000-0000-000000000003', '00000000-0000-0000-0000-000000000301');

-- ============================================================
-- 种子数据：parent 角色权限（classes:read + viewport:read）
-- ============================================================
INSERT IGNORE INTO `iam_role_permissions` (`role_id`, `permission_id`) VALUES
  ('00000000-0000-0000-0000-000000000004', '00000000-0000-0000-0000-000000000101'),
  ('00000000-0000-0000-0000-000000000004', '00000000-0000-0000-0000-000000000205');

-- ============================================================
-- 种子数据：admin 视口（L1 导航，level=admin）
-- ============================================================
INSERT IGNORE INTO `iam_role_viewports` (`id`, `role_id`, `viewport_key`, `label`, `route`, `icon`, `sort_order`, `required_permission`, `level`, `component_config`) VALUES
  ('00000000-0000-0000-0000-000000000b01', '00000000-0000-0000-0000-000000000001', 'dashboard', '仪表盘',   '/dashboard',  'home',    '0', NULL,             'admin', NULL),
  ('00000000-0000-0000-0000-000000000b02', '00000000-0000-0000-0000-000000000001', 'classes',   '班级管理', '/classes',    'users',   '1', 'classes:read',   'admin', NULL),
  ('00000000-0000-0000-0000-000000000b03', '00000000-0000-0000-0000-000000000001', 'iam',       '用户管理', '/iam/users',  'shield',  '2', 'iam:user:read',  'admin', NULL),
  ('00000000-0000-0000-0000-000000000b04', '00000000-0000-0000-0000-000000000001', 'audit',     '审计日志', '/iam/audit',  'list',    '3', 'iam:audit:read', 'admin', NULL),
  ('00000000-0000-0000-0000-000000000b05', '00000000-0000-0000-0000-000000000001', 'profile',   '个人中心', '/profile',    'user',    '9', NULL,             'admin', NULL);

-- ============================================================
-- 种子数据：teacher 视口（L1 导航，level=teacher）
-- ============================================================
INSERT IGNORE INTO `iam_role_viewports` (`id`, `role_id`, `viewport_key`, `label`, `route`, `icon`, `sort_order`, `required_permission`, `level`, `component_config`) VALUES
  ('00000000-0000-0000-0000-000000000a01', '00000000-0000-0000-0000-000000000002', 'dashboard', '仪表盘',   '/dashboard', 'home',     '0', NULL,             'teacher', NULL),
  ('00000000-0000-0000-0000-000000000a02', '00000000-0000-0000-0000-000000000002', 'classes',   '班级管理', '/classes',   'users',    '1', 'classes:read',   'teacher', NULL),
  ('00000000-0000-0000-0000-000000000a03', '00000000-0000-0000-0000-000000000002', 'exam',      '考试管理', '/exam',      'file-text','2', 'exam:read',      'teacher', NULL),
  ('00000000-0000-0000-0000-000000000a04', '00000000-0000-0000-0000-000000000002', 'profile',   '个人中心', '/profile',   'user',     '9', NULL,             'teacher', NULL);

-- ============================================================
-- 种子数据：student 视口（L1 导航，level=student）
-- ============================================================
INSERT IGNORE INTO `iam_role_viewports` (`id`, `role_id`, `viewport_key`, `label`, `route`, `icon`, `sort_order`, `required_permission`, `level`, `component_config`) VALUES
  ('00000000-0000-0000-0000-000000000c01', '00000000-0000-0000-0000-000000000003', 'dashboard', '仪表盘',   '/dashboard', 'home',     '0', NULL,             'student', NULL),
  ('00000000-0000-0000-0000-000000000c02', '00000000-0000-0000-0000-000000000003', 'classes',   '我的班级', '/classes',   'users',    '1', 'classes:read',   'student', NULL),
  ('00000000-0000-0000-0000-000000000c03', '00000000-0000-0000-0000-000000000003', 'exam',      '我的考试', '/exam',      'file-text','2', 'exam:read',      'student', NULL),
  ('00000000-0000-0000-0000-000000000c04', '00000000-0000-0000-0000-000000000003', 'profile',   '个人中心', '/profile',   'user',     '9', NULL,             'student', NULL);

-- ============================================================
-- 种子数据：parent 视口（L1 导航，level=parent）
-- ============================================================
INSERT IGNORE INTO `iam_role_viewports` (`id`, `role_id`, `viewport_key`, `label`, `route`, `icon`, `sort_order`, `required_permission`, `level`, `component_config`) VALUES
  ('00000000-0000-0000-0000-000000000d01', '00000000-0000-0000-0000-000000000004', 'dashboard', '仪表盘',   '/dashboard', 'home',     '0', NULL,             'parent', NULL),
  ('00000000-0000-0000-0000-000000000d02', '00000000-0000-0000-0000-000000000004', 'children',  '我的孩子', '/children',  'users',    '1', 'classes:read',   'parent', NULL),
  ('00000000-0000-0000-0000-000000000d03', '00000000-0000-0000-0000-000000000004', 'profile',   '个人中心', '/profile',   'user',     '9', NULL,             'parent', NULL);