USE db_abos_v0.1;

-- Fixes QA Bug #4: sp_bulk_create_tenant_users' JSON contract (first/last/hash/
-- role/scope/dept) never matched what UserService.createUserViaSp() actually
-- sends (firstName/lastName/passwordHash/passwordSalt/userType/roleId/
-- moduleAccess), so v_role_name was always NULL, the INSERT INTO roles
-- violated its NOT NULL constraint, the blanket EXIT HANDLER swallowed the
-- error silently, and every tenant-admin/tenant-manager creation failed with
-- an opaque 500. Also: AuthProcedureRepository declares parameters
-- (p_tenant_id, p_users_json, p_created_by) plus an OUT p_created_count,
-- which never matched this procedure's (p_tenant_id, p_users_json,
-- p_creator_id) with no OUT param either — fixed here too.
--
-- Role creation now happens in Java (UserService.findOrCreateRole) before
-- this procedure is called, so roleId is passed directly instead of being
-- resolved/created from a role name inside the procedure.

DROP PROCEDURE IF EXISTS sp_bulk_create_tenant_users;

DELIMITER $$

CREATE PROCEDURE sp_bulk_create_tenant_users(
    IN p_tenant_id INT,
    IN p_users_json JSON,
    /* Example payload element:
    {
      "firstName": "John", "lastName": "Doe", "email": "j@test.com",
      "passwordHash": "$2a$...", "passwordSalt": "uuid", "userType": "TENANT_MANAGER",
      "roleId": 5,
      "moduleAccess": [{"module": "CRM", "accessLevel": "WRITE"}]
    }
    */
    IN p_created_by INT,
    OUT p_created_count INT
)
BEGIN
    DECLARE v_user_count INT DEFAULT 0;
    DECLARE v_module_count INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE j INT DEFAULT 0;

    DECLARE v_first VARCHAR(100);
    DECLARE v_last VARCHAR(100);
    DECLARE v_email VARCHAR(255);
    DECLARE v_hash VARCHAR(512);
    DECLARE v_salt VARCHAR(255);
    DECLARE v_user_type VARCHAR(20);
    DECLARE v_role_id INT;
    DECLARE v_new_user_id INT;
    DECLARE v_module_json JSON;
    DECLARE v_module_name VARCHAR(50);
    DECLARE v_access_level VARCHAR(20);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SET v_user_count = JSON_LENGTH(p_users_json);

    WHILE i < v_user_count DO
        SET v_first     = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].firstName')));
        SET v_last      = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].lastName')));
        SET v_email     = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].email')));
        SET v_hash       = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].passwordHash')));
        SET v_salt       = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].passwordSalt')));
        SET v_user_type  = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].userType')));
        SET v_role_id    = JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].roleId'));
        SET v_module_json = JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].moduleAccess'));

        INSERT INTO users (
            tenant_id, first_name, last_name, email, password_hash, password_salt,
            role_id, status, is_super_admin, user_type, is_enabled, created_by
        ) VALUES (
            p_tenant_id, v_first, v_last, v_email, v_hash, v_salt,
            v_role_id, 'Active', 0, v_user_type, b'1', p_created_by
        );

        SET v_new_user_id = LAST_INSERT_ID();

        IF v_module_json IS NOT NULL THEN
            SET v_module_count = JSON_LENGTH(v_module_json);
            SET j = 0;
            WHILE j < v_module_count DO
                SET v_module_name  = JSON_UNQUOTE(JSON_EXTRACT(v_module_json, CONCAT('$[', j, '].module')));
                SET v_access_level = JSON_UNQUOTE(JSON_EXTRACT(v_module_json, CONCAT('$[', j, '].accessLevel')));
                INSERT INTO user_module_access (user_id, module, access_level, tenant_id, is_deleted)
                VALUES (v_new_user_id, v_module_name, IFNULL(v_access_level, 'READ'), p_tenant_id, 0);
                SET j = j + 1;
            END WHILE;
        END IF;

        SET i = i + 1;
    END WHILE;

    SET p_created_count = v_user_count;

    COMMIT;
END$$

DELIMITER ;