-- =============================================================================
-- Migración:    0005_create_user_roles_table
-- Sprint:       1.1 — auth + RBAC + audit
-- Tabla:        user_roles  (pivot)
-- Aplica en:    BD de cada tenant (innovium_<slug>)
-- Dependencias: 0001 (users), 0002 (roles).
--
-- Descripción:
--   Asocia un usuario con su rol. En Innovium un usuario tiene UN solo
--   rol activo, pero la estructura de tabla pivot deja la puerta abierta
--   para multi-rol futuro (basta con quitar la UNIQUE en user_id).
--
-- Notas:
--   - UNIQUE(user_id) → restricción dura: un solo rol por user en 1.x.
--   - ON DELETE CASCADE en user_id: si se borra (físicamente) un user,
--     su asignación de rol se va con él. Es excepción documentada en
--     SCHEMA_SISTEMA.md.
--   - ON DELETE RESTRICT en role_id: nunca borrar un rol mientras
--     haya users asignados. Reasignar primero.
--   - asignado_por: ON DELETE SET NULL — preserva el registro aunque
--     el usuario que asignó el rol haya sido borrado físicamente.
-- =============================================================================

CREATE TABLE user_roles (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
                  COMMENT 'PK auto-incremental del mapeo.',
    user_id       BIGINT UNSIGNED NOT NULL
                  COMMENT 'FK → users.id (CASCADE). UNIQUE: un solo rol por user.',
    role_id       BIGINT UNSIGNED NOT NULL
                  COMMENT 'FK → roles.id (RESTRICT). Rol asignado al user.',
    asignado_por  BIGINT UNSIGNED     NULL
                  COMMENT 'FK → users.id (SET NULL). Quién asignó el rol.',
    asignado_en   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                  COMMENT 'UTC del momento de la asignación. Inmutable.',
    PRIMARY KEY (id),
    UNIQUE KEY uq_user_roles_user_id (user_id),
    KEY idx_user_roles_role_id (role_id),
    KEY idx_user_roles_asignado_por (asignado_por),
    CONSTRAINT fk_user_roles_user
        FOREIGN KEY (user_id) REFERENCES users (id)
        ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT fk_user_roles_role
        FOREIGN KEY (role_id) REFERENCES roles (id)
        ON DELETE RESTRICT ON UPDATE RESTRICT,
    CONSTRAINT fk_user_roles_asignado_por
        FOREIGN KEY (asignado_por) REFERENCES users (id)
        ON DELETE SET NULL ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Pivot user↔rol. Restricción 1.x: un solo rol por usuario.';